complex Query help

[Wired Hosting News]

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?

[Terry Kreft]

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?

[pietlinden]

Wired Hosting News wrote:

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

Maybe you should start by reading up on Allen Browne's notes on
calculating quantity on hand on the fly. See
http://www.allenbrowne.com/AppInventory.html

then if you got sales data, you could calculate what you need.

That aside, you can probably get what you want by basing a report on a
crosstab query.

[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?