Crosstab query Help

[elena]

Hi, All
I need help with query for the report;
table has following fields

Plate Location MarkDate MarkTime Shield
-------------------------------------------------------
12AB Main St 02/23/07 11:00 801
34CD West St 02/23/07 12:15 801
12AB Main St 02/23/07 14:00 801
12AB Main St 02/23/07 14:30 801
---------------------------------------------------------

I need to produce output for the report like this:
Date Location Plate MTime1 MTime2 MTime3
------------------------------------------------------------------
02/23/07 Main St 12AB 11:00 14:00 14:30
02/23/07 West St 34CD 12:15

I don't know how to define fields in output from the query...

TRANSFORM First(t.MarkTime) AS FirstOfTime
SELECT t.MarkDate, t.Location, t.Plate
FROM t
GROUP BY t.MarkDate, t.Location, t.Plate
PIVOT t.MarkTime IN ("MTime1", "MTime2", "MTime3")

 

[Michel Walsh]

A crosstab supply new field based on values stored in a field. Since you
don't have MTime1, MTime2 and MTime3 as values, already, a crosstab is not
really the solution to use. If it was not for MTime2, a possible solution
could have been:


SELECT theDate, Location, Plate, MIN(MarkTime) AS MTime1, MAX(MarkTime) AS
MTime3
FROM myTable
GROUP BY theDate, Location, Plate

(and there will be also the problem that MTime3 can be equal to MTime1,
rather than being null, as you have sketched the desired result, but I
assume this is not a major problem).

Sure, if you can use the AVERAGE rather than a third time, for MTime2, that
would be already almost done:

SELECT theDate, Location, Plate, MIN(MarkTime) AS MTime1, AVG(MarkTime) AS
MTIime2, MAX(MarkTime) AS MTime3
FROM myTable
GROUP BY theDate, Location, Plate


but if there is more than three MarkTIme, for a plate, for a given date,
what we do, as example, if there is a fourth record about Plate=12AB for
02/23/2007 ?


Vanderghast, Access MVP

 

[elena]

Michel,
Thank you for reply, i agree that it possible to have fourth record with the
same
plate and location and different marktime...
But any way how to achive the output result in that case?

Thank you,

 

[Duane Hookom]

Try this SQL

TRANSFORM First(t.MarkTime) AS FirstOfMarkTime
SELECT t.MarkDate, t.Location, t.Plate
FROM t
GROUP BY t.MarkDate, t.Location, t.Plate
PIVOT "MTime" & DCount("*","t","Location='" & [Location] & "' AND
MarkDate=#" & [MarkDate] & "# AND MarkTime <=#" & [MarkTIme] & "#");

 

[elena]

Duane,
Thank you so much, it works the way i needed, thank you again!

Try this SQL

TRANSFORM First(t.MarkTime) AS FirstOfMarkTime
SELECT t.MarkDate, t.Location, t.Plate
FROM t
GROUP BY t.MarkDate, t.Location, t.Plate
PIVOT "MTime" & DCount("*","t","Location='" & [Location] & "' AND
MarkDate=#" & [MarkDate] & "# AND MarkTime <=#" & [MarkTIme] & "#");

--
Duane Hookom
Microsoft Access MVP

Michel,
Thank you for reply, i agree that it possible to have fourth record with the
same
plate and location and different marktime...
But any way how to achive the output result in that case?

Thank you,