Parameter Query

[matt22]

I have a table that lists products sold by date. I want to display how many
of each product were purchased. I wrote the following query:

SELECT First(Productid) AS [Productid], Count(Productid]) AS [Number
Purchased]
FROM Sales
GROUP BY Productid

This returns exactly what I want. A list of each product purchased,
displayed only once, and how many of each product were purchased.

Now I want to use a parameter query so that I can use this data in a report.
I want the user to enter a date range so that the data returned is for a
given period of time. The syntax for the query is:

SELECT First(Productid) AS [Productid], Count(Productid) AS [Number Purchased]
FROM Sales
GROUP BY Productid, DateofSale
HAVING (((Count(Productid))>=1) AND ((DateofSale) Between [Enter Start
Date:] And [Enter End Date:]));

This returns a row for each day a productid was sold and the amound sold on
that day resulting in the productid displaying more than once. I only want to
show one row for each productid sold and how many were sold.

Thanks for your help.

 

[Jason Lepack]

SELECT First(Productid) AS [Productid], Count(Productid) AS [Number
Purchased]
FROM Sales
GROUP BY Productid, DateofSale
HAVING (((Count(Productid))>=1) AND ((DateofSale) Between [Enter
Start
Date:] And [Enter End Date:]));

You were so close! Just remove DateofSale from your Group By clause.
See below: (Or in QueryDesigner Change the drop down under DateOfSale
to "Where" instead of "Group By"

SELECT First(Productid) AS [Productid], Count(Productid) AS [Number
Purchased]
FROM Sales
GROUP BY Productid
HAVING (((Count(Productid))>=1) AND ((DateofSale) Between [Enter
Start
Date:] And [Enter End Date:]));

That should do it.

 

[matt22]

Thanks for your response Jason. But I actually figured it out. When I removed
DateofSale from the GROUP BY clause I got an error in the query. You need to
remove the HAVING clause completely and put a WHERE clause for the dates
before the GROUP BY clause and then remove DateofSale from the GROUP BY
clause and the results are what I wanted.

Thanks.

SELECT First(Productid) AS [Productid], Count(Productid) AS [Number
Purchased]
FROM Sales
GROUP BY Productid, DateofSale
HAVING (((Count(Productid))>=1) AND ((DateofSale) Between [Enter
Start
Date:] And [Enter End Date:]));

You were so close! Just remove DateofSale from your Group By clause.
See below: (Or in QueryDesigner Change the drop down under DateOfSale
to "Where" instead of "Group By"

SELECT First(Productid) AS [Productid], Count(Productid) AS [Number
Purchased]
FROM Sales
GROUP BY Productid
HAVING (((Count(Productid))>=1) AND ((DateofSale) Between [Enter
Start
Date:] And [Enter End Date:]));

That should do it.

I have a table that lists products sold by date. I want to display how many
of each product were purchased. I wrote the following query:

SELECT First(Productid) AS [Productid], Count(Productid]) AS [Number
Purchased]
FROM Sales
GROUP BY Productid

This returns exactly what I want. A list of each product purchased,
displayed only once, and how many of each product were purchased.

Now I want to use a parameter query so that I can use this data in a report.
I want the user to enter a date range so that the data returned is for a
given period of time. The syntax for the query is:

SELECT First(Productid) AS [Productid], Count(Productid) AS [Number Purchased]
FROM Sales
GROUP BY Productid, DateofSale
HAVING (((Count(Productid))>=1) AND ((DateofSale) Between [Enter Start
Date:] And [Enter End Date:]));

This returns a row for each day a productid was sold and the amound sold on
that day resulting in the productid displaying more than once. I only want to
show one row for each productid sold and how many were sold.

Thanks for your help.