Dynamically Selecting Columns? 2

[danneedham]

Hi All

I'm trying to create a database I could use at work to replace the excel reports currently in place.

It is a retail weekly sales report - by store and store status.

I have several tables, store, store status, RTS Download (Sales Info).

I download the information and import it into the databas and append the current store status etc.

The Sales & Discount Data:
Year: eg 0708
Period: eg 01
Week: eg 01
Store: eg A0001
Status: eg NEW
Sunday: eg £800
Monday:
Tuesday:
Wednesday:
etc.
Disc.Sunday:
Disc.Monday:

I'm trying to think how to pull the data together for the weeks data, but the weeks that need reporting will change every week. I first thought of doing a crosstab query.

Any ideas how i could acheive a on the fly week selection, weeks that are included in the cumulative figure?

Many Thanks


Dan

 

[HarleyQuinn]

I'm not 100% sure I understand what you want (if I've misunderstood could you clarify your need for me and others?) but you could use a parameter in your query to specify the week (or week range) you require?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[danneedham]

Hi Sorry

Do you know what, completely hadnt thought of that. I can pass the criteria into the query from a form or something.

There is one other i would like to ask about. In my crosstab query currently looks like so:

Store (Row)
Status (Row)
Week (Column)
Value (Sum) - this is currently sales.

Would you suggest having two crosstabs - one to calculate sales, and one to calculate discounts and then bringing the two together?

Thanks Dan

 

[HarleyQuinn]

Glad you got the first part sorted

How/where are the sales and discount values stored? Same table?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[danneedham]

Yes its all stored in the same table as below:

Year: eg 0708
Period: eg 01
Week: eg 01
Store: eg A0001
Status: eg NEW
Sunday: eg £800
Monday:
Tuesday:
Wednesday:
etc.
Disc.Sunday:
Disc.Monday:

 

[lespaul]

Is there any way to alter the table structure? this structure isn't normalized...

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[HarleyQuinn]

Yes, I think that the two queries is the best idea (IMO).

I know this may go against my initial suggestion but it might be better to base your first query on form criteria rather than parameters, have a look at faq701-6763

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

I also have to agree with Leslie, normalized table structure would help you here.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[danneedham]

Hi The Sales data is from a query which holds:

Store No
Sales: Mon, Tues, Wed, etc
Discs: Mon, Tues etc

I add the year,period,week and status when i import the data, for the reports i will need to run from the data.

Basically the report will show for each store the total sales for this week, last week, discounts this week and last week. and also cumulatives for the year.

I'm not sure where to go on this one - I'm completely stuck!

Dan

 

[lespaul]

again, can you change the table structure? you are apparently importing data from some external source into Access tables..is it your application that you are importing into? If so you would be better off normalizing the table structure.

Does the imported information contain the "SalesDate"? if so then just include that in your import and then you can extract what day of the week that is and group your query by the day of the week and get the totals....what is making this hard is the non-normalized structure.

If it was set up like this:
[tt]
StoreNumber SalesDate Sales Discounts[/tt]

that would be closer to normalized. Even better would be:

[tt]
StoreNumber SalesDate Amount Type
A033 2008/1/15 5000 S
A033 2008/1/15 350 D
A033 2008/1/22 8000 S
A033 2008/1/22 500 D
[/tt]

then you just have to do a simple select query to add up each type by date (I don't remember the function to get the day of week, but here's how it would be used):

SELECT StoreNumber, DayOfWeek(SalesDate) As Weekday, Type, SUM(Amount) As Total FROM SalesTable

[tt]
StoreNumber WeekDay Type Total
A033 Tuesday S 13000
A033 Tuesday D 850
[/tt]




Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[danneedham]

Thanks Leslie

Your comments were very useful. I'll work on this and let you know how I get on!

Not sure that I'll be able to change the initial table too much. But once the data has beeen imported - I only need the week total.

So I think I might go along the lines of a append query which will process the data to make it more normalised.

Dan