How to Query one table easily?

[bewsh1987]

Hi All



Please I Need help, I have the table set below called Daily task what I would like is this to be placed into a listed query please see the query table below

I would like to do this with no VBA (Feel free to provide this for future projects :-) )

I have tried Union/Union All in the SQL however I found I needed to make 7 queries to get the result I wanted

is there a quicker method using only one query (Like Excels pivot table) or maybe a detailed written SQL?


if it make it easier I can change the Yes's to show the Title(Day)

I really hope I explained this well!! What is needed feel free to ask

And if there is no easy method please tell me

Thank you


Table

ID	Weekly Task	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Weekly/Fortnightly
1	Task 1	Yes	Yes	Yes	Yes	Yes	No	0	Weekly
2	Task 2	Yes	Yes	Yes	No	Yes	No	No	Weekly
3	Task 3	Yes	Yes	Yes	Yes	Yes	No	No	Weekly
4	Task 4	Yes	Yes	Yes	Yes	Yes	No	No	Weekly
5	Task 5	Yes	No	No	No	Yes	No	No	Weekly
6	Task 6	Yes	Yes	Yes	Yes	Yes	No	No	Weekly
7	Task 7	No	Yes	No	No	No	No	No	Weekly
8	Task 8	No	Yes	Yes	No	Yes	No	No	Weekly
9	Task 9	No	No	Yes	No	No	No	No	Fortnightly Even
10	Task 10	No	Yes	Yes	Yes	No	No	Yes	Weekly
11	Task 11	No	No	Yes	No	No	No	No	Weekly
12	Task 12	No	No	Yes	No	No	No	No	Weekly

Query

Weekly Task	Day	Weekly/Fortnightly
Task 1	Monday	Weekly
Task 2	Monday	Weekly
Task 3	Monday	Weekly
Task 4	Monday	Weekly
Task 5	Monday	Weekly
Task 6	Monday	Weekly
Task 1	Tuesday	Weekly
Task 3	Tuesday	Weekly
Task 4	Tuesday	Weekly
Task 6	Tuesday	Weekly
Task 7	Tuesday	Weekly
Task 8	Tuesday	Weekly
Task 10	Tuesday	Weekly
Task 1	Wednesday	Weekly
Task 2	Wednesday	Weekly
Task 3	Wednesday	Weekly
Task 4	Wednesday	Weekly
Task 6	Wednesday	Weekly
Task 8	Wednesday	Weekly
Task 9	Wednesday	Fortnightly Even
Task 10	Wednesday	Weekly
Task 11	Wednesday	Weekly
Task 12	Wednesday	Weekly

[bewsh1987]

Hi All

I figured this out was using union all wrong

SELECT [Daily task].[Weekly Task], IIf([Daily task].Monday=True,"Monday","") AS Day, [Daily task].[Weekly/Fortnightly] FROM [Daily task]
WHERE (((IIf([Daily task].Monday=True,"Monday",Null)) Is Not Null))
UNION ALL
SELECT [Daily task].[Weekly Task], IIf([Daily task].Tuesday=True,"Tuesday","") AS Day, [Daily task].[Weekly/Fortnightly] FROM [Daily task]
WHERE (((IIf([Daily task].Tuesday=True,"Tuesday",Null)) Is Not Null))
UNION ALL
SELECT [Daily task].[Weekly Task], IIf([Daily task].Wednesday=True,"Wednesday","") AS Day, [Daily task].[Weekly/Fortnightly] FROM [Daily task]
WHERE (((IIf([Daily task].Wednesday=True,"Wednesday",Null)) Is Not Null))
UNION ALL
SELECT [Daily task].[Weekly Task], IIf([Daily task].Thursday=True,"Thursday","") AS Day , [Daily task].[Weekly/Fortnightly] FROM [Daily task]
WHERE (((IIf([Daily task].Thursday=True,"Thursday",Null)) Is Not Null))
UNION ALL
SELECT [Daily task].[Weekly Task], IIf([Daily task].Friday=True,"Friday","") AS Day, [Daily task].[Weekly/Fortnightly] FROM [Daily task]
WHERE (((IIf([Daily task].Friday=True,"Friday",Null)) Is Not Null))
UNION ALL
SELECT [Daily task].[Weekly Task], IIf([Daily task].Saturday=True,"Saturday","") AS Day, [Daily task].[Weekly/Fortnightly] FROM [Daily task]
WHERE (((IIf([Daily task].Saturday=True,"Saturday",Null)) Is Not Null))
UNION ALL
SELECT [Daily task].[Weekly Task], IIf([Daily task].Sunday=True,"Sunday","") AS Day, [Daily task].[Weekly/Fortnightly] FROM [Daily task]
WHERE (((IIf([Daily task].Sunday=True,"Sunday",Null)) Is Not Null))

[John_G]

Your table (data) is not properly normalized, and you are experiencing one of the many problems that can arise with un-normalized data.

Your main data table should look like your query, and it makes the Yes/No values redundant. If there is an entry in the new table for a given task # - Day combination, the Yes is implied.

[bewsh1987]

Your table (data) is not properly normalized, and you are experiencing one of the many problems that can arise with un-normalized data.

Your main data table should look like your query, and it makes the Yes/No values redundant. If there is an entry in the new table for a given task # - Day combination, the Yes is implied.

Apologies I didn't explain this earlier I have a Yes = true and no = false (data type = Yes/No)