Query of items ordered by month

  • Thread starter Thread starter ZenMasta
  • Start date Start date
Z

ZenMasta

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
(e-mail address removed)
 
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");
 
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,

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

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 said:
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
(e-mail address removed)





ZenMasta said:
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.
Click to expand...
Click to expand...
 
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 said:
Remove Expr1 and Expr2. Don't put square btackets around OrderYear and
OrderMonth.

Steve


Steve said:
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
(e-mail address removed)





ZenMasta said:
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.
Click to expand...
Click to expand...
Click to expand...
 
My mistake!

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

Steve




ZenMasta said:
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 said:
Remove Expr1 and Expr2. Don't put square btackets around OrderYear and
OrderMonth.

Steve


Steve said:
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
(e-mail address removed)





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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for that, but I still get an error
http://www.buggyonpurpose.com/random/qry2.png


Steve said:
My mistake!

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

Steve




ZenMasta said:
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 said:
Remove Expr1 and Expr2. Don't put square btackets around OrderYear and
OrderMonth.

Steve


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
(e-mail address removed)





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.
Click to expand...
Click to expand...
Click to expand...
 
Remove the OrderDate field from TblOrderDetails. You have the OrderDate in
TblOrderHdr; you don't need the date on each item ordered.

Steve


ZenMasta said:
Thanks for that, but I still get an error
http://www.buggyonpurpose.com/random/qry2.png


Steve said:
My mistake!

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

Steve




ZenMasta said:
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.

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

Steve


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
(e-mail address removed)





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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ken!
Perfect.

KenSheridan via AccessMonster.com said:
Try:

SELECT
YEAR(tblOrderHdr.OrderDate) AS OrderYear,
MONTH(tblOrderHdr.OrderDate) AS OrderMonth,
tblInventory.ISBN_B,
SUM(tblOrderDetails.Quantity) As MonthlyQuantity,
COUNT(*) AS NumberOfOrders
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
YEAR(tblOrderHdr.OrderDate),
MONTH(tblOrderHdr.OrderDate),
tblInventory.ISBN_B;

Ken Sheridan
Stafford, England
Click to expand...
 
It's not absurd! You have OrderDate in TblOrderHdr. You don;t need it for
every item in TblOrderDetail.
 
Sure it seems like that is a redundant field but your suggestion to destroy
data is ridiculous.
You have absolutely no idea if there is a form or query somewhere else in my
app that uses that field.

But whatever, Kens suggestion achieved the results I was looking for with
out table alteration.
 
A prime directive of database design is that a piece of data should be
stored only once. What's ridiculous is that you have violated this basic
design principle by storing OrderDate more than once; once in TblInvoiceHdr
and once in TblInvoiceDetail. Suppose February 11, 2010 got stored in
TblInvoiceHdr and for some reason February 9, 2010 got stored in
TblInvoiceDetail. A year from now you go back to the invoice record, how do
you know which is correct. Suppose February 11, 2010 got stored in
TblInvoiceHdr and for some reason February 11, 2009 got stored in
TblInvoiceDetail. A year from now you go back to the invoice record, how do
you know which is correct.

Another thing that is ridiculous is that there are forms or queries
somewhere else in your app that uses the InvoiceDate field in
TblInvoiceDetail. There is a high probability that the value in the
InvoiceDate field in TblInvoiceDetail is not the same as the InvoiceDate
field in TblInvoiceHdr. That makes your database very unreliable.

Finally, it is ridiculous that an auditor hasn't screamed about the design
of your database.

Steve
(e-mail address removed)
 
Back
Top