If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#3
|
|||
|
|||
Query of items ordered by month
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. . |
#4
|
|||
|
|||
Query of items ordered by month
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 |
#5
|
|||
|
|||
Query of items ordered by month
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 |
#6
|
|||
|
|||
Query of items ordered by month
I wasn't using date as a field name either.
No luck yet. http://www.buggyonpurpose.com/random/qry.png |
#7
|
|||
|
|||
Query of items ordered by month
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 |
#8
|
|||
|
|||
Query of items ordered by month
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. |
#9
|
|||
|
|||
Query of items ordered by month
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. |
#10
|
|||
|
|||
Query of items ordered by month
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. |
Thread Tools | |
Display Modes | |
|
|