How to create a weekly query with totals from 2 date columns? 1

[Aximboy]

I have a table with 3 columns, ID, LogIn and LogOut.
How do I create a querry with 3 columns grouped weekly with Login totals and Logout totals for that week.
Example:
DateTable
ID LogIn LogOut
1 01/01/2014 01/01/2014
2 01/10/2014 01/27/2014
3 01/10/2014 01/30/2014
4 01/13/2014 01/23/2014
5 01/13/2014 01/30/2014
6 01/16/2014 01/23/2014
7 01/21/2014 01/28/2014
8 01/23/2014 01/30/2014
9 01/23/2014 02/03/2014
10 01/23/2014 01/29/2014

Query results should be like:
Week#1 1 1
Week#2 2 0
Week#3 3 0
Week#4 7 1
…and so on…

 

[dhookom]

I would first create a normalizing union query of your data with SQL of:

=== quniAximboy ======

SELECT "IN" AS InOut, Format([LogIn],"yyyy ww") AS LogWeek
FROM tblAximboy
UNION ALL
SELECT "Out", Format([LogOut],"yyyy ww")
FROM tblAximboy;

Then create a crosstab query based on the union query:

TRANSFORM Count(quniAximboy.LogWeek) AS CountOfLogWeek
SELECT quniAximboy.LogWeek
FROM quniAximboy
GROUP BY quniAximboy.LogWeek
PIVOT quniAximboy.InOut;

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

dhookom,

Thank you so much! You're the man!
It took me a couple of days to comprehend your query.