Complex query calculating sales - question explained

[Wired Hosting News]

I tried to be breif and give a scenario so as not to be overlooked because
it was soooo long. Let me give you real world.

I am a manufacturer of goods and produce 11 items that are distributed to
1800 stores of a national home improvement chain store. Every week I
electronicaly receive an excel spreadsheet "inventory report" with 19,800
rows or records, which I import into my tblSalesData table. The table now
has 10 weeks of data or approx 200,000 records.

The reports that are sent have a store number, item number and inventory "on
hand" for each item, each store that is present at the end of that week; no
sales data, no on-order data.

Every week I am also having accounting generate a spreadsheet of purchase
orders receieved which I am importing into another table.

I have what I think is a normalized database with the following table with
the primary in quotes:

StoreInfo: "StoreNum", Address, MarketNum, ManagerName,ContactName,etc
ProductInfo: "ItemNum", Description, Buypack, UPC code
SalesData: "StoreNum", "ItemNum", "ReportDate", OnHandQty
POInfo: "StoreNum", "ItemNum", "PODate", PONum, QtyOrdered

What I would like to do is take a 10 week snapshot of all stores, or one
store at a time of inventory levels, and of sales trends. In other words, I
would like to show a store with all 11 items listed in rows, each row
containing 10 columns of data.

For inventory qty's, I think that should be easy enough th pull out with a
query into a form or report since I have the inventory data. So I can see
this.

Store# Item Inventory2/1/06 2/8/06 2/15/06 2/23/06

That will give me a visual of how their inventory looks for a period of
time.

But what I really would like to see is a sales trend. Like:

Store# Item Sales2/1/06 2/8/06 2/15/06 2/23/06

That way, I can visually see which items arent moving in which stores;
identify those stores that have more then one item not performing, and I
also would like to analyze all stores and come up with the least to most
performing stores based on all item in each store, so I can hand thos "bad"
stores off to Customer Service.

How can I calculate sales for a given week. I need something like this:
Find the inventory on the newest report date for store# / item# ; then find
the next newest report date and find the inventory for the same item and
subtract, find the next newest, etc.

I hope that made more sense. Any help or direction will be appreciated. I
am new to access, but have VBA experience with Excel. I am reading "MS
Office Access 2003 Inside Out", John L. Viesas and I will read what the
other poster gave me to read also.

Eventualy I would like to have a report that shows for each store; the
onhand, 8 weeks of sales history, and qty on order, so I can see the health
of each store.

These days, inventory management is the sole responsibility of the supplier
when dealing with this chain, and I want to take the bull by the horn and
make sure our program is successful.

Thank you all.

************************************************** **************************

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:qN********************@karoo.co.uk...

You seem to have left some parts of the transactions out of your
description, do you not take into account purchases or returns (from and
to
stock) for example. How do these shops get their stock?
--

Terry Kreft
"Wired Hosting News" <jt******@hotmail.comwrote in message
news:12*************@corp.supernews.com...

>Lets say I have 10 products in 10 different stores and every week I get a
report from each store telling me how many items they have left for each

of

>the 10 products.

So each week I enter in 100 records with a store number, item number, on
hand qty, and date of report into my sales data table.

My tables primary is a combo of store# & Item number & Date

After 10 week of this I have 1000 record total with ten unique dates.

How can I figure the sales each week for each item in each store, for eg.

Store# Item# Week1 Week 2 Week 3 etc
1 112 2 0 1

I think this could not be done in a query data view and should be done in

a

>report, but my question is how do I pull the info for each week and

subtract

>it from the previous weeks inventory and then the next...

I would like to be able to view a store, showing all 10 items listed in

rows

>with a column for each weeks sales either in a query dataview or report.

I

>would then like to say " OK, what stores have low sales for many of the
items they carry. " I would like to identify those stores that aren't
performing. A store that hasnt sold anything on 5 of the product for the
last 4 weeks is worse off then a store that hasnt sold anything on two

item

>for two weeks...

Any suggestions?
Does this need to be done in VBA first?

[Bob Quintal]

Well done.

For you sales, you need to join the inventory table on itself,
with the joins on storenum and itemnum. Alisas the tabkles as CW
(CurrentWeek) and PW (PreviousWeek) you will also need to filter
the second copy to limit the result to the previous week, using
the dateAdd() function as the criteria. I'd put =dateAdd("ww",-
1, CW.ReportDate) in the PW.ReportDate criteria. Your sales is
calculated from the CW.OnHandQty-PW.OnhandQty.

