Week by Day

  • Thread starter Thread starter simplymidori
  • Start date Start date
S

simplymidori

I'm looking for some suggestions on how I can get my query to populate data
by day.

My data is as such;

Weekstart mon tue wed thu fri sat sun
2/18/08 1 1 3 4 2 1 1

_____________________________

I want to output

2/18/08 1
2/19/08 1
2/20/08 3
2/21/08 4

etc

Many thanks in advance
 
I'm looking for some suggestions on how I can get my query to populate data
by day.

My data is as such;

Weekstart mon tue wed thu fri sat sun
2/18/08 1 1 3 4 2 1 1
Click to expand...

Then your table is (as you no doubt have concluded) incorrectly structured.
_____________________________

I want to output

2/18/08 1
2/19/08 1
2/20/08 3
2/21/08 4

etc
Click to expand...

A "Normalizing Union" query will do the job:

SELECT [Weekstart] AS TheDate, [Mon] AS TheAmount FROM yourtable
UNION ALL
SELECT DateAdd("d", 1, [Weekstart]), [Tue] FROM yourtable
UNION ALL
SELECT DateAdd("d", 2, [Weekstart]), [Wed] FROM yourtable
UNION ALL
SELECT DateAdd("d", 3, [Weekstart]), [Thu] FROM yourtable
UNION ALL
SELECT DateAdd("d", 4, [Weekstart]), [Fri] FROM yourtable
<etc>
 
Back
Top