top value question...

[NCRStinks]

Hi All

Was hoping to get some advise on a project i am doing for work. We're a retail business and report sales figures on a weekly basis.

at the moment the reports are generated in excel, and are very laborious!

I entend to have the following tables:

* Store
* Store Status (subdatasheet)
* Sales Info

the sales info will be downloaded from the accounts system and will show in the format of [Store] , [Sunday] , [Monday] , [Tuesday] , [Wednesday] , [Thursday] , [Friday] , [Saturday]

the store table is quite self explanitory - store id, name etc...

the store status table it will hold all records for the store status, ie, 01/01/01 the store was new, 01/01/02 the store becomes "like for like", and on 25/06/02 the store gets a refit.

the reports we generate are all based upon the stores status. so for example the report will show the current status for this week ie like for like, last week could be a refit - so will show on a seperate line.

in the cumulative section it may have a couple of weeks as new, a few weeks as like for like.

I've looked into the "top value" function, but that will only show the highest value? What i need is a top value as at a date, which can be changed to pull out different weeks...

Does anyone have any ideas on how this could be implemented??


Many Thanks


Dan

[Rabbit]

I'm not following, could you provide an example?

[NCRStinks]

I'm not following, could you provide an example?

Ok sorry, its quite complicated.

The report currently feeds from the downloads, and sums the weeks value.

This Week - Like for Like
Last Week - Like for Like

Cum - Like for Like
Cum - New

For example, a store opened on the 1st of October 2006.

Our financial year starts in Feb, so the report would hold feb through to october this year in the cumulative columns as "new"

From the first of october the store goes "like for like" so is reported on a different line...

STORE___Status_ ___TW________LW __________% VAR________ Etc
Store "a" Like4Like 20,000 18,000 etc

Cum Totals
STORE___Status_ ___ThisYr______ LastYr_________ _% VAR________ Etc
Store "a" Like4Like 40,000 0
Store "a" New 120,000 60,000


Hope this clarifies.

Was thinking, perhaps if i did queries for the weeks where i look at equals or less than, then do a top query on that - i would not have the problems??

Or if there would be a better way?


Dab

[Rabbit]

What is it that you're showing me? The reports or the records that the reports are based on?

If it's the reports, then I'm not sure how you're getting your numbers.
If it's the records, then I'm not sure what you're trying to accomplish.

Can you give me the following information?

Relevant Table Name
FieldName; PK/FK; Data Type
...

FieldName1   FieldName2   FieldName3
Sample       Record       1
Sample       Record       2
Sample       Record       3

Then describe what it is you're trying to accomplish, what you've tried to do so far, and what problems you are running into.

[NCRStinks]

What is it that you're showing me? The reports or the records that the reports are based on?

If it's the reports, then I'm not sure how you're getting your numbers.
If it's the records, then I'm not sure what you're trying to accomplish.

Can you give me the following information?

Relevant Table Name
FieldName; PK/FK; Data Type
...

FieldName1   FieldName2   FieldName3
Sample       Record       1
Sample       Record       2
Sample       Record       3

Then describe what it is you're trying to accomplish, what you've tried to do so far, and what problems you are running into.

Sorry if i stick to the bit I am confused about.

The problem is with pulling through a stores status "as at" a date.

For example

Week 1 - New
Week 2 - New
Week 3 - Like for Like
Week 4 - Like for Like

If in the status table I want to find the top value for a store, I want to be able to find it "as at" week 1, week 2, week 3 so that the correct status is pulled through.

Would you suggest perhaps a query where i filter the table to <= week 1 and then run a topvalue?

Also i am unsure how to return a value for every store as top value only returns one?

Thanks


Dan

[Rabbit]

I think you're looking for the Max and Grouping by Score.
Not exactly what you're looking for but it gives you the main idea.

StoreID   Income
1          200
1          300
1          450
2          230
2          340
2          897

[Code=sql]
SELECT StoreID, Max(Income) AS MaxOfIncome
FROM Table1
GROUP BY StoreID;
[/Code]

StoreID   MaxOfIncome
1          450
2          897