Once that is working to your satisfaction, you'll note that
replenishments will give negative sales numbers. We'll need to
left join what we have to the orders table, I'm unclear as to
how that table is set up, so I'll not offer advice on handling
it yet.

This will give you a query that lists the sales by store, item
and week. You then need to create a crosstab query that shows
the store and item as row headings, the date as column headings
and the sales (calculated above) as value. since you start with
one record per condition anyways, sum, max and avg should all
give the same result for the value.
"Wired Hosting News" <jt******@hotmail.comwrote in
news:12*************@corp.supernews.com:

I tried to be breif and give a scenario so as not to be
overlooked because it was soooo long. Let me give you real
world.

I am a manufacturer of goods and produce 11 items that are
distributed to 1800 stores of a national home improvement
chain store. Every week I electronicaly receive an excel
spreadsheet "inventory report" with 19,800 rows or records,
which I import into my tblSalesData table. The table now has
10 weeks of data or approx 200,000 records.

The reports that are sent have a store number, item number and
inventory "on hand" for each item, each store that is present
at the end of that week; no sales data, no on-order data.

Every week I am also having accounting generate a spreadsheet
of purchase orders receieved which I am importing into another
table.

I have what I think is a normalized database with the
following table with the primary in quotes:

StoreInfo: "StoreNum", Address, MarketNum,
ManagerName,ContactName,etc ProductInfo: "ItemNum",
Description, Buypack, UPC code SalesData: "StoreNum",
"ItemNum", "ReportDate", OnHandQty POInfo: "StoreNum",
"ItemNum", "PODate", PONum, QtyOrdered

What I would like to do is take a 10 week snapshot of all
stores, or one store at a time of inventory levels, and of
sales trends. In other words, I would like to show a store
with all 11 items listed in rows, each row containing 10
columns of data.

For inventory qty's, I think that should be easy enough th
pull out with a query into a form or report since I have the
inventory data. So I can see this.

Store# Item Inventory2/1/06 2/8/06 2/15/06
2/23/06

That will give me a visual of how their inventory looks for a
period of time.

But what I really would like to see is a sales trend. Like:

Store# Item Sales2/1/06 2/8/06 2/15/06 2/23/06

That way, I can visually see which items arent moving in which
stores; identify those stores that have more then one item not
performing, and I also would like to analyze all stores and
come up with the least to most performing stores based on all
item in each store, so I can hand thos "bad" stores off to
Customer Service.

How can I calculate sales for a given week. I need something
like this: Find the inventory on the newest report date for
store# / item# ; then find the next newest report date and
find the inventory for the same item and subtract, find the
next newest, etc.

I hope that made more sense. Any help or direction will be
appreciated. I am new to access, but have VBA experience with
Excel. I am reading "MS Office Access 2003 Inside Out", John
L. Viesas and I will read what the other poster gave me to
read also.

Eventualy I would like to have a report that shows for each
store; the onhand, 8 weeks of sales history, and qty on order,
so I can see the health of each store.

These days, inventory management is the sole responsibility of
the supplier when dealing with this chain, and I want to take
the bull by the horn and make sure our program is successful.

Thank you all.

************************************************** ************

*

*************

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:qN********************@karoo.co.uk...

>You seem to have left some parts of the transactions out of
your description, do you not take into account purchases or
returns (from and to
stock) for example. How do these shops get their stock?
--

Terry Kreft
"Wired Hosting News" <jt******@hotmail.comwrote in message
news:12*************@corp.supernews.com...

>>Lets say I have 10 products in 10 different stores and every
week I get a report from each store telling me how many
items they have left for each

of

>>the 10 products.

So each week I enter in 100 records with a store number,
item number, on hand qty, and date of report into my sales
data table.

My tables primary is a combo of store# & Item number & Date

After 10 week of this I have 1000 record total with ten
unique dates.

How can I figure the sales each week for each item in each
store, for eg.

Store# Item# Week1 Week 2 Week 3
etc 1 112 2 0
1

I think this could not be done in a query data view and
should be done in

a

>>report, but my question is how do I pull the info for each
week and

subtract

>>it from the previous weeks inventory and then the next...

I would like to be able to view a store, showing all 10
items listed in

rows

>>with a column for each weeks sales either in a query
dataview or report.

I

>>would then like to say " OK, what stores have low sales for
many of the items they carry. " I would like to identify
those stores that aren't performing. A store that hasnt
sold anything on 5 of the product for the last 4 weeks is
worse off then a store that hasnt sold anything on two

item

>>for two weeks...

Any suggestions?
Does this need to be done in VBA first?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dean]

