MS Access 2007 Data creation query

[1druid1]

Hi All

I have a table with the following fields

Date|Department|Process|Mon|Tue|Wed|Thu|Fri|Sat|Sun

The Data would be as example

Date |Department|Process|Mon|Tue|Wed|Thu|Fri |Sat|Sun
07/10/2013|Wood |Mould |250|250|200 |200 |100|0 |0
15/10/2013|Wood |Mould |300|300|350 |200 |100|0 |0
04/11/2013|Wood |Mould |222|222|200 |200 |100|0 |0

What I am wanting to do is create a query that does the following

Date |Department|Process|Amount
07/10/2013|Wood |Mould |250
08/10/2013|Wood |Mould |250
09/10/2013|Wood |Mould |200
10/10/2013|Wood |Mould |200
11/10/2013|Wood |Mould |100
12/10/2013|Wood |Mould |0
13/10/2013|Wood |Mould |0
14/10/2013|Wood |Mould |300
15/10/2013|Wood |Mould |300
16/10/2013|Wood |Mould |350
17/10/2013|Wood |Mould |200
18/10/2013|Wood |Mould |100
19/10/2013|Wood |Mould |0
20/10/2013|Wood |Mould |0
04/11/2013|Wood |Mould |222
05/10/2013|Wood |Mould |222
06/10/2013|Wood |Mould |200
07/10/2013|Wood |Mould |200
08/10/2013|Wood |Mould |100
09/10/2013|Wood |Mould |0
10/10/2013|Wood |Mould |0

So as you can see whatever week the date lands on it moves the Mon, Tue, Wed, Thu, Fri, Sat and Sun amounts to the correct dates associated to the week days


Just looking for a little help on how to structure this query.

Cheers

DJ

 

[pr2-eugin]

I hate being the bearer of grim news, but here it is.. Your data structure is wrong.. With this structure your result would be very hard to obtain, and not likely to get what you are after.. Lookup on Normalization. You need to sort the tables right..

 

[jdraw]

Suggest you redesign the table to deal with Daily Production.
Since Date is a reserved word in Access, I'd change it to ProductionDate or whatever makes sense to you. If you are dealing with Workdays (Mon-Fri), why are you reporting weekends?
You can determine Weekday from a full date
see http://www.techonthenet.com/access/functions/date/weekday.php and alsoWeekDayName
see http://www.techonthenet.com/access/functions/date/weekdayname.php


Table:

ProductionDate
Department
Process
Quantity

SELECT ProductionDate, Department, Process, Quantity
FROM Table
Order By ProductionDate

If you have multiple Departments, you may consider a separate Department table.

 

[plog]

You would need to use a UNION query (http://www.techonthenet.com/sql/union.php). This query would have 7 parts (one for each day of the week) and would look like this:

SELECT [Date] AS ProcessDate, Department, Process, Mon As Amount
FROM YourTableNameHere
UNION ALL
SELECT DateAdd("d", 1, [Date]) AS ProcessDate, Department, Process, Tue As Amount
FROM YourTableNameHere
UNION ALL
SELECT DateAdd("d", 2, [Date]) AS ProcessDate, Department, Process, Wed As Amount
FROM YourTableNameHere
UNION ALL
.
.
.

And I agree with everything the prior posters said about your structure.

 

[1druid1]

Hi Thanks for the replies.

Unfortuantly it isnt my database so I cant restructure anything as to much relies on how the tables are structured.

I have just been asked to create a specific report and the report needs the dates as I have shown in the example.

We work Sat and Sun on occasions so it has to be included.

The Field is called TgDate not Date, my mistake.

Unfortuantly I have to work with what is there, I can add tables and queries but cant modify any of the existing content.

Cheers

Dj

 

[1druid1]

Hi Plog

Thanks for the reply, its almost perfect only downside is the date might not fall on the monday of each week. So I will have to put in some checking on the date to see what day it is.

Cheers

Dj