Query problem??

[IMO]

Hi all,

Can anybody tell me why the following query gives me the strange results attached??

SELECT dbo_HistoryLines.Qty, dbo_HistoryLines.UnitPrice, Sum([Qty]*[UnitPrice]) AS [Qty * UnitPrice]
FROM dbo_HistoryLines
GROUP BY dbo_HistoryLines.Qty, dbo_HistoryLines.UnitPrice, dbo_HistoryLines.Date, dbo_HistoryLines.DocumentType
HAVING (((dbo_HistoryLines.Date) Between "2002-01-01" And "2002-12-31") AND ((dbo_HistoryLines.DocumentType)=3));

Thanks in advance

IMO

 

[bradcccs]

You are asking for grouping by Qty & UnitPrice, and then summing (Qty * Unitprice).

thus if you have 2 seperate entries as below:

Qty 1 UnitPrice $10
Qty 1 UnitPrice $10

it gets represented as a single entry (grouped)

Qty 1 UnitPrice $10 Summed Total $20

To correct the issue, remove the Sum( ) from your Qty * UnitPrice

Brad.

 

[bradcccs]

As an aside, avoid using the field name of "date".

Date is a reserved word, and will cause headaches in further coding and date handling.

 

[IMO]

Brad,

Thanks for that, I think I left my brain at home today.

IMO

 

[Rich]

HAVING (((dbo_HistoryLines.Date) Between "2002-01-01" And "2002-12-31") AND

If those are actual dates then you need to enclose them with the hash and not quotes
HAVING (((dbo_HistoryLines.Date) Between #2002-01-01#And #2002-12-31#) AND
I also believe it's more efficient to use the Where clause rather than the Having

 

[IMO]

Rich,

Thanks.

I'm having a bad day!!!

IMO