Help with crosstab query

D

Den

Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
 
J

John Spencer

Don't use LAST to get the latest date installed. Use MAX.

Last returns the value of the last record accessed in each group of records.
That will not necessarily be the latest date or even the last record added to
the database. You should consider First and Last as returning an arbitrary,
more-or-less random value from the group of records in each group.

IF you post the SQL of your existing query, then we can look at it and get an
idea of exactly what you are attempting to do. Hint: View: SQL from the menu
will show the the actual SQL text instead of the graphic representation of the
SQL.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
K

KARL DEWEY

Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;
 
D

Den

Karl

The first part works but I don't know how to execute the second part in
order that I can get the value from Serial Number field

KARL DEWEY said:
Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


Den said:
Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
Click to expand...
Click to expand...
 
D

Den

Sorry Karl But I explained wrong, the value that I need to get is the serial
number (in Serial No field) that correspond to the last date (Max date)
documented in each position.

I will apprciate any help from you, thanks..

KARL DEWEY said:
Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


Den said:
Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
Click to expand...
Click to expand...
 
K

KARL DEWEY

I can not provide a suggestion if you do not say what the results is doing
incorrect. So what is it doing wrong?
--
Build a little, test a little.


Den said:
Sorry Karl But I explained wrong, the value that I need to get is the serial
number (in Serial No field) that correspond to the last date (Max date)
documented in each position.

I will apprciate any help from you, thanks..

KARL DEWEY said:
Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


Den said:
Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
Click to expand...
Click to expand...
Click to expand...
 
D

Den

Karl

The cross tab query actually brings me the most recent date and not the
serial number. I can’t find a way in the cross tab query that brings me the
serial number based in the most recent date.

I hope that in this way I can be more clear that I’m trying to do. My
intention is a Crosstab query that brings me the value (Serial No) stored in
the Serial No field using the most recent date and grouped by Instrument
number stored in Diss Unit field. The data is stored in the table Paddle
that is joined to other table.


Table Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed – Date


PosID Diss Unit Position Serial No Date installed

3 62 1 573 05/20/09
4 62 2 575 05/22/09
5 62 3 627 05/22/09
6 62 2 644 06/10/09
7 62 2 688 06/20/09
8 62 3 609 06/25/09
9 61 1 610 02/10/09
10 61 2 620 02/12/09
11 61 3 630 02/15/09
12 61 1 645 01/10/10
13 61 3 648 01/12/10
14 61 3 650 01/15/10
15 65 1 350 12/20/09
16 65 2 355 12/15/09
17 65 3 360 12/18/09
18 65 3 375 01/10/10
19 65 1 380 01/10/10
20 65 1 390 01/15/10



Diss Unit Position 1 Position 2 Position 3

62 573 688 609
61 645 620 650
65 390 355 375

Thanks again for your help...

KARL DEWEY said:
I can not provide a suggestion if you do not say what the results is doing
incorrect. So what is it doing wrong?
--
Build a little, test a little.


Den said:
Sorry Karl But I explained wrong, the value that I need to get is the serial
number (in Serial No field) that correspond to the last date (Max date)
documented in each position.

I will apprciate any help from you, thanks..

KARL DEWEY said:
Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


:

Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

The cross tab query actually brings me the most recent date and not the
serial number.
The crosstab I posted would not return a date unless you have a date in the
serial no. field.
Post back your crosstab SQL.

--
Build a little, test a little.


Den said:
Karl

The cross tab query actually brings me the most recent date and not the
serial number. I can’t find a way in the cross tab query that brings me the
serial number based in the most recent date.

I hope that in this way I can be more clear that I’m trying to do. My
intention is a Crosstab query that brings me the value (Serial No) stored in
the Serial No field using the most recent date and grouped by Instrument
number stored in Diss Unit field. The data is stored in the table Paddle
that is joined to other table.


Table Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed – Date


PosID Diss Unit Position Serial No Date installed

3 62 1 573 05/20/09
4 62 2 575 05/22/09
5 62 3 627 05/22/09
6 62 2 644 06/10/09
7 62 2 688 06/20/09
8 62 3 609 06/25/09
9 61 1 610 02/10/09
10 61 2 620 02/12/09
11 61 3 630 02/15/09
12 61 1 645 01/10/10
13 61 3 648 01/12/10
14 61 3 650 01/15/10
15 65 1 350 12/20/09
16 65 2 355 12/15/09
17 65 3 360 12/18/09
18 65 3 375 01/10/10
19 65 1 380 01/10/10
20 65 1 390 01/15/10



Diss Unit Position 1 Position 2 Position 3

62 573 688 609
61 645 620 650
65 390 355 375

Thanks again for your help...