Bob Quintal wrote:

Store# Item Inventory2/1/06 2/8/06 2/15/06
2/23/06

That will give me a visual of how their inventory looks for a
period of time.

But what I really would like to see is a sales trend. Like:

Store# Item Sales2/1/06 2/8/06 2/15/06 2/23/06

I am not a big fan of cross tab queries, however, to create a sales
report (query) like the one you described, you might look into cross
tabs. They work great as queries, which export to excel nicely if you
are going to export.

That way, I can visually see which items arent moving in which
stores; identify those stores that have more then one item not
performing, and I also would like to analyze all stores and
come up with the least to most performing stores based on all
item in each store, so I can hand thos "bad" stores off to
Customer Service.

I view this as another report, not to tie into the main one noted
above.

[Mike Gramelspacher]

In article <11**********************@p79g2000cwp.googlegroups .com>,
no*****@coveyaccounting.com says...

>
Bob Quintal wrote:

>
Store# Item Inventory2/1/06 2/8/06 2/15/06
2/23/06
>
That will give me a visual of how their inventory looks for a
period of time.
>
But what I really would like to see is a sales trend. Like:
>
Store# Item Sales2/1/06 2/8/06 2/15/06 2/23/06

I am not a big fan of cross tab queries, however, to create a sales
report (query) like the one you described, you might look into cross
tabs. They work great as queries, which export to excel nicely if you
are going to export.

>
That way, I can visually see which items arent moving in which
stores; identify those stores that have more then one item not
performing, and I also would like to analyze all stores and
come up with the least to most performing stores based on all
item in each store, so I can hand thos "bad" stores off to
Customer Service.

I view this as another report, not to tie into the main one noted
above.

Only thing I can suggest is to google for newsgroup items by "Tom
Ellison" and the keyword Crosstab. Maybe also with Alternative. In his
method the numbers are generated by individual subqueries. In your
example you need 4 subqueries for the "sales" and 4 expressions for the
dates. The good thing is that the initial date could be a date
parameter and the other dates could be calculated from that date. The
sales periods to total would also be gotten from the date parameter.
The query could run for any 4-week period by just asking for the first
date. This is the general concept as I understand it.

[John T Ingato]

Thank you Bob. That worked. As for the orders table, I will ask for
advise when I better know how that will be permanantly set up. I am still a
beginer with Acces.

Since I will be using that sales data on a regular basis and the query does
take some time to calculate the sales data, would it, ad is it possible to
save that query info, the calculated sales data, into a table of its own?
That way I can update that table every week when I import the new weekly
report ( either append the new data or completely rewrite the table.

Does that make sense?
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...

Well done.

For you sales, you need to join the inventory table on itself,
with the joins on storenum and itemnum. Alisas the tabkles as CW
(CurrentWeek) and PW (PreviousWeek) you will also need to filter
the second copy to limit the result to the previous week, using
the dateAdd() function as the criteria. I'd put =dateAdd("ww",-
1, CW.ReportDate) in the PW.ReportDate criteria. Your sales is
calculated from the CW.OnHandQty-PW.OnhandQty.

Once that is working to your satisfaction, you'll note that
replenishments will give negative sales numbers. We'll need to
left join what we have to the orders table, I'm unclear as to
how that table is set up, so I'll not offer advice on handling
it yet.

This will give you a query that lists the sales by store, item
and week. You then need to create a crosstab query that shows
the store and item as row headings, the date as column headings
and the sales (calculated above) as value. since you start with
one record per condition anyways, sum, max and avg should all
give the same result for the value.
"Wired Hosting News" <jt******@hotmail.comwrote in
news:12*************@corp.supernews.com:

>I tried to be breif and give a scenario so as not to be
overlooked because it was soooo long. Let me give you real
world.

I am a manufacturer of goods and produce 11 items that are
distributed to 1800 stores of a national home improvement
chain store. Every week I electronicaly receive an excel
spreadsheet "inventory report" with 19,800 rows or records,
which I import into my tblSalesData table. The table now has
10 weeks of data or approx 200,000 records.

The reports that are sent have a store number, item number and
inventory "on hand" for each item, each store that is present
at the end of that week; no sales data, no on-order data.

Every week I am also having accounting generate a spreadsheet
of purchase orders receieved which I am importing into another
table.

I have what I think is a normalized database with the
following table with the primary in quotes:

StoreInfo: "StoreNum", Address, MarketNum,
ManagerName,ContactName,etc ProductInfo: "ItemNum",
Description, Buypack, UPC code SalesData: "StoreNum",
"ItemNum", "ReportDate", OnHandQty POInfo: "StoreNum",
"ItemNum", "PODate", PONum, QtyOrdered

