Query of items ordered by month

Using access 2002 I made a query in design view that lists all orders for a
certain item over a few years. There are only 3 relevant columns although
others were used to get this info:
order number, quantity, date

I'm interested in knowing the steps to alter this query so it sums up each
month. Ultimately I want a report that shows a bar chart with the quantity
sold per month (and maybe show the number of orders too)

Here's my current query I made in design view

SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity, tblInventory.ISBN_B,
tblOrderHdr.UserID, tblOrderHdr.OrderDate
FROM (tblOrderHdr INNER JOIN tblOrderDetails ON tblOrderHdr.OrderHdrID =
tblOrderDetails.odOrderHdrID) INNER JOIN tblInventory ON
tblOrderDetails.odInvID = tblInventory.InvID
WHERE (((tblInventory.ISBN_B)="15706") AND ((tblOrderHdr.UserID) Is Not
Null) AND ((tblOrderHdr.OrderDate) Between #1/1/2000# And #12/8/2009#));

If this can be done in a report instead of exporting and me making the bar
chart in excel that would be cool but I can't use microsoft object web
components reference because it breaks my app.

date is a resrved word and if you are using it as a field name, you need to
change the name.

Create two blank fields at the beginning of your query. Assuming you change
Date to OrderDate, add this field in the first blank field of your query:
OrderYear=Year([OrderDate])
Sort ascending on this field

Add this field in the second blank field of your query:
OrderMonth=Month([OrderDate])
Sort ascending on this field

Change the query to a Totals query. Click on the Sigma (looks like a capital
E) icon in the menu at the top of the screen. Change Group By under Quantity
to Sum. Change Group By under OrderNumber to Count.

Steve






"ZenMasta" wrote in message
...
Using access 2002 I made a query in design view that lists all orders for
a certain item over a few years. There are only 3 relevant columns
although others were used to get this info:
order number, quantity, date

I'm interested in knowing the steps to alter this query so it sums up each
month. Ultimately I want a report that shows a bar chart with the quantity
sold per month (and maybe show the number of orders too)

Here's my current query I made in design view

SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity,
tblInventory.ISBN_B, tblOrderHdr.UserID, tblOrderHdr.OrderDate
FROM (tblOrderHdr INNER JOIN tblOrderDetails ON tblOrderHdr.OrderHdrID =
tblOrderDetails.odOrderHdrID) INNER JOIN tblInventory ON
tblOrderDetails.odInvID = tblInventory.InvID
WHERE (((tblInventory.ISBN_B)="15706") AND ((tblOrderHdr.UserID) Is Not
Null) AND ((tblOrderHdr.OrderDate) Between #1/1/2000# And #12/8/2009#));

If this can be done in a report instead of exporting and me making the bar
chart in excel that would be cool but I can't use microsoft object web
components reference because it breaks my app.

quantity sold per month (and maybe show the number of orders too)
You have to drop some details to rollup the data to a monthly level.
Try this --
SELECT tblInventory.ISBN_B, Sum(tblOrderDetails.Quantity) AS Month_Total,
Format(tblOrderHdr.OrderDate, "mmm yyyy") AS Sales_Month
FROM (tblOrderHdr INNER JOIN tblOrderDetails ON tblOrderHdr.OrderHdrID =
tblOrderDetails.odOrderHdrID) INNER JOIN tblInventory ON
tblOrderDetails.odInvID = tblInventory.InvID
WHERE (((tblInventory.ISBN_B)="15706") AND ((tblOrderHdr.UserID) Is Not
Null) AND ((tblOrderHdr.OrderDate) Between #1/1/2000# And #12/8/2009#))
GROUP BY tblInventory.ISBN_B, Format(tblOrderHdr.OrderDate, "mmm yyyy")
ORDER BY tblInventory.ISBN_B, Format(tblOrderHdr.OrderDate, "yyyymm");

--
Build a little, test a little.


"ZenMasta" wrote:

Using access 2002 I made a query in design view that lists all orders for a
certain item over a few years. There are only 3 relevant columns although
others were used to get this info:
order number, quantity, date

I'm interested in knowing the steps to alter this query so it sums up each
month. Ultimately I want a report that shows a bar chart with the quantity
sold per month (and maybe show the number of orders too)

Here's my current query I made in design view

SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity, tblInventory.ISBN_B,
tblOrderHdr.UserID, tblOrderHdr.OrderDate
FROM (tblOrderHdr INNER JOIN tblOrderDetails ON tblOrderHdr.OrderHdrID =
tblOrderDetails.odOrderHdrID) INNER JOIN tblInventory ON
tblOrderDetails.odInvID = tblInventory.InvID
WHERE (((tblInventory.ISBN_B)="15706") AND ((tblOrderHdr.UserID) Is Not
Null) AND ((tblOrderHdr.OrderDate) Between #1/1/2000# And #12/8/2009#));

If this can be done in a report instead of exporting and me making the bar
chart in excel that would be cool but I can't use microsoft object web
components reference because it breaks my app.


.

Steve,
Not sure were you saw data but I'm using it as any of my table names.
I'm not sure where I'm supposed to add what you're telling me.
Do i put it in Field:/Criteria:/or: ?


Karl
I tried to copy and paste your SQL and I got an error
You tried to execute a query that does not include the specified expression
'Format(tblOrderHder.OrderDate, "yyyymm")' as part of an aggregate

Please reread my response again CAREFULLY!!!!

order number, quantity, date
I said "DATE" not "data!!

Create two blank fields at the beginning of your query.
"Fields" not "criteria"

Steve


"ZenMasta" wrote in message
...
Steve,
Not sure were you saw data but I'm using it as any of my table names.
I'm not sure where I'm supposed to add what you're telling me.
Do i put it in Field:/Criteria:/or: ?


Karl
I tried to copy and paste your SQL and I got an error
You tried to execute a query that does not include the specified
expression 'Format(tblOrderHder.OrderDate, "yyyymm")' as part of an
aggregate

I wasn't using date as a field name either.
No luck yet.
http://www.buggyonpurpose.com/random/qry.png

ZenMasta,

That message actually indicates that the query does not know which OrderDate
you want to use... tblOrderHdr and tblOrderDetails both have a field named
OrderDate. So what you need to do is specify which table you are referring
to when using OrderDate. Example: [OrderHdr]![OrderDate]

The first two columns weren't expanded so I was unable to tell you exactly
where to put it.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"ZenMasta" wrote in message
...
I wasn't using date as a field name either.
No luck yet.
http://www.buggyonpurpose.com/random/qry.png

Remove Expr1 and Expr2. Don't put square btackets around OrderYear and
OrderMonth.

Steve


"Steve" wrote in message
...
date is a resrved word and if you are using it as a field name, you need
to change the name.

Create two blank fields at the beginning of your query. Assuming you
change Date to OrderDate, add this field in the first blank field of your
query:
OrderYear=Year([OrderDate])
Sort ascending on this field

Add this field in the second blank field of your query:
OrderMonth=Month([OrderDate])
Sort ascending on this field

Change the query to a Totals query. Click on the Sigma (looks like a
capital E) icon in the menu at the top of the screen. Change Group By
under Quantity to Sum. Change Group By under OrderNumber to Count.

Steve






"ZenMasta" wrote in message
...
Using access 2002 I made a query in design view that lists all orders for
a certain item over a few years. There are only 3 relevant columns
although others were used to get this info:
order number, quantity, date

I'm interested in knowing the steps to alter this query so it sums up
each month. Ultimately I want a report that shows a bar chart with the
quantity sold per month (and maybe show the number of orders too)

Here's my current query I made in design view

SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity,
tblInventory.ISBN_B, tblOrderHdr.UserID, tblOrderHdr.OrderDate
FROM (tblOrderHdr INNER JOIN tblOrderDetails ON tblOrderHdr.OrderHdrID =
tblOrderDetails.odOrderHdrID) INNER JOIN tblInventory ON
tblOrderDetails.odInvID = tblInventory.InvID
WHERE (((tblInventory.ISBN_B)="15706") AND ((tblOrderHdr.UserID) Is Not
Null) AND ((tblOrderHdr.OrderDate) Between #1/1/2000# And #12/8/2009#));

If this can be done in a report instead of exporting and me making the
bar chart in excel that would be cool but I can't use microsoft object
web components reference because it breaks my app.

I didn't put the expr and brackets there, when I pasted your example it
automatically does it.
If I try to correct it, it's automatically put it back.

"Steve" wrote in message
...
Remove Expr1 and Expr2. Don't put square btackets around OrderYear and
OrderMonth.

Steve


"Steve" wrote in message
...
date is a resrved word and if you are using it as a field name, you need
to change the name.

Create two blank fields at the beginning of your query. Assuming you
change Date to OrderDate, add this field in the first blank field of your
query:
OrderYear=Year([OrderDate])
Sort ascending on this field

Add this field in the second blank field of your query:
OrderMonth=Month([OrderDate])
Sort ascending on this field

Change the query to a Totals query. Click on the Sigma (looks like a
capital E) icon in the menu at the top of the screen. Change Group By
under Quantity to Sum. Change Group By under OrderNumber to Count.

Steve






"ZenMasta" wrote in message
...
Using access 2002 I made a query in design view that lists all orders
for a certain item over a few years. There are only 3 relevant columns
although others were used to get this info:
order number, quantity, date

I'm interested in knowing the steps to alter this query so it sums up
each month. Ultimately I want a report that shows a bar chart with the
quantity sold per month (and maybe show the number of orders too)

Here's my current query I made in design view

SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity,
tblInventory.ISBN_B, tblOrderHdr.UserID, tblOrderHdr.OrderDate
FROM (tblOrderHdr INNER JOIN tblOrderDetails ON tblOrderHdr.OrderHdrID =
tblOrderDetails.odOrderHdrID) INNER JOIN tblInventory ON
tblOrderDetails.odInvID = tblInventory.InvID
WHERE (((tblInventory.ISBN_B)="15706") AND ((tblOrderHdr.UserID) Is Not
Null) AND ((tblOrderHdr.OrderDate) Between #1/1/2000# And #12/8/2009#));

If this can be done in a report instead of exporting and me making the
bar chart in excel that would be cool but I can't use microsoft object
web components reference because it breaks my app.

My mistake!

Change to this:
OrderYear:Year([OrderDate])
OrderMonth:Month([OrderDate])

Steve




"ZenMasta" wrote in message
...
I didn't put the expr and brackets there, when I pasted your example it
automatically does it.
If I try to correct it, it's automatically put it back.

"Steve" wrote in message
...
Remove Expr1 and Expr2. Don't put square btackets around OrderYear and
OrderMonth.

Steve


"Steve" wrote in message
...
date is a resrved word and if you are using it as a field name, you need
to change the name.

Create two blank fields at the beginning of your query. Assuming you
change Date to OrderDate, add this field in the first blank field of
your query:
OrderYear=Year([OrderDate])
Sort ascending on this field

Add this field in the second blank field of your query:
OrderMonth=Month([OrderDate])
Sort ascending on this field

Change the query to a Totals query. Click on the Sigma (looks like a
capital E) icon in the menu at the top of the screen. Change Group By
under Quantity to Sum. Change Group By under OrderNumber to Count.

Steve






"ZenMasta" wrote in message
...
Using access 2002 I made a query in design view that lists all orders
for a certain item over a few years. There are only 3 relevant columns
although others were used to get this info:
order number, quantity, date

I'm interested in knowing the steps to alter this query so it sums up
each month. Ultimately I want a report that shows a bar chart with the
quantity sold per month (and maybe show the number of orders too)

Here's my current query I made in design view

SELECT tblOrderHdr.InvoiceNo, tblOrderDetails.Quantity,
tblInventory.ISBN_B, tblOrderHdr.UserID, tblOrderHdr.OrderDate
FROM (tblOrderHdr INNER JOIN tblOrderDetails ON tblOrderHdr.OrderHdrID
= tblOrderDetails.odOrderHdrID) INNER JOIN tblInventory ON
tblOrderDetails.odInvID = tblInventory.InvID
WHERE (((tblInventory.ISBN_B)="15706") AND ((tblOrderHdr.UserID) Is Not
Null) AND ((tblOrderHdr.OrderDate) Between #1/1/2000# And
#12/8/2009#));

If this can be done in a report instead of exporting and me making the
bar chart in excel that would be cool but I can't use microsoft object
web components reference because it breaks my app.