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
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