473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

top value question...

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
Oct 31 '07 #1
5 1381
Rabbit
12,516 Expert Mod 8TB
I'm not following, could you provide an example?
Nov 1 '07 #2
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
Nov 1 '07 #3
Rabbit
12,516 Expert Mod 8TB
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
...

Expand|Select|Wrap|Line Numbers
  1. FieldName1   FieldName2   FieldName3
  2. Sample       Record       1
  3. Sample       Record       2
  4. Sample       Record       3
  5.  
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.
Nov 1 '07 #4
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
...

Expand|Select|Wrap|Line Numbers
  1. FieldName1   FieldName2   FieldName3
  2. Sample       Record       1
  3. Sample       Record       2
  4. Sample       Record       3
  5.  
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
Nov 2 '07 #5
Rabbit
12,516 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. StoreID   Income
  2. 1          200
  3. 1          300
  4. 1          450
  5. 2          230
  6. 2          340
  7. 2          897
  8.  
Expand|Select|Wrap|Line Numbers
  1. SELECT StoreID, Max(Income) AS MaxOfIncome
  2. FROM Table1
  3. GROUP BY StoreID;
  4.  
Expand|Select|Wrap|Line Numbers
  1. StoreID   MaxOfIncome
  2. 1          450
  3. 2          897
  4.  
Nov 2 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: FLEB | last post by:
Okay, so I've got this XML: <qa> <questionset> <question name="yourname">What is your name?</question> <question name="yourquest">What is your quest?</question> <question name="favcolor"> What...
1
by: Display Name | last post by:
Used one of these canned scripts to set up a JS quiz but not before having used another canned PHP script for "Tell your friend about this Web page!" sort of thing. Now i've gotta integrate them;...
29
by: garyusenet | last post by:
I'm trying to investigate the maximum size of different variable types. I'm using INT as my starting variable for exploration. I know that the maximum number that the int variable can take is:...
21
by: Steven T. Hatton | last post by:
I'm trying to improve my formal understanding of C++. One significant part of that effort involves clarifying my understanding of the vocabulary used to describe the language. This is from the...
1
by: ahmurad | last post by:
Dear all, I am new group user, computer science graduate; just have joined this established group and thanks to all. I am working in network field but so much interested in web (PHP) field. Recently...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.