Day, Week and Month Summary

[SDS100UK]

Hi,

I have a query which has the sales figures of 11 different products sold on a daily basis. What I need is to be able to compare yesterdays figure with the this weeks figure and this months figure.

So I need to be able to see 3 seperate figues for each product sold.

I have 3 queries Daily, Weekly and Monthly. I have tried to join them in a fourth by product but it does not seem to work. It seems to give me far too many records!

I feel it is going wrong in the joing of them in the fourth query.

Please can some one stop me from going mad!!!!

Many thanks


Steven

 

[Nancy2]

In your query, do you have one instance of the product table with 3 joins? going to each of the queries?

 

[SDS100UK]

Nancy2,

No I have attempted to join the 3 individual queries, daily joined to weekly in turn weekly joined to monthly.

What do you suspect??

Steven

 

[Nancy2]

1) Delete all your Joins
2) You should have 1 table (product) and the 3 queries on on the screen.
3) Join product to EACH of the 3 queries - so you'll have products with 3 joins and each query with one join.

This should work -- one problem you might have is if any of the queries do not have a certain product, it will no appear -- in that instance, change the each join's type to include all records from the product table.

In the grid, the product code should be pulled from the product table (not any of the queries)

 

[SDS100UK]

Nancy,

If only life were that simple!!

My main table has the date the products were sold and the products themselves along with the qty sold.

There is nothing I can do about this I'm afraid.
So with this table I have my 3 queries mentioned above. I did what you suggested but I am afraid it did not work.
What it is doing in the 'Summary' query is giving me the Day's sales for product 1 followed by The week's sales for product 1 then the month's sales for product 1. Which is fine except on the next row it gives me the first days figure again, then the next weeks figure and then the month figure again! It repeats each of the weeks figures along side the different days and different months figures!

Does that make sense (my explanation not the design))?

I REALLY hope you have some good news for me though!!

Many many thanks


Steven

 

[Nancy2]

a quick thought - create a unique list of products from the product table - i.e., bring the product ID into the table, set the query property: Unique to Yes - then use this query instead of the product table in the query we were discussing? Then should not repeat - the only field you should have in the Product Query is Product ID

 

[SDS100UK]

Nancy2,

Did as you suggested but unfortunately it has not worked.

It is still duplicating the reults from each of the other queries. Here is a sample


Date Skill Daily Weekly Monthly
01/08/2001 General 4261 14294 103297
01/08/2001 General 4261 20170 103297
01/08/2001 General 4261 20743 103297
01/08/2001 General 4261 22767 103297
01/08/2001 General 4261 25323 103297
01/08/2001 Home 0 0 0
01/08/2001 Instant 29 98 1230
The Weekly figures are correct but why is it repeating the Daily figures for each week? This is only a small sample of the 1000's of records generated.

Hope this helps you help me!!

Many thanks

Steven

 

[Nancy2]

You need to turn on grouping - summing daily weekly and monthly.

 

[SDS100UK]

Tried that already but it gives me an incorrect figure as it some all the duplicates in as well

Here is an example.
Date Skill Day Week Month
01/08/2001 General 21305 103297 516485

I REALLY appreciate all your help, and the time you have taken out of what I assume is a busy schedule. I cant even think of any work arounds.

What now??

Steven

 

[Sawatzky]

dear Steven ,

I asume that your weekly query gives you a record for each week in a month

so for August 2001 having 5 weeks you have a result of 5 records

whereas daily only gives 1 line and monthly also only gives 1 line

now combining the resultlists gives you five lines as there are 5 weeks in august 2001 * 1 day * 1 month

You have to ristrict the weekly query to the current week to have only one record for weekly query


HTH

regards Astrid

 

[SDS100UK]

Nancy2 & Sawatzky,

Thank you both SOOOOOOO uch. With your input and that of another I have done it.

I can now go and enjoy my weekend ready to continue from this point the other side of a massive brick wall.

Once again thank you


Steven