Numbering Date

D

Dave

I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005 24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
 
D

Duane Hookom

Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE = 'Q' AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE" &
Format(Number,"00") as the column heading, IDENT as the Row Heading, and
First of ACTDATE as the value.
 
D

Duane Hookom

I'm not sure a subquery will work when you want the results of the subquery
to be used as column headings in a crosstab. I could be wrong (wouldn't be
the first or last time).
 
D

Dave

Duane,
I have tried the SQL but i am getting the following
IDENT ACTDATE Number
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 2
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 4
00005 24/06/2005 4
00006 22/06/2005 1
Any ideas?
thanks
Dave

Duane Hookom said:
Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE = 'Q' AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE" &
Format(Number,"00") as the column heading, IDENT as the Row Heading, and
First of ACTDATE as the value.
--
Duane Hookom
MS Access MVP


Dave said:
I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005 24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
Click to expand...
Click to expand...
 
D

Duane Hookom

This worked for me with your sample data as a saved query. For instance save
your query without the DCount() as "qselTrainDate". Then create a query
like:

SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE,
qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*", "qselTrainDate", "IDENT=""" & [IDENT] & """
AND ACTDATE <=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Results of query qselTrainDateNums
IDENT ACTDATE QUALTYPE DateNum
0001 4/27/2005 Q ACTDATE01
0001 6/29/2005 Q ACTDATE02
0003 6/23/2005 Q ACTDATE01
0003 9/6/2005 Q ACTDATE02

Then create the query:
TRANSFORM First(qselTrainDateNums.ACTDATE) AS FirstOfACTDATE
SELECT qselTrainDateNums.IDENT
FROM qselTrainDateNums
GROUP BY qselTrainDateNums.IDENT
PIVOT qselTrainDateNums.DateNum;

--
Duane Hookom
MS Access MVP


Dave said:
Duane,
I have tried the SQL but i am getting the following
IDENT ACTDATE Number
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 2
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 4
00005 24/06/2005 4
00006 22/06/2005 1
Any ideas?
thanks
Dave

Duane Hookom said:
Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE = 'Q' AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE" &
Format(Number,"00") as the column heading, IDENT as the Row Heading, and
First of ACTDATE as the value.
--
Duane Hookom
MS Access MVP


Dave said:
I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005 24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE
criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
Click to expand...
Click to expand...
Click to expand...
 
D

Dave

Duane,
I got as far as the date numbering query, but ended up with the following;
IDENT ACTDATE QUALTYPE DateNum
00001 27/04/2005 Q ACTDATE01
00001 29/06/2005 Q ACTDATE02
00002 21/07/2005 Q ACTDATE01
00003 09/06/2005 Q ACTDATE03
00003 23/06/2005 Q ACTDATE02
00003 18/07/2005 Q ACTDATE03
00005 13/05/2005 Q ACTDATE01
00005 23/05/2005 Q ACTDATE02
00005 10/06/2005 Q ACTDATE04
00005 24/06/2005 Q ACTDATE04
00006 22/06/2005 Q ACTDATE01
00007 23/05/2005 Q ACTDATE01
any ideas where I am going wrong?

Duane Hookom said:
This worked for me with your sample data as a saved query. For instance save
your query without the DCount() as "qselTrainDate". Then create a query
like:

SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE,
qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*", "qselTrainDate", "IDENT=""" & [IDENT] & """
AND ACTDATE <=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Results of query qselTrainDateNums
IDENT ACTDATE QUALTYPE DateNum
0001 4/27/2005 Q ACTDATE01
0001 6/29/2005 Q ACTDATE02
0003 6/23/2005 Q ACTDATE01
0003 9/6/2005 Q ACTDATE02

Then create the query:
TRANSFORM First(qselTrainDateNums.ACTDATE) AS FirstOfACTDATE
SELECT qselTrainDateNums.IDENT
FROM qselTrainDateNums
GROUP BY qselTrainDateNums.IDENT
PIVOT qselTrainDateNums.DateNum;

--
Duane Hookom
MS Access MVP


Dave said:
Duane,
I have tried the SQL but i am getting the following
IDENT ACTDATE Number
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 2
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 4
00005 24/06/2005 4
00006 22/06/2005 1
Any ideas?
thanks
Dave

Duane Hookom said:
Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE = 'Q' AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE" &
Format(Number,"00") as the column heading, IDENT as the Row Heading, and
First of ACTDATE as the value.
--
Duane Hookom
MS Access MVP


I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005 24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE
criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Post the SQL views of your queries.

--
Duane Hookom
MS Access MVP


