Want to make reports regarding office roster

[arei]

Hi,



I am trying to make weekly reports from a given roster. For example I have following information;

Name	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Employee A	Overnight	Day Off	Day Off	Evening	Evening	Morning	Overnight
Employee B	Day Off	Day Off	Morning	Morning	Morning	Evening	Morning
Employee C	Overnight	Overnight	Day Off	Day Off	Morning	Overnight	Overnight
Employee D	Overnight	Overnight	Overnight	Overnight	Overnight	Day Off	Day Off

I tried to generate following weekly reports for a month.

	2 Feb Monday
Name	Shift	Arrival Time	Signeture
Employee A	Overnight
Employee B	Day Off
Employee C	Overnight
Employee D	Overnight
	3 Feb Tuesday
Name	Shift	Arrival Time	Signeture
Employee A	Day Off
Employee B	Day Off
Employee C	Overnight
Employee D	Overnight

Would you please help me about this. please suggest me how to make it.

[June7]

Your roster table is not a normalized data structure. Rearranging into the vertical structure can be done with VBA code to create records in a table or with a UNION query.

SELECT [Name], [Monday] AS Shift FROM tablename
UNION SELECT [Name], [Tuesday] FROM tablename
UNION SELECT [Name], [Wednesday] FROM tablename
UNION SELECT [Name], [Thursday] FROM tablename
UNION SELECT [Name], [Friday] FROM tablename
UNION SELECT [Name], [Saturday] FROM tablename
UNION SELECT [Name], [Sunday] FROM tablename;

There is no designer or wizard for UNION query, must type into SQL View of query builder. Cannot edit data via UNION query.