Multiple Sum Report

[kmclane]

I have a task I am having trouble getting a handle on. I have large amounts of data in several different tables. I need to pull sums of about 40 different fields for each of a couple of hundred agents and spit them into an Excel Worksheet in an organized manner. I already have the spreadsheet layout, and most of the data in tables. Some data I may have to integrate later. I am having a hard time getting started. I have agent Name/Numbers as column Headings and fields that I need totals for as row headings. Some of the totals I need are simply counts that are used in formulas on the spreadsheet, others are Currency. I need to break this down into logical stages and I am stuck.
Any Suggestions would be greatly appreciated.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dhookom]

I can't get my head around what you are storing in your fields and how you want to present it in Excel. However, have you considered MSQuery in Excel to pull the data from Access tables or queries?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kmclane]

Yes, I have looked at it, and I may want to do this all programatically. I have about 40 fields that I need to sum for each distinct entry in my "group" field. In other words, in Group I may have 5 entries for the same agent, which means I have 5 entries in all of my 40 fields. I want to make a new table that has one entry for group, and a total of each other field. Does that make sense?

Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dhookom]

It still doesn't make sense to me. Could you take the time to enter a few records into a reply as well as how you want to display the results and where you want to display them?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kmclane]

Actually, I think I have a start on it, as somebody told me, "just follow the wizard". I went through the query design Wizard and had it sum all the fields I needed totals for and group it by Agent. I got what I wanted but I am still missing some data to finish the project. Once I can pull all my data together, I will need to get it in a report, or over to a spreadsheet. Just to clarify for you, I have an agent, #7, I have multiple rows for him, with fields like a count of transactions, volume of dollars, volume of fees. What I needed was one row with the total transactions, total dollars, total fees, etc... I just forgot that the query wizard would do this automatically in the summary options. Here is an abridged version of the sql from the query:

SELECT DISTINCTROW EOMVisaMC.Sort, Sum(EOMVisaMC.VDuesAss) AS SumOfVDuesAss, Sum(EOMVisaMC.TotNetVol) AS [Sum Of TotNetVol], Sum(EOMVisaMC.FileFeeCtEDC) AS [Sum Of FileFeeCtEDC], Sum(EOMVisaMC.NewMrchCnt) AS [Sum Of NewMrchCnt], Sum(EOMVisaMC.StatementCt) AS [Sum Of StatementCt], Sum(EOMVisaMC.DepFeeCtEDC) AS [Sum Of DepFeeCtEDC], Sum(EOMVisaMC.DepFeeCtPaper) AS [Sum Of DepFeeCtPaper], Sum(EOMVisaMC.TotRefCt) AS [Sum Of TotRefCt], Sum(EOMVisaMC.DebTotCt) AS [Sum Of DebTotCt], Sum(EOMVisaMC.MCDuesAss) AS [Sum Of MCDuesAss]
FROM TrustCommerce INNER JOIN EOMVisaMC ON TrustCommerce.SortCode = EOMVisaMC.Sort
GROUP BY EOMVisaMC.Sort;

I hope this gives you a better idea. Of course I actually need to include about 100 total fields for each agent, but I have a direction to go now. All I need is access to ALL the data I need. I just realized that what I was given does not include everything I need.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dhookom]

Your table structure looks quite un-normalized. I still don't know how many different tables and what is different about them as well as how you want to split them into Excel Worksheets.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kmclane]

What do you mean "un-normalized"? I do not have control over the data until I get it. I work for a credit card processor, and these tables have to do with billing our agents for the processing that their merchants do. There are actually many fields that I do not even use. Since I do not have all the data, I am not sure how many tables I will be dealing with. I am having a bit of trouble getting cooperation from the people sending me the data. The reason I am trying to do this is that they think their reporting is great, but it isn't. My boss wants better reporting, and they think what they give us is fine. My boss gave me an Excel spreadsheet laid out with what he wants on the report. In going through it I found that some of the things he wants do not exist in the data I have been given, but at least I know how to get my totals now. That's the first step.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dhookom]

Any table that has more than about 25-30 fields raises a red flag regarding normalization. There are lots of good articles on the web about this. If you don't have any influence over the data structure, then you will have to make do with what they give you.

I still don't understand how/where you want the totals from your query. You didn't really answer my question but it sounds like you might not have all the information yet.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kmclane]

To tell you the truth, I don't yet know for sure where the info is going. The people doing the reports now seem to be outputting them from Access, And I asked for the queries and tables involved, as adding a few things and making minor changes is a lot easier than reinventing the wheel. I actually have a MS cert in Excel, so I am much more comfortable there, but I haven't worked a whole lot with MS Query. I already knew how to get totals in Excel. I had my boss call them again asking for ALL the data, maybe I'll get lucky this time. Thanks for your interest, I'll post back if I get stuck on something, this board is rapidly teaching me Access, I had never touched it before 6 weeks ago.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg