Logic help with SQL Pass-through query

bmurch71

Board Regular
Joined
Oct 15, 2004
Messages
68
Hi All,
I have a SQL pass-through query in my database (called qry_SMSProductsOnly) that pulls information from a retail products table - it's only bringing in UPC and department number from SQL. I have a secondary query that joins this SQL pass-through with a table containing invoice information (Unfi_coding), on the UPC field. I am trying to total the invoice by department number (from the SQL query), but am having difficulty. If I use the following code:

SELECT Unfi_coding.InvNum, Sum(Unfi_coding.ExtendedPrice) AS SumOfExtendedPrice, qry_SMSProductsOnly.F04 INTO tbl_InvoiceTotals
FROM Unfi_coding INNER JOIN qry_SMSProductsOnly ON Unfi_coding.UPC = qry_SMSProductsOnly.F01
GROUP BY Unfi_coding.InvNum, qry_SMSProductsOnly.F04;

where F04 is the department number field, InvNum is the invoice number, I get totals that are significantly higher than they should be. If I remove the department number field, I get totals that accurately reflect the total invoice amount. I have checked for duplicate values in my sql table (on UPC and Dept Number) and have found none - I thought perhaps that if an item appeared twice in SQL, it would throw off the totals. Anyone have any thoughts/ideas as to what might be going on here?

I'm stumped!
Thanks,
Brian
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
If you look at this query as a plain select query, without the summary functions here, I think you will see you have multiple records where there should only be one record, therefore, when you get the sum of invoice amount, it will be too high. I would suggest two queries, one to give you the total amount by invoice, then do the join of invoice totals with department number.
HTH
 

bmurch71

Board Regular
Joined
Oct 15, 2004
Messages
68
hi Vic,
Thanks for the input. What I can't figure out is WHY there are duplicate rows - there shouldn't be, as each upc field should have only one corresponding department field (i.e. you can't sell an item in more than one department). I agree, however, that this is what it seems to be doing; my problem is that I can't first total the invoice, then split it up by department. If I just total by invoice number, I lose the UPC fields, which is how I'm able to sum by departments. UPC is the field common to both tables. Maybe I've misunderstood something in your suggestion - please let me know if that's the case.

Thanks again for the help!
brian
 

bmurch71

Board Regular
Joined
Oct 15, 2004
Messages
68
More info - the problem can be traced to the SQl pass-through query. If instead of using the query, I actually link to the SQL table, I can get my invoice totals by department no problem. Here's the code for the pass-through query:

SELECT POS_TAB.F01, POS_TAB.F04
FROM POS_TAB;

It's a simple select statement, but my SQL knowledge is quite minimal. Anyone know how to word it so that duplicates are not generated (i.e. is there a GROUP BY command one can use in SQL)?

Thanks,
Brian
 

bmurch71

Board Regular
Joined
Oct 15, 2004
Messages
68

ADVERTISEMENT

OK, figured it out! I just used the GROUP BY command in the SQL statement and that took care of my problem!

SELECT POS_TAB.F01, POS_TAB.F04
FROM POS_TAB WHERE POS_TAB.F04 IS NOT NULL GROUP BY POS_TAB.F01, POS_TAB.F04

(Also added a statement to get rid of null values)

Brian
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Good job Brian. Sorry I could not get back to you sooner, but I had a couple of appointments this morning.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Brian,

This is another option --

Code:
SELECT DISTINCT POS_TAB.F01, POS_TAB.F04
FROM POS_TAB WHERE POS_TAB.F04 IS NOT NULL
Denis
 

Forum statistics

Threads
1,136,696
Messages
5,677,259
Members
419,682
Latest member
M3one

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top