What I would like to do is take a 10 week snapshot of all
stores, or one store at a time of inventory levels, and of
sales trends. In other words, I would like to show a store
with all 11 items listed in rows, each row containing 10
columns of data.

For inventory qty's, I think that should be easy enough th
pull out with a query into a form or report since I have the
inventory data. So I can see this.

Store# Item Inventory2/1/06 2/8/06 2/15/06
2/23/06

That will give me a visual of how their inventory looks for a
period of time.

But what I really would like to see is a sales trend. Like:

Store# Item Sales2/1/06 2/8/06 2/15/06 2/23/06

That way, I can visually see which items arent moving in which
stores; identify those stores that have more then one item not
performing, and I also would like to analyze all stores and
come up with the least to most performing stores based on all
item in each store, so I can hand thos "bad" stores off to
Customer Service.

How can I calculate sales for a given week. I need something
like this: Find the inventory on the newest report date for
store# / item# ; then find the next newest report date and
find the inventory for the same item and subtract, find the
next newest, etc.

I hope that made more sense. Any help or direction will be
appreciated. I am new to access, but have VBA experience with
Excel. I am reading "MS Office Access 2003 Inside Out", John
L. Viesas and I will read what the other poster gave me to
read also.

Eventualy I would like to have a report that shows for each
store; the onhand, 8 weeks of sales history, and qty on order,
so I can see the health of each store.

These days, inventory management is the sole responsibility of
the supplier when dealing with this chain, and I want to take
the bull by the horn and make sure our program is successful.

Thank you all.

************************************************* *************

*

>*************

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:qN********************@karoo.co.uk...

>>You seem to have left some parts of the transactions out of
your description, do you not take into account purchases or
returns (from and to
stock) for example. How do these shops get their stock?
--

Terry Kreft
"Wired Hosting News" <jt******@hotmail.comwrote in message
news:12*************@corp.supernews.com...
Lets say I have 10 products in 10 different stores and every
week I get a report from each store telling me how many
items they have left for each
of
the 10 products.

So each week I enter in 100 records with a store number,
item number, on hand qty, and date of report into my sales
data table.

My tables primary is a combo of store# & Item number & Date

After 10 week of this I have 1000 record total with ten
unique dates.

How can I figure the sales each week for each item in each
store, for eg.

Store# Item# Week1 Week 2 Week 3
etc 1 112 2 0
1

I think this could not be done in a query data view and
should be done in
a
report, but my question is how do I pull the info for each
week and
subtract
it from the previous weeks inventory and then the next...

I would like to be able to view a store, showing all 10
items listed in
rows
with a column for each weeks sales either in a query
dataview or report.
I
would then like to say " OK, what stores have low sales for
many of the items they carry. " I would like to identify
those stores that aren't performing. A store that hasnt
sold anything on 5 of the product for the last 4 weeks is
worse off then a store that hasnt sold anything on two
item
for two weeks...

Any suggestions?
Does this need to be done in VBA first?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"John T Ingato" <jt******@hotmail.comwrote in
news:12*************@corp.supernews.com:

Thank you Bob. That worked. As for the orders table, I will
ask for advise when I better know how that will be permanantly
set up. I am still a beginer with Acces.

Since I will be using that sales data on a regular basis and
the query does take some time to calculate the sales data,
would it, ad is it possible to save that query info, the
calculated sales data, into a table of its own? That way I can
update that table every week when I import the new weekly
report ( either append the new data or completely rewrite the
table.

Does that make sense?

Yes, it sometimes makes sense to build temporary tables. There
are several options that you could think about.
1) calculate just this weeks data and add it to a permanent
table.
2) Empty the table structure and just append the records.
3) Overwrite the old table with new.

All would be done with simple queries, based on your existing
query,-- an append query for option 1, a delete query and append
query for option 2 and a make-table query for option 3.

Q

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...

>Well done.

For you sales, you need to join the inventory table on
itself, with the joins on storenum and itemnum. Alisas the
tabkles as CW (CurrentWeek) and PW (PreviousWeek) you will
also need to filter the second copy to limit the result to
the previous week, using the dateAdd() function as the
criteria. I'd put =dateAdd("ww",- 1, CW.ReportDate) in the
PW.ReportDate criteria. Your sales is calculated from the
CW.OnHandQty-PW.OnhandQty.

