Query/Report how many items ordered over the year

[Robbie]

I have created a report that shows how many items were ordered over the
course of one year for xyz products. I would like to enhance the query but
my skills are pretty limited so I am seeking some help.

I would like my query to display results similarly to how I have typed them
up in this example excel snapshot
Year
Item Number
List total for each item
List how many orders there were for each item (I forgot to include this in
my screenshot example)

This way I can export to excel and autosum all of the item totals while
still having quantities for each one. I was also thinking of making a bar or
line chart for when I do previous years.

http://www.buggyonpurpose.com/random/query-allproductsforyear.png


Originally my query had each item in the criteria but after I made the table
that contained the products and linked it instead and so far seems to work
exactly the same as if I had specified each item. The items in my query are
all duplicate items but different versions. This isn't software but for
example if it was lets say it was windows licenses and each item number is a
different version; home, pro, ultimate, what have you.

As you can see I am specifying the date range in the criteria but in my
ItemsPerYearQuery table there is a start/End Date as well as a criteria
field. I am okay with using manually setting the criteria in the query
instead of pulling it from a table but I thought it might allow more options
if I had the date range in a table.

You'll also see in my screenshot how the query currently displays

SELECT tblOrderDetails.OrderDate, tblOrderDetails.Quantity,
ItemsPerYearQuery.ISBNB
FROM ItemsPerYearQuery INNER JOIN (tblOrderDetails INNER JOIN tblInventory
ON tblOrderDetails.odInvID = tblInventory.InvID) ON ItemsPerYearQuery.ISBNB
= tblInventory.ISBN_B
WHERE (((tblOrderDetails.OrderDate)>=#1/1/2006# And
(tblOrderDetails.OrderDate)<#12/31/2006#));


THANKS!

 

[Clifford Bass]

Hi Robbie,

What you need is a summary query. In SQL it would look like this:

select ISBN_B, Count(*) as Order_Count, Sum(Quantity) as Total_Ordered
from tblInventory inner join tblOrderDetails on odInvID = InvID
where OrderDate between #1/1/2006# and #12/31/2006#
group by ISBN_B;

If you need the year in the results, change it to:

select Year(OrderDate) as Order_Year, ISBN_B, Count(*) as Order_Count,
Sum(Quantity) as Total_Ordered
from tblInventory inner join tblOrderDetails on odInvID = InvID
where OrderDate between #1/1/2006# and #12/31/2006#
group by Year(OrderDate), ISBN_B;

If you want to limit it to certain random items add the following to
the where clause:

and ISBN_B in ("16628", "09613", "17588", "33412", "01389", "16260")

Hope that helps,

Clifford Bass

 

[Gina Whipp]

Robbie,

First, let me start off by saying that this newsgroup is for FREE support.
While some find it neccessary to solicit, 99.9% are here offering FREE
support.

Please see in-line responses to what I could answer...

--
Gina Whipp

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

I have created a report that shows how many items were ordered over the
course of one year for xyz products. I would like to enhance the query but
my skills are pretty limited so I am seeking some help.

I would like my query to display results similarly to how I have typed
them up in this example excel snapshot
Year
Item Number
List total for each item
List how many orders there were for each item (I forgot to include this in
my screenshot example)

If you want your fields names to export a certain way, simply type:
WhatYouWantTheFieldNameToBe: TheActualFieldName, ie: Qty: Quantity

If you want Year to show type: OrderYear: Year([OrderDate]). You should
not use Year as a field name because it is an Access Reserved Word.

If you want to get totals then you need to create a 'Totals' query. Look at
Help for specific instructions, post back if you have problems.

This way I can export to excel and autosum all of the item totals while
still having quantities for each one. I was also thinking of making a bar
or line chart for when I do previous years.

http://www.buggyonpurpose.com/random/query-allproductsforyear.png


Originally my query had each item in the criteria but after I made the
table that contained the products and linked it instead and so far seems
to work exactly the same as if I had specified each item. The items in my
query are all duplicate items but different versions. This isn't software
but for example if it was lets say it was windows licenses and each item
number is a different version; home, pro, ultimate, what have you.

As you can see I am specifying the date range in the criteria but in my
ItemsPerYearQuery table there is a start/End Date as well as a criteria
field. I am okay with using manually setting the criteria in the query
instead of pulling it from a table but I thought it might allow more
options if I had the date range in a table.

You'll also see in my screenshot how the query currently displays

SELECT tblOrderDetails.OrderDate, tblOrderDetails.Quantity,
ItemsPerYearQuery.ISBNB
FROM ItemsPerYearQuery INNER JOIN (tblOrderDetails INNER JOIN tblInventory
ON tblOrderDetails.odInvID = tblInventory.InvID) ON
ItemsPerYearQuery.ISBNB = tblInventory.ISBN_B
WHERE (((tblOrderDetails.OrderDate)>=#1/1/2006# And
(tblOrderDetails.OrderDate)<#12/31/2006#));

This looks like you will have to open the query to change the dates. You
might want to put a prompt eith on a menu or in the query itself unless you
are the ONLY one using it. I wouldn't want End-Users changing the date.

 

[John... Visio MVP]

Hello Robbie,

If you don't get the help you need in the newsgroup, I can help you for a
modest fee. I provide help with Access applications for a reasonable fee.
Email me a copy of your database to (e-mail address removed) if you would like my
help.

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
Stevie is a known troll who likes to harass posters for work. He has proven
many times that his help is overpriced even when it is free.

John... Visio MVP