KARL DEWEY said:
I can not provide a suggestion if you do not say what the results is doing
incorrect. So what is it doing wrong?
--
Build a little, test a little.


Den said:
Sorry Karl But I explained wrong, the value that I need to get is the serial
number (in Serial No field) that correspond to the last date (Max date)
documented in each position.

I will apprciate any help from you, thanks..

:

Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


:

Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I built the table and tested the queries and now it works --
qryLastPaddleChange
SELECT Paddle.[Diss Unit], Paddle.Position, Max(Paddle.[Date Installed]) AS
[MaxOfDate Installed]
FROM Paddle
GROUP BY Paddle.[Diss Unit], Paddle.Position;

TRANSFORM Last(Paddle.[Serial No]) AS [LastOfSerial No]
SELECT Paddle.[Diss Unit]
FROM Paddle INNER JOIN qryLastPaddleChange ON Paddle.[Diss Unit] =
qryLastPaddleChange.[Diss Unit] AND Paddle.Position =
qryLastPaddleChange.Position AND Paddle.[Date Installed] =
qryLastPaddleChange.[MaxOfDate Installed]
GROUP BY Paddle.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


Den said:
Karl

The cross tab query actually brings me the most recent date and not the
serial number. I can’t find a way in the cross tab query that brings me the
serial number based in the most recent date.

I hope that in this way I can be more clear that I’m trying to do. My
intention is a Crosstab query that brings me the value (Serial No) stored in
the Serial No field using the most recent date and grouped by Instrument
number stored in Diss Unit field. The data is stored in the table Paddle
that is joined to other table.


Table Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed – Date


PosID Diss Unit Position Serial No Date installed

3 62 1 573 05/20/09
4 62 2 575 05/22/09
5 62 3 627 05/22/09
6 62 2 644 06/10/09
7 62 2 688 06/20/09
8 62 3 609 06/25/09
9 61 1 610 02/10/09
10 61 2 620 02/12/09
11 61 3 630 02/15/09
12 61 1 645 01/10/10
13 61 3 648 01/12/10
14 61 3 650 01/15/10
15 65 1 350 12/20/09
16 65 2 355 12/15/09
17 65 3 360 12/18/09
18 65 3 375 01/10/10
19 65 1 380 01/10/10
20 65 1 390 01/15/10



Diss Unit Position 1 Position 2 Position 3

62 573 688 609
61 645 620 650
65 390 355 375

Thanks again for your help...

KARL DEWEY said:
I can not provide a suggestion if you do not say what the results is doing
incorrect. So what is it doing wrong?
--
Build a little, test a little.


Den said:
Sorry Karl But I explained wrong, the value that I need to get is the serial
number (in Serial No field) that correspond to the last date (Max date)
documented in each position.

I will apprciate any help from you, thanks..

:

Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


:

Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
Click to expand...
Click to expand...
Click to expand...
 
D

Den

Karl

Here is

SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Thanks for your patience

KARL DEWEY said:
serial number.
The crosstab I posted would not return a date unless you have a date in the
serial no. field.
Post back your crosstab SQL.

--
Build a little, test a little.


Den said:
Karl

The cross tab query actually brings me the most recent date and not the
serial number. I can’t find a way in the cross tab query that brings me the
serial number based in the most recent date.

I hope that in this way I can be more clear that I’m trying to do. My
intention is a Crosstab query that brings me the value (Serial No) stored in
the Serial No field using the most recent date and grouped by Instrument
number stored in Diss Unit field. The data is stored in the table Paddle
that is joined to other table.


Table Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed – Date


PosID Diss Unit Position Serial No Date installed

3 62 1 573 05/20/09
4 62 2 575 05/22/09
5 62 3 627 05/22/09
6 62 2 644 06/10/09
7 62 2 688 06/20/09
8 62 3 609 06/25/09
9 61 1 610 02/10/09
10 61 2 620 02/12/09
11 61 3 630 02/15/09
12 61 1 645 01/10/10
13 61 3 648 01/12/10
14 61 3 650 01/15/10
15 65 1 350 12/20/09
16 65 2 355 12/15/09
17 65 3 360 12/18/09
18 65 3 375 01/10/10
19 65 1 380 01/10/10
20 65 1 390 01/15/10



Diss Unit Position 1 Position 2 Position 3

62 573 688 609
61 645 620 650
65 390 355 375

Thanks again for your help...

KARL DEWEY said:
I can not provide a suggestion if you do not say what the results is doing
incorrect. So what is it doing wrong?
--
Build a little, test a little.


:

Sorry Karl But I explained wrong, the value that I need to get is the serial
number (in Serial No field) that correspond to the last date (Max date)
documented in each position.

