Hi
It is really confusing me how to explain but, I am trying to build a attendance report for a week
I have create a report from query
SELECT qryTS1.ID, qryTS1.Employee, qryTS1.calndrid, qryTS1.ATTN
FROM qryTS1
ORDER BY qryTS1.ID, qryTS1.calndrid;

so it is giving the format
ID Employee Date AttN
1 Emp1 24/4/12 P
2 Emp2 24/4/12 A

is it possible to customise the report in the format

ID Employee 24/4/12
1 Emp1 P
2 Emp2 A


I am sorry for not able to explain more

Yes, it is possible

In the design view of the report,
Group On the Name of the Date Control-
Then place the Date Control textbox in the header of this Group.

Hope this helps.

Thank you! Cynthia for your quick response

it is ok if there no more dates,
I mean if the reports have more date value like,

ID Employee Date AttN
1 Emp1 24/4/12 P
2 Emp2 24/4/12 A
1 Emp1 25/4/12 P
2 Emp2 25/4/12 P

then I want

ID Employee 24/4/12 25/4/12
1 Emp1 P P
2 Emp2 A P

if it can be like that, my Attendence report would be nice to see & more Communicative (I hope)
but it is really making my head confus...!!

In order to do that you need to use a crosstab query, however making reports based on them can be problematic due to the variable number of columns, however in your example it could easily be done as long as you have a maximum number of dates you can display. Is that a possibility?

Thank you Jon for help


The query would be increasing as the day progress like…
ID Employee Date AttN
1 Emp1 22/4/12 P
2 Emp2 22/4/12 A
1 Emp1 23/4/12 P
2 Emp2 23/4/12 P
1 Emp1 24/4/12 P
2 Emp2 24/4/12 P
1 Emp1 25/4/12 P
2 Emp2 25/4/12 P
1 Emp1 26/4/12 P
2 Emp2 26/4/12 A
1 Emp1 27/4/12 P
2 Emp2 27/4/12 P
1 Emp1 28/4/12 P
2 Emp2 28/4/12 P
1 Emp1 29/4/12 P
2 Emp2 29/4/12 P
1 Emp1 30/4/12 A
2 Emp2 30/4/12 A


But I don’t want all dates as a column in report, instead only for a week -Last 7 days.(since i am looking for a weekly Attendance register report)
ID Employee 4/24/12 4/25/12 4/26/12 4/27/12 4/28/12 4/29/12 4/30/12
1 Emp1 P P P P P P A
2 Emp2 P P A P P P A

Then you would probably be better off with a standard select query for the report, and the query would have 7 columns for days that would be equal to the date you select minus 1, minus 2, ...minus 7. You should be able to use dlookup for your query fields, or a sub query.

Honestly
I am not getting form where to start

I create another query from main query which is
SELECT DISTINCT qry.ID, qry.Employee, DLookUp("[ATTN]","qry","date=#4/22/12#") AS Day1, DLookUp("[ATTN]","qry","date=#4/21/12#") AS Day2
FROM qry;
I set “unique Values “ to “Yes”
But still the result is not correct

You need to filter the lookup based on your ID as well:

SELECT DISTINCT qry.ID, qry.Employee, DLookUp("[ATTN]","qry","date=#4/22/12# AND ID=" & qry.ID) AS Day1, DLookUp("[ATTN]","qry","date=#4/21/12# AND ID=" & qry.ID) AS Day2
FROM qry;

If you get that working, you could report on the last 7 days by doing the following:

SELECT DISTINCT qry.ID, qry.Employee, DLookUp("[ATTN]","qry","date=#" & DateAdd("d",-7,[Your Selected Date]) & "# AND ID=" & qry.ID) AS Day1, DLookUp("[ATTN]","qry","date=#" & DateAdd("d",-6,[Your Selected Date]) & "# AND ID=" & qry.ID) AS Day2
FROM qry;

It Worked but little slow

Anyway Thank you very much !!! darnellk