Once that is working to your satisfaction, you'll note that
replenishments will give negative sales numbers. We'll need
to left join what we have to the orders table, I'm unclear as
to how that table is set up, so I'll not offer advice on
handling it yet.

This will give you a query that lists the sales by store,
item and week. You then need to create a crosstab query that
shows the store and item as row headings, the date as column
headings and the sales (calculated above) as value. since you
start with one record per condition anyways, sum, max and avg
should all give the same result for the value.
"Wired Hosting News" <jt******@hotmail.comwrote in
news:12*************@corp.supernews.com:

>>I tried to be breif and give a scenario so as not to be
overlooked because it was soooo long. Let me give you real
world.

I am a manufacturer of goods and produce 11 items that are
distributed to 1800 stores of a national home improvement
chain store. Every week I electronicaly receive an excel
spreadsheet "inventory report" with 19,800 rows or records,
which I import into my tblSalesData table. The table now
has 10 weeks of data or approx 200,000 records.

The reports that are sent have a store number, item number
and inventory "on hand" for each item, each store that is
present at the end of that week; no sales data, no
on-order data.

Every week I am also having accounting generate a
spreadsheet of purchase orders receieved which I am
importing into another table.

I have what I think is a normalized database with the
following table with the primary in quotes:

StoreInfo: "StoreNum", Address, MarketNum,
ManagerName,ContactName,etc ProductInfo: "ItemNum",
Description, Buypack, UPC code SalesData: "StoreNum",
"ItemNum", "ReportDate", OnHandQty POInfo: "StoreNum",
"ItemNum", "PODate", PONum, QtyOrdered

What I would like to do is take a 10 week snapshot of all
stores, or one store at a time of inventory levels, and of
sales trends. In other words, I would like to show a store
with all 11 items listed in rows, each row containing 10
columns of data.

For inventory qty's, I think that should be easy enough th
pull out with a query into a form or report since I have the
inventory data. So I can see this.

Store# Item Inventory2/1/06 2/8/06 2/15/06
2/23/06

That will give me a visual of how their inventory looks for
a period of time.

But what I really would like to see is a sales trend. Like:

Store# Item Sales2/1/06 2/8/06 2/15/06
2/23/06

That way, I can visually see which items arent moving in
which stores; identify those stores that have more then one
item not performing, and I also would like to analyze all
stores and come up with the least to most performing stores
based on all item in each store, so I can hand thos "bad"
stores off to Customer Service.

How can I calculate sales for a given week. I need
something like this: Find the inventory on the newest report
date for store# / item# ; then find the next newest report
date and find the inventory for the same item and subtract,
find the next newest, etc.

I hope that made more sense. Any help or direction will be
appreciated. I am new to access, but have VBA experience
with Excel. I am reading "MS Office Access 2003 Inside
Out", John L. Viesas and I will read what the other poster
gave me to read also.

Eventualy I would like to have a report that shows for each
store; the onhand, 8 weeks of sales history, and qty on
order, so I can see the health of each store.

These days, inventory management is the sole responsibility
of the supplier when dealing with this chain, and I want to
take the bull by the horn and make sure our program is
successful.

Thank you all.

************************************************** ***********

>>*

*

>>*************

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:qN********************@karoo.co.uk...
You seem to have left some parts of the transactions out of
your description, do you not take into account purchases or
returns (from and to
stock) for example. How do these shops get their stock?
--

Terry Kreft
"Wired Hosting News" <jt******@hotmail.comwrote in
message news:12*************@corp.supernews.com...
Lets say I have 10 products in 10 different stores and
every week I get a report from each store telling me how
many items they have left for each
of
the 10 products.
>
So each week I enter in 100 records with a store number,
item number, on hand qty, and date of report into my sales
data table.
>
My tables primary is a combo of store# & Item number &
Date
>
After 10 week of this I have 1000 record total with ten
unique dates.
>
How can I figure the sales each week for each item in each
store, for eg.
>
Store# Item# Week1 Week 2 Week 3
etc 1 112 2 0
1
>
I think this could not be done in a query data view and
should be done in
a
report, but my question is how do I pull the info for each
week and
subtract
it from the previous weeks inventory and then the next...
>
I would like to be able to view a store, showing all 10
items listed in
rows
with a column for each weeks sales either in a query
dataview or report.
I
would then like to say " OK, what stores have low sales
for many of the items they carry. " I would like to
identify those stores that aren't performing. A store
that hasnt sold anything on 5 of the product for the last
4 weeks is worse off then a store that hasnt sold anything
on two
item
for two weeks...
>
Any suggestions?
Does this need to be done in VBA first?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com