It works fine(ish) in Northwind. Unfortunately I cannot apply it
successfully
to my query. The main problem I have is that I don't understand the
criteria
of the DCount function ("CustomerID = """ & [CustomerID] & """ AND
OrderDate<=#" & [OrderDate] & "#").
:
Try this in Northwind.mdb
SELECT Orders.CustomerID, Orders.OrderDate,
DCount("OrderDate", "Orders", "CustomerID = """ & [CustomerID] & """
AND
OrderDate<=#" & [OrderDate] & "#") AS OrderNumber
FROM Orders
ORDER BY Orders.CustomerID, Orders.OrderDate;
--
Duane Hookom
MS Access MVP
Duane,
Thanks for the guidance. I have re-created the query;
SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount("[ACTDATE]","Query1","ACTDATE<=#" & [ACTDATE] & "#
AND [IDENT] =""" & [Trainee]![IDENT] & """") AS DateNum
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT = Trainee.IDENT
ORDER BY Trainee.IDENT;
a sample of the output is:
IDENT ACTDATE DateNum
00001 27/04/2005 1
00001 29/06/2005 2
00003 23/06/2005 2*
00003 09/06/2005 2
When I crosstab this;
TRANSFORM First(Query1.ACTDATE) AS FirstOfACTDATE
SELECT Query1.IDENT
FROM Query1
GROUP BY Query1.IDENT
PIVOT Query1.DateNum;
I get the following output:
IDENT <> 0 1 2 3 4...
00001 27/04/2005 29/06/2005
00003 ** 09/06/2005
I am looking to have all(*) the dates in order, without any
blanks(**)
in
the final output.
Any further help is much appreciated.
Dave
:
You really didn't follow my example very well. Domain aggregate
functions
expect all arguments to be in quotes. Try something like:
DCount("[ACTDATE]","Query1","ACTDATE<=#" & [ACTDATE] & "#
AND [IDENT] =""" & [Trainee]![IDENT] & """") AS DateNum
--
Duane Hookom
MS Access MVP
Duane,
I have switched to using the Candidate ID (IDENT) rather than the
NAME
field:
SELECT Trainee.IDENT, pcAssUnt.ACTDATE,
DCount([pcAssUnt]![ACTDATE],"Query1","ACTDATE<=#" &
[pcAssUnt]![ACTDATE] &
"#
AND [Trainee]![IDENT] =""" & [Trainee]![IDENT] & """") AS
DateNum
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT =
Trainee.IDENT
ORDER BY Trainee.IDENT;
However the query output...
IDENT ACTDATE DateNum
00001 27/04/2005 6
00001 29/06/2005 7
00003 09/06/2005 11 *
00003 23/06/2005 11 *
00005 23/05/2005 12
00005 10/06/2005 22 *
00005 24/06/2005 22 *
...is still not as desired, can you advise where I am going
wrong.
Thanks
Dave
:
If you need separate columns, take your query and add a column
DateNum: DCount("ACDate","YourQueryName","ACDate<=#" & ACDATE
&
"#
AND
[Name]=""" & [Name] & """")
This will "number" the dates for each name.
You can then create a crosstab based on a query with the above
column
and
use "Date" & DateNum as the column heading, [Name] as the Row
Heading,
and
First of ACDate as the Value.
BTW: Consider changing Name to something other than Name since
Name
is
a
property of every object in Access.
--
Duane Hookom
MS Access MVP
--
Duane,
The query is currently as follows;
SELECT Trainee.NAME, pcAssUnt.ACTDATE
FROM pcAssUnt INNER JOIN Trainee ON pcAssUnt.IDENT =
Trainee.IDENT;
(IDENT is the candidate ID and ACTDATE is the actual date of
the
candidate
'lesson')
Query output is:
NAME ACDATE
Gemma Naughton 14/06/2005
Lucy Nutcher 17/06/2005
Lyndsey Owens 20/06/2005
Lyndsey Owens 01/07/2005
Matthew Tebay 03/06/2005
Matthew Tebay 10/06/2005
What I would like to see is:
NAME ACTDATE01 ACTDATE02...etc
Gemma Naughton 14/06/2005
Lucy Nutcher 17/06/2005
Lyndsey Owens 20/06/2005 01/07/2005
Matthew Tebay 03/06/2005 10/06/2005
the no. of lessons may go up to 9.
Any help is much appreciated
Dave
:
You would do this in a query. If you provide the same kind of
sample
data
and desired output, we might be of more assistance.
--
Duane Hookom
MS Access MVP
I have a problem identical to that of David Fawn, however I
don't
understand
the answer [from Duane]. Can anyone explain in more detail
(how/where
to
use)
the anwser provided.
Many thanks
:
Hi David,
Here be a previous reponse by Duane
to a similar question:
****quote*******
TRANSFORM Min(tblCowdog.Column2) AS MinOfColumn2
SELECT tblCowdog.Column1
FROM tblCowdog
GROUP BY tblCowdog.Column1
PIVOT "Column " & DCount("Column1","tblCowDog","Column2
<='"
&
[Column2]
&
"' AND Column1 = '" & [Column1] & "'")+1;
--
Duane Hookom
MS Access MVP
in
message
Hi,
I need a query that will convert rows to columns where
the first values are equal. The data I have is like:
Column 1 Column2
A Value 1
A Value 2
B Value 3
B Value 4
B Value 5
I want to convert that to:
Column 1 Column 2 Column 3 Column 4
A Value 1 Value 2
B Value 3 Value 4 Value 5
There will never be any letter for which there are more
than 3 values. Please help if you can.
**********unquote******
"David Fawn wrote:
I have this problem:
I have an Access database which looks like this:
<PRE>
Value 1 Value 2
Text1 blabla
Text1 blabla2
Text2 xxxxxx
Text2 yyyyyy
Text2 ffffff
Text3 aaaaaa
Text4 kkkkkk
</PRE>
There are "Values 1" which repeat one or more time, but
each
of
these
repeating values has different "Value 2". What I need is
to
do
this:
It should look like this:
<PRE>
Value 1 Value 2 Value 3 Value 4
Text1 blabla blabla2
Text2 xxxxxx yyyyyy ffffff
Text3 aaaaaa
Text4 kkkkkk
</PRE>
I want to add "Value 2" of the rows with the same "Value
1"
to
the
first
row of this value and to delete the others...
