Simple Sum Question

[sstidham]

I cannot understand for the life of me why this isn't working, but maybe someone can help me out. It should be a simple Sum.

I have a table that is imported data from Excel. I need to total up different, for lack of a better word, Columns to get a number of HSD sales for each Agent.

Here is how I currently have the query:

SELECT [Flexcomm Import].[Rep#], Sum([Flexcomm Import]![RR7I Qty]+[Flexcomm Import]![RR7U Qty]+[Flexcomm Import]![ELKI Qty]+[Flexcomm Import]![ELKU Qty]+[Flexcomm Import]![RRLiteto7 Qty]+[Flexcomm Import]![RR10I Qty]+[Flexcomm Import]![RR10U Qty]+[Flexcomm Import]![RRLiteto10 Qty]+[Flexcomm Import]![RR15I Qty]+[Flexcomm Import]![RR15U Qty]+[Flexcomm Import]![RRliteto15 Qty]) AS HSD
FROM [Flexcomm Import]
GROUP BY [Flexcomm Import].[Rep#]
ORDER BY [Flexcomm Import.[Rep#]

For whatever reason when the query runs it is returning a value that is INSANE. 99401130, for a representative that if I total it up in Excel is only like 27.

I am only using only one table. Now I asked a similar question before and the wonderful person who replied pointed me in the direction of an article on normalization. The problem here is that I cannot change the way the data imports or how this report is done.

Each representative has a seperate row for each Customer they sold anything to. And each product type has a 0 or a 1 . 0 if nothing is sold, 1 if something was sold. So each agent can have 77 or more lines of data.

Any help would be much appreciated. Thank you.

[DLN]

I deleted this post 3/25/2008

[DLN]

I ran a query that just did a SUM on one table and the total came up 15.
I added the Group By and the total came up 15.

But if I added another table to the query (Even if it wasn't used) the total came out 125.

Do you have more then one table in the query?

[sstidham]

No I'm only using 1 table for this query. Now it is not populating ANYTHING. From what I can see the updated excel file is the same as the one before. But it is showing the field it should be giving me the total in as blank. The Sum formula is copied exactly as I have it in another query for a different table that works fine, (except I of course changed the table name and the field names to match).

And now its blank. I've been playing with it all day and I'm about to blow my brains out.

[DLN]

No I'm only using 1 table for this query. Now it is not populating ANYTHING. From what I can see the updated excel file is the same as the one before. But it is showing the field it should be giving me the total in as blank. The Sum formula is copied exactly as I have it in another query for a different table that works fine, (except I of course changed the table name and the field names to match).

And now its blank. I've been playing with it all day and I'm about to blow my brains out.

You're trying to update the Excel File?

[sstidham]

No, sorry I didn't mean to be confusing. What I am doing is I have two excel files that I am importing into two tables.

I have managed to get the math to work with one tables data, and logically could use the same query for the other tables data (they are essentially the same in regards to the math). What I need to do is add the results of Query A (which is the Sum of all the data from Table A) to the results of Query B to get a final total for the products and services each agent sold yeilding Query C, and then have a 4rd query, which takes the results of Query C and does some more math (sell in percentages etc).

So, I have the results of Query A, which I need to Combine with Query B, to do some final math in Query C. And I don't know how to do that.

To explain the table in more detail essentially each agent is identified in two ways Agent Number and Agent name. Each for lack of a better word row of data is a seperate sale. So the datasheet looks like:
Sales ID Agent Name DC RR DP STN BAS
489590 Agent Name 1 1 1 1 1
785269 Agent Name 1 0 0 1 1

Query A's Code:

SELECT Flexcomm.[Rep Name], Sum(IIf(Flexcomm![RR7I Qty]+Flexcomm![RR10I Qty]+Flexcomm![RR15I Qty]>0 And Flexcomm![ORDER TYPE] In ("NC","RC") And (Flexcomm![UF PhI Qty]+Flexcomm![DgPhI Qty]>0) And (Flexcomm![DigiI Qty]+Flexcomm![CPSTI Qty]>0),1,0)) AS [Triple play], Sum(Flexcomm![RR7I Qty]+Flexcomm![RR7U Qty]+Flexcomm![RRLiteto7 Qty]+Flexcomm![RR10I Qty]+Flexcomm![RR10U Qty]+Flexcomm![RR15I Qty]+Flexcomm![RR15U Qty]+Flexcomm![ELKI Qty]+Flexcomm![ELKU Qty]+Flexcomm![RRLiteto10 Qty]+Flexcomm![RRliteto15 Qty]) AS [RR 7 Mb], Sum(Flexcomm![UF PhI Qty]+Flexcomm![UF PhU Qty]+Flexcomm![DgPhI Qty]+Flexcomm![DgPhU Qty]+Flexcomm![2nd Line Qty]) AS Phone, Sum(Flexcomm![RRLiteI Qty]+Flexcomm![RRLITEU Qty]+Flexcomm![RR10I Qty]+Flexcomm![RR10U Qty]+Flexcomm![RRLiteto10 Qty]+Flexcomm![RR7to10 Qty]+Flexcomm![RR15I Qty]+Flexcomm![RR15U Qty]+Flexcomm![RRliteto15 Qty]+Flexcomm![RR7to15 Qty]+Flexcomm![DVR Qty]+Flexcomm![DVR U Qty]+Flexcomm![HD Qty]+Flexcomm![SPT Qty]) AS [Non Core]
FROM Flexcomm
GROUP BY Flexcomm.[Rep Name];


Query B's code would esentially be the same, except for some of the values change to match that particular table.

Now I need to add the results for A to B and then have a third query C which does the finalizing math.

I don't know if its possible. I'm hoping it is, because its very important we are able to do this.