472,354 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,354 software developers and data experts.

complex Query help

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?
Jul 2 '06 #1
3 2085
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?


Jul 2 '06 #2

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.

Jul 2 '06 #3
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?



Jul 2 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Starbuck | last post by:
OK, first let me say that I am no DB person. But I have a user here who keeps getting this error whenever she does, whatever it is she does, with...
2
by: Mikel | last post by:
I am trying to get around the problem "The expression you have entered is too complex" for a select query. (The example below is not the expression...
1
by: ravi | last post by:
I have created the following interest to calculate the interest for the following currency pairs. I have tried to combine them in macros using...
4
by: ED | last post by:
I am attempting to to write a query that has a numerous nested IIf statements. The problem that I am having is that it is to long of a query to be...
8
by: Matt | last post by:
Hi all, Thank you for taking the time. I have a database with 45 tables on it. 44 tables are linked to a main table through a one to one...
19
by: kawaks40 | last post by:
Hi everyone :) I just recently started using access/sql. and right away I ran into this problem "SQL expression too complex" I google'd a lot...
13
by: DDonnachie | last post by:
Hi folks, The following SQL statment works SELECT StaffHoursTable.EngName, StaffHoursTable.JobNumberShadow, ++++++ AS Norm_Hours, ++++++ AS...
3
by: Eric Davidson | last post by:
DB2 9.5 I keep geting the message. SQL0101N The statement is too long or too complex. SQLSTATE=54001 When one of my sql statements takes...
0
crystal2005
by: crystal2005 | last post by:
Hi, I am having trouble with some complex SQL queries. Iíve got winestore database, taken from Web Database Application with PHP and MySQL book. ...
3
by: william67 | last post by:
I'm having a hard time building a query to do what I need to do and was hoping some genius could help me out, I need to do a complex query and any...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.