473,857 Members | 1,773 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2208
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******@hotma il.comwrote in message
news:12******** *****@corp.supe rnews.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,Con tactName,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*********@mp s.co.ukwrote in message
news:qN******** ************@ka roo.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******@hotma il.comwrote in message
news:12******** *****@corp.supe rnews.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
8981
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 databases... A google search takes me to various forums where I am forced to sign up before I can read any answers. Interesting note here is that the guy in the office next
2
6083
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 that is giving me headaches.) So I am thinking that I just need to do the parsing and calculating in an event procedure for an "On Click" event. My question is: If I have a query field in access97 that parses date (in format
1
3215
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 conditions but the next query that is run in the macro ends up deleting the previous interest value that has been generated by the query. For example if query 1 is run on the table with currency pair USD/CHF then the interest will be updated without any problem but if there is another entry in the...
4
2029
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 built in design mode and when I build it in sql mode after a certain point it give me the error message that the expression is to complex. Below is the sql code that I am using (this works so far, anything added to the code will give me the to complex error message.) SELECT .WONUM, .,...
8
5071
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 relationship. My question is, is there no way i can have a query that will pull a single field from all the tables. In other words i should have 44 fields. when i try to do that same, i get an error message saying "Query is too complex"
19
3665
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 on what it means, and the only workaround I've seen sofar, is to just rewrite my code/query. Situation: I am sorting forms into the way they are used (print,storage,readonly,etc...).
13
2320
by: DDonnachie | last post by:
Hi folks, The following SQL statment works SELECT StaffHoursTable.EngName, StaffHoursTable.JobNumberShadow, ++++++ AS Norm_Hours, ++++++ AS OT_Hours, wktodate(StaffHoursTable.WeekNo,StaffHoursTable.Year) AS WeekDate, Jobs.ProjectType, Jobs.ProjectDescription, wktodate(.,.) FROM (StaffHoursTable INNER JOIN Jobs ON StaffHoursTable.JobNumberShadow = Jobs.JobNumber) INNER JOIN EngStaff ON StaffHoursTable.EngName = EngStaff.Eng_Staff ...
3
15942
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 over 60 seconds to compile the sql statement. Is there any parameter that controls how long DB2 allows a statement to compile for.
0
2462
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. And some question about queries as the following 1. The wine name, grape variety, year, winery, and region 2. The minimum cost of wine in the inventory 3. The number of bottles available at the minimum price 4. The total number of bottles available at any price 5. The total number of unique...
3
2211
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 and all help is much appreciated this is the table, with sample data ------------------ | x_id | y_id | ------------------ | 3 | 9 | ------------------ | 1 | 2 |
0
9923
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9768
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11083
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10394
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7104
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5774
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5971
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4191
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3215
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.