Dave said:
Duane,
I got as far as the date numbering query, but ended up with the following;
IDENT ACTDATE QUALTYPE DateNum
00001 27/04/2005 Q ACTDATE01
00001 29/06/2005 Q ACTDATE02
00002 21/07/2005 Q ACTDATE01
00003 09/06/2005 Q ACTDATE03
00003 23/06/2005 Q ACTDATE02
00003 18/07/2005 Q ACTDATE03
00005 13/05/2005 Q ACTDATE01
00005 23/05/2005 Q ACTDATE02
00005 10/06/2005 Q ACTDATE04
00005 24/06/2005 Q ACTDATE04
00006 22/06/2005 Q ACTDATE01
00007 23/05/2005 Q ACTDATE01
any ideas where I am going wrong?

Duane Hookom said:
This worked for me with your sample data as a saved query. For instance
save
your query without the DCount() as "qselTrainDate". Then create a query
like:

SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE,
qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*", "qselTrainDate", "IDENT=""" & [IDENT] &
"""
AND ACTDATE <=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Results of query qselTrainDateNums
IDENT ACTDATE QUALTYPE DateNum
0001 4/27/2005 Q ACTDATE01
0001 6/29/2005 Q ACTDATE02
0003 6/23/2005 Q ACTDATE01
0003 9/6/2005 Q ACTDATE02

Then create the query:
TRANSFORM First(qselTrainDateNums.ACTDATE) AS FirstOfACTDATE
SELECT qselTrainDateNums.IDENT
FROM qselTrainDateNums
GROUP BY qselTrainDateNums.IDENT
PIVOT qselTrainDateNums.DateNum;

--
Duane Hookom
MS Access MVP


Dave said:
Duane,
I have tried the SQL but i am getting the following
IDENT ACTDATE Number
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 2
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 4
00005 24/06/2005 4
00006 22/06/2005 1
Any ideas?
thanks
Dave

:

Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE = 'Q'
AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE" &
Format(Number,"00") as the column heading, IDENT as the Row Heading,
and
First of ACTDATE as the value.
--
Duane Hookom
MS Access MVP


I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005
24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE
criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
Click to expand...
Click to expand...
Click to expand...
 
D

Dave

(1)
SELECT Trainee.IDENT, pcAssUnt.ACTDATE, pcAssUnt.QUALTYPE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;
(2)
SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE, qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*","qselTrainDate","IDENT=""" & [IDENT] & """
AND ACTDATE<=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Duane Hookom said:
Post the SQL views of your queries.

--
Duane Hookom
MS Access MVP


Dave said:
Duane,
I got as far as the date numbering query, but ended up with the following;
IDENT ACTDATE QUALTYPE DateNum
00001 27/04/2005 Q ACTDATE01
00001 29/06/2005 Q ACTDATE02
00002 21/07/2005 Q ACTDATE01
00003 09/06/2005 Q ACTDATE03
00003 23/06/2005 Q ACTDATE02
00003 18/07/2005 Q ACTDATE03
00005 13/05/2005 Q ACTDATE01
00005 23/05/2005 Q ACTDATE02
00005 10/06/2005 Q ACTDATE04
00005 24/06/2005 Q ACTDATE04
00006 22/06/2005 Q ACTDATE01
00007 23/05/2005 Q ACTDATE01
any ideas where I am going wrong?

Duane Hookom said:
This worked for me with your sample data as a saved query. For instance
save
your query without the DCount() as "qselTrainDate". Then create a query
like:

SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE,
qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*", "qselTrainDate", "IDENT=""" & [IDENT] &
"""
AND ACTDATE <=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Results of query qselTrainDateNums
IDENT ACTDATE QUALTYPE DateNum
0001 4/27/2005 Q ACTDATE01
0001 6/29/2005 Q ACTDATE02
0003 6/23/2005 Q ACTDATE01
0003 9/6/2005 Q ACTDATE02

Then create the query:
TRANSFORM First(qselTrainDateNums.ACTDATE) AS FirstOfACTDATE
SELECT qselTrainDateNums.IDENT
FROM qselTrainDateNums
GROUP BY qselTrainDateNums.IDENT
PIVOT qselTrainDateNums.DateNum;

--
Duane Hookom
MS Access MVP


Duane,
I have tried the SQL but i am getting the following
IDENT ACTDATE Number
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 2
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 4
00005 24/06/2005 4
00006 22/06/2005 1
Any ideas?
thanks
Dave

:

Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE = 'Q'
AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE" &
Format(Number,"00") as the column heading, IDENT as the Row Heading,
and
First of ACTDATE as the value.
--
Duane Hookom
MS Access MVP


