Adding "Date Range" messing up a prefectly good Query!

[Willebanks]

Greetings All,

Let me start by saying I'm very new to MS Access and I might not have all the naming conventions correct but I'll try my best to convey my issue.

I have a small shipping and receiving database that I wrote some years ago to keep track of a small warehouse where I work. I created a query that I use to populate a report that basically tells me how much of any / all item(s) have been shipped from the beginning of the year. Below is the SQL view of that query.

SELECT DISTINCTROW [Orders by Schools Details].ProductID, [Orders by Schools Details].Description, Sum([Orders by Schools Details].Shipped) AS Total, [Orders by Schools Details].UnitCost, CCur(Sum([Orders by Schools Details].Shipped*[UnitCost])) AS [Line Total]
FROM [Orders by School] INNER JOIN [Orders by Schools Details] ON [Orders by School].OrderID = [Orders by Schools Details].OrderID
GROUP BY [Orders by Schools Details].ProductID, [Orders by Schools Details].Description, [Orders by Schools Details].UnitCost
HAVING (((Sum([Orders by Schools Details].Shipped))>0));

This little query has suited me for 10 years but now I'd like to know the total of items shipped during any particular date range: Below is what I tried.

SELECT DISTINCTROW [Orders by Schools Details].ProductID, [Orders by Schools Details].Description, Sum([Orders by Schools Details].Shipped) AS Total, [Orders by Schools Details].UnitCost, CCur(Sum([Orders by Schools Details].Shipped*[UnitCost])) AS [Line Total]
FROM [Orders by School] INNER JOIN [Orders by Schools Details] ON [Orders by School].OrderID = [Orders by Schools Details].OrderID
GROUP BY [Orders by Schools Details].ProductID, [Orders by Schools Details].Description, [Orders by Schools Details].UnitCost, [Orders by School].OrderDate
HAVING (((Sum([Orders by Schools Details].Shipped))>0) AND (([Orders by School].OrderDate) Between [Start Date] And [End Date]));

The problem is that the moment I add anything to do with dates I get individual lines for every item that was shipped not the nice totals I'm used to seeing...I can't figure out how to get Access to "Total" each item shipped.

Any help with this would be a blessing as I'm nearly bald over this and I almost certain the solution is probably laughably easy I'm just too new to see it.

Thanks in advance!

Will

 

[plog]

If you only want to use it as criteria, you need to only add a reference to it in the criteria section of your SQL. When you add items to the GROUP BY it affects how the rows are displayed (each unique permutation of fields in the GROUP BY get their own row) . Remove your Date field from the GROUP BY:

GROUP BY [Orders by Schools Details].ProductID, [Orders by Schools Details].Description, [Orders by Schools Details].UnitCost, [Orders by School].OrderDate

 

[Willebanks]

Thanks for the fast rely plog!

Again I'm very much the beginner and I couldn't figure out how to show my work so I used the SQL view to post here. I'm not used to working in SQL view but I think I understand what you are talking about so I played around a bit until I got something that seems to be working exactly how I want it...Here is the SQL view.

SELECT DISTINCTROW [Orders by Schools Details].ProductID, [Orders by Schools Details].Description, Sum([Orders by Schools Details].Shipped) AS Total, [Orders by Schools Details].UnitCost, CCur(Sum([Orders by Schools Details].Shipped*[UnitCost])) AS [Line Total]
FROM [Orders by School] INNER JOIN [Orders by Schools Details] ON [Orders by School].OrderID = [Orders by Schools Details].OrderID
WHERE ((([Orders by School].OrderDate) Between [Start Date] And [End Date]))
GROUP BY [Orders by Schools Details].ProductID, [Orders by Schools Details].Description, [Orders by Schools Details].UnitCost
HAVING (((Sum([Orders by Schools Details].Shipped))>0));

Not sure if this is what you were talking about but it seems to be working perfectly!!!
If I'm off base let me know....and again thanks for the help and the fast reply!

AWESOME!

Will

 

[plog]

I would remove the DISTINCTROW.