I will apprciate any help from you, thanks..

:

Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


:

Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
Click to expand...
Click to expand...
Click to expand...
 
D

Den

Karl

Thank you so much the query works great. You help me a lot so again, Thank
you

KARL DEWEY said:
I built the table and tested the queries and now it works --
qryLastPaddleChange
SELECT Paddle.[Diss Unit], Paddle.Position, Max(Paddle.[Date Installed]) AS
[MaxOfDate Installed]
FROM Paddle
GROUP BY Paddle.[Diss Unit], Paddle.Position;

TRANSFORM Last(Paddle.[Serial No]) AS [LastOfSerial No]
SELECT Paddle.[Diss Unit]
FROM Paddle INNER JOIN qryLastPaddleChange ON Paddle.[Diss Unit] =
qryLastPaddleChange.[Diss Unit] AND Paddle.Position =
qryLastPaddleChange.Position AND Paddle.[Date Installed] =
qryLastPaddleChange.[MaxOfDate Installed]
GROUP BY Paddle.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


Den said:
Karl

The cross tab query actually brings me the most recent date and not the
serial number. I can’t find a way in the cross tab query that brings me the
serial number based in the most recent date.

I hope that in this way I can be more clear that I’m trying to do. My
intention is a Crosstab query that brings me the value (Serial No) stored in
the Serial No field using the most recent date and grouped by Instrument
number stored in Diss Unit field. The data is stored in the table Paddle
that is joined to other table.


Table Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed – Date


PosID Diss Unit Position Serial No Date installed

3 62 1 573 05/20/09
4 62 2 575 05/22/09
5 62 3 627 05/22/09
6 62 2 644 06/10/09
7 62 2 688 06/20/09
8 62 3 609 06/25/09
9 61 1 610 02/10/09
10 61 2 620 02/12/09
11 61 3 630 02/15/09
12 61 1 645 01/10/10
13 61 3 648 01/12/10
14 61 3 650 01/15/10
15 65 1 350 12/20/09
16 65 2 355 12/15/09
17 65 3 360 12/18/09
18 65 3 375 01/10/10
19 65 1 380 01/10/10
20 65 1 390 01/15/10



Diss Unit Position 1 Position 2 Position 3

62 573 688 609
61 645 620 650
65 390 355 375

Thanks again for your help...

KARL DEWEY said:
I can not provide a suggestion if you do not say what the results is doing
incorrect. So what is it doing wrong?
--
Build a little, test a little.


:

Sorry Karl But I explained wrong, the value that I need to get is the serial
number (in Serial No field) that correspond to the last date (Max date)
documented in each position.

I will apprciate any help from you, thanks..

:

Try this --
qryLastPaddleChange
SELECT Paddles.[Diss Unit], Paddles.Position, Max(Paddles.[Date Installed])
FROM Paddles
GROUP BY Paddles.[Diss Unit], Paddles.Position;

Then ---
TRANSFORM Last(qryLastPaddleChange.[Serial No]) AS [LastOfSerial No]
SELECT qryLastPaddleChange.[Diss Unit]
FROM qryLastPaddleChange
GROUP BY qryLastPaddleChange.[Diss Unit]
PIVOT qryLastPaddleChange.Position;

--
Build a little, test a little.


:

Sorry but I’m new using Access

I have a crosstab query (from the table name Paddle) with Diss Unit as Row
Heading and Position as Column Heading. The data is grouped by Diss Unit.
The cross tab query works fine when I use Last in Total Row and Value in
Crosstab row under Serial No. The crosstab query work well if the data is
entered in date sequence but I need sure that the value that I get is from
the last date in the case that the data is not entered in sequence. Each
instrument will have six positions with paddles assigned to each position so
I need to document the serial number of the paddle and the date when it was
installed or changed. The paddles in each position will be changed
frequently but each one at different dates so the purpose is to have a report
of current paddles that are in use in the instrument. How can I get the
value from the last date?

Table Name Paddle

PosID - Autonumber
Diss Unit – text
Position – Text
Serial No. – text
Date Installed - Date


SQL

TRANSFORM Last(Paddles.[Serial No]) AS [LastOfSerial No]
SELECT Paddles.[Diss Unit]
FROM Paddles
GROUP BY Paddles.[Diss Unit]
PIVOT Paddles.Position;

I will appreciate any help from you
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

Append Crosstab Query to Existing Table 0
crosstab query? 12
VBA - exporting serial letters to single documents 0
Creating a Chart on form from selected data in query. 0
Transpose - Help with crosstab query 7
Crosstab query question 2
Group by Range in Crosstab Query 5
Help on crosstab query 2

Top