I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005
24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE
criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

This should work. If you can't get it working as expected, you can contact
me about sending a file to me to review. You must clear this first with an
email since I don't accept emails with attachments.
DuaneHookomATgmailDOTcom.

--
Duane Hookom
MS Access MVP


Dave said:
(1)
SELECT Trainee.IDENT, pcAssUnt.ACTDATE, pcAssUnt.QUALTYPE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;
(2)
SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE, qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*","qselTrainDate","IDENT=""" & [IDENT] & """
AND ACTDATE<=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Duane Hookom said:
Post the SQL views of your queries.

--
Duane Hookom
MS Access MVP


Dave said:
Duane,
I got as far as the date numbering query, but ended up with the
following;
IDENT ACTDATE QUALTYPE DateNum
00001 27/04/2005 Q ACTDATE01
00001 29/06/2005 Q ACTDATE02
00002 21/07/2005 Q ACTDATE01
00003 09/06/2005 Q ACTDATE03
00003 23/06/2005 Q ACTDATE02
00003 18/07/2005 Q ACTDATE03
00005 13/05/2005 Q ACTDATE01
00005 23/05/2005 Q ACTDATE02
00005 10/06/2005 Q ACTDATE04
00005 24/06/2005 Q ACTDATE04
00006 22/06/2005 Q ACTDATE01
00007 23/05/2005 Q ACTDATE01
any ideas where I am going wrong?

:

This worked for me with your sample data as a saved query. For
instance
save
your query without the DCount() as "qselTrainDate". Then create a
query
like:

SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE,
qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*", "qselTrainDate", "IDENT=""" & [IDENT] &
"""
AND ACTDATE <=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Results of query qselTrainDateNums
IDENT ACTDATE QUALTYPE DateNum
0001 4/27/2005 Q ACTDATE01
0001 6/29/2005 Q ACTDATE02
0003 6/23/2005 Q ACTDATE01
0003 9/6/2005 Q ACTDATE02

Then create the query:
TRANSFORM First(qselTrainDateNums.ACTDATE) AS FirstOfACTDATE
SELECT qselTrainDateNums.IDENT
FROM qselTrainDateNums
GROUP BY qselTrainDateNums.IDENT
PIVOT qselTrainDateNums.DateNum;

--
Duane Hookom
MS Access MVP


Duane,
I have tried the SQL but i am getting the following
IDENT ACTDATE Number
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 2
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 4
00005 24/06/2005 4
00006 22/06/2005 1
Any ideas?
thanks
Dave

:

Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE =
'Q'
AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE" &
Format(Number,"00") as the column heading, IDENT as the Row
Heading,
and
First of ACTDATE as the value.
--
Duane Hookom
MS Access MVP


I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT =
Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005
24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE
criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

There were a number of issues with the attempts.
1) ACTDATEs were not unique for IDENT values with a "Q" value in QUALTYPE.
2) There were records with no value for ACTDATE

The first query (qselTrainDate) was set up with a sql of
SELECT Trainee.IDENT, pcAssUnt.ACTDATE, pcAssUnt.QUALTYPE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
GROUP BY Trainee.IDENT, pcAssUnt.ACTDATE, pcAssUnt.QUALTYPE
HAVING (((pcAssUnt.ACTDATE) Is Not Null) AND ((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

The next query (qselTrainDateNums):
SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE, "ACTDATE" &
Format(DCount("*","qselTrainDate","IDENT=""" & [IDENT] & """
AND ACTDATE<=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

The crosstab SQL:
TRANSFORM First(qselTrainDateNums.ACTDATE) AS FirstOfACTDATE
SELECT qselTrainDateNums.IDENT
FROM qselTrainDateNums
GROUP BY qselTrainDateNums.IDENT
PIVOT qselTrainDateNums.DateNum;



--
Duane Hookom
MS Access MVP


Duane Hookom said:
This should work. If you can't get it working as expected, you can contact
me about sending a file to me to review. You must clear this first with an
email since I don't accept emails with attachments.
DuaneHookomATgmailDOTcom.

--
Duane Hookom
MS Access MVP


Dave said:
(1)
SELECT Trainee.IDENT, pcAssUnt.ACTDATE, pcAssUnt.QUALTYPE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;
(2)
SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE,
qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*","qselTrainDate","IDENT=""" & [IDENT] & """
AND ACTDATE<=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Duane Hookom said:
Post the SQL views of your queries.

--
Duane Hookom
MS Access MVP


Duane,
I got as far as the date numbering query, but ended up with the
following;
IDENT ACTDATE QUALTYPE DateNum
00001 27/04/2005 Q ACTDATE01
00001 29/06/2005 Q ACTDATE02
00002 21/07/2005 Q ACTDATE01
00003 09/06/2005 Q ACTDATE03
00003 23/06/2005 Q ACTDATE02
00003 18/07/2005 Q ACTDATE03
00005 13/05/2005 Q ACTDATE01
00005 23/05/2005 Q ACTDATE02
00005 10/06/2005 Q ACTDATE04
00005 24/06/2005 Q ACTDATE04
00006 22/06/2005 Q ACTDATE01
00007 23/05/2005 Q ACTDATE01
any ideas where I am going wrong?

:

This worked for me with your sample data as a saved query. For
instance
save
your query without the DCount() as "qselTrainDate". Then create a
query
like:

SELECT qselTrainDate.IDENT, qselTrainDate.ACTDATE,
qselTrainDate.QUALTYPE,
"ACTDATE" & Format(DCount("*", "qselTrainDate", "IDENT=""" & [IDENT]
&
"""
AND ACTDATE <=#" & [ACTDATE] & "#"),"00") AS DateNum
FROM qselTrainDate
ORDER BY qselTrainDate.IDENT, qselTrainDate.ACTDATE;

Results of query qselTrainDateNums
IDENT ACTDATE QUALTYPE DateNum
0001 4/27/2005 Q ACTDATE01
0001 6/29/2005 Q ACTDATE02
0003 6/23/2005 Q ACTDATE01
0003 9/6/2005 Q ACTDATE02

Then create the query:
TRANSFORM First(qselTrainDateNums.ACTDATE) AS FirstOfACTDATE
SELECT qselTrainDateNums.IDENT
FROM qselTrainDateNums
GROUP BY qselTrainDateNums.IDENT
PIVOT qselTrainDateNums.DateNum;

--
Duane Hookom
MS Access MVP


Duane,
I have tried the SQL but i am getting the following
IDENT ACTDATE Number
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 2
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 4
00005 24/06/2005 4
00006 22/06/2005 1
Any ideas?
thanks
Dave

:

Try this SQL to get your numbered dates:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("*","pcAssUnt","IDENT =""" & [IDENT] & """ AND QUALTYPE =
'Q'
AND
ACTDATE<=#" & [ACTDATE] & "#") as Number
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

Then create your crosstab based on the above query with "ACTDATE"
&
Format(Number,"00") as the column heading, IDENT as the Row
Heading,
and
First of ACTDATE as the value.
--
Duane Hookom
MS Access MVP


I have the following query:

SELECT Trainee.IDENT, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT =
Trainee.IDENT
WHERE (((pcAssUnt.QUALTYPE)="Q"))
ORDER BY Trainee.IDENT, pcAssUnt.ACTDATE;

and the output is:

IDENT ACTDATE
00001 27/04/2005
00001 29/06/2005
00003 09/06/2005
00003 23/06/2005
00005 13/05/2005
00005 23/05/2005
00005 10/06/2005
00005 24/06/2005
00006 22/06/2005
00007 23/05/2005

I would like to number the ACTDATEs by IDENT:

IDENT ACTDATE NUMBER
00001 27/04/2005 1
00001 29/06/2005 2
00003 09/06/2005 1
00003 23/06/2005 2
00005 13/05/2005 1
00005 23/05/2005 2
00005 10/06/2005 3
00005 24/06/2005 4
00006 22/06/2005 1
00007 23/05/2005 1

I would then like to crosstab the resultant query to display as:

IDENT ACTDATE01 ACTDATE02 ACTDATE03 ACTDATE 04 ACTDATE05
00001 27/04/2005 29/06/2005
00003 09/06/2005 23/06/2005
00005 13/05/2005 23/05/2005 10/06/2005 10/06/2005
24/06/2005
00006 22/06/2005
00007 23/05/2005

Can anyone suggest a way of achieving this 'numbering'.
The ACTDATE is not unique, as shown when removing the QUALTYPE
criteria:
IDENT ACTDATE QUALTYPE
00001 27/04/2005 Q
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 27/04/2005 U
00001 29/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 Q
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 09/06/2005 U
00003 23/06/2005 Q
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U
00003 23/06/2005 U...etc

any help is much appreciated
thanks
Dave
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query help 1
Advanced problem using auto-number and datediff to find readmissio 2
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Find then highlight in yellow 6
booking query 2 1
Reverse Index Match 2
DATES - Grouping sets of sequential dates 3
Dates - Grouping Start and End dates of sequential assignments. 0

Top