SELECTing records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

forum
Can anybody tell me how to find the last records found in a query
Lets say I have a unique Pk combination of fileds A,B and C. The query should pick only the last record (1 record only) in every distinct A and B
eg:
Say I have these 4 records
-ValueA,ValueB, ValueC
-ValueA,ValueB, ValueC
-ValueA,ValueB1,ValueC
-ValueA,ValueB1,ValueC
The result of the query should find 2 records
-ValueA,ValueB,ValueC2 an
-ValueA,ValueB1,ValueC

Anyone can help

Thanks
Djoezz
 
If you only want to find the last (maximum) value of C for each distinct
combination of A and B, you might try a query whose SQL looks like this:

SELECT
[Your Table].[A],
[Your Table].,
Max([Your Table].[C]) AS [Maximum C]
FROM
[Your Table]
GROUP BY
[Your Table].[A],
[Your Table].

If you want the query to also return the values of other fields in your
table from the record with the maximum value of C for each distinct
combination of A and B, you might create a second query that joins the first
query to your table. Assuming you saved the first query as "Query1", the
SQL for the second query might look something like this:

SELECT
[Your Table].*
FROM
[Your Table] INNER JOIN [Query1]
ON
[Your Table].[A] = [Query1].[A]
AND
[Your Table]. = [Query1].
AND
[Your Table].[C] = [Query1].[Maximum C]

You can find examples of this and other approaches at:

http://www.mvps.org/access/queries/qry0020.htm

Hope this helps.

Djoezz said:
forum,
Can anybody tell me how to find the last records found in a query?
Lets say I have a unique Pk combination of fileds A,B and C. The query
Click to expand...
should pick only the last record (1 record only) in every distinct A and B.
 
I think the 2nd query is not supported in Access. It works in SQL server or
DB2, but not Access.


Brian Camire said:
If you only want to find the last (maximum) value of C for each distinct
combination of A and B, you might try a query whose SQL looks like this:

SELECT
[Your Table].[A],
[Your Table].,
Max([Your Table].[C]) AS [Maximum C]
FROM
[Your Table]
GROUP BY
[Your Table].[A],
[Your Table].

If you want the query to also return the values of other fields in your
table from the record with the maximum value of C for each distinct
combination of A and B, you might create a second query that joins the first
query to your table. Assuming you saved the first query as "Query1", the
SQL for the second query might look something like this:

SELECT
[Your Table].*
FROM
[Your Table] INNER JOIN [Query1]
ON
[Your Table].[A] = [Query1].[A]
AND
[Your Table]. = [Query1].
AND
[Your Table].[C] = [Query1].[Maximum C]

You can find examples of this and other approaches at:

http://www.mvps.org/access/queries/qry0020.htm

Hope this helps.

Djoezz said:
forum,
Can anybody tell me how to find the last records found in a query?
Lets say I have a unique Pk combination of fileds A,B and C. The query
Click to expand...
should pick only the last record (1 record only) in every distinct A and B.
eg:
Say I have these 4 records:
-ValueA,ValueB, ValueC1
-ValueA,ValueB, ValueC2
-ValueA,ValueB1,ValueC3
-ValueA,ValueB1,ValueC4
The result of the query should find 2 records:
-ValueA,ValueB,ValueC2 and
-ValueA,ValueB1,ValueC4

Anyone can help?

Thanks,
Djoezz
Click to expand...
Click to expand...
 
The second query in my previous post is supported in Access.

Phil Hunt said:
I think the 2nd query is not supported in Access. It works in SQL server or
DB2, but not Access.


Brian Camire said:
If you only want to find the last (maximum) value of C for each distinct
combination of A and B, you might try a query whose SQL looks like this:

SELECT
[Your Table].[A],
[Your Table].,
Max([Your Table].[C]) AS [Maximum C]
FROM
[Your Table]
GROUP BY
[Your Table].[A],
[Your Table].

If you want the query to also return the values of other fields in your
table from the record with the maximum value of C for each distinct
combination of A and B, you might create a second query that joins the first
query to your table. Assuming you saved the first query as "Query1", the
SQL for the second query might look something like this:

SELECT
[Your Table].*
FROM
[Your Table] INNER JOIN [Query1]
ON
[Your Table].[A] = [Query1].[A]
AND
[Your Table]. = [Query1].
AND
[Your Table].[C] = [Query1].[Maximum C]

You can find examples of this and other approaches at:

http://www.mvps.org/access/queries/qry0020.htm

Hope this helps.

Djoezz said:
forum,
Can anybody tell me how to find the last records found in a query?
Lets say I have a unique Pk combination of fileds A,B and C. The query
Click to expand...
should pick only the last record (1 record only) in every distinct A and B.
eg:
Say I have these 4 records:
-ValueA,ValueB, ValueC1
-ValueA,ValueB, ValueC2
-ValueA,ValueB1,ValueC3
-ValueA,ValueB1,ValueC4
The result of the query should find 2 records:
-ValueA,ValueB,ValueC2 and
-ValueA,ValueB1,ValueC4

Anyone can help?

Thanks,
Djoezz
Click to expand...
Click to expand...
Click to expand...
 
You are right. I was thinking about adding join criteria using constant.


Brian Camire said:
The second query in my previous post is supported in Access.

Phil Hunt said:
I think the 2nd query is not supported in Access. It works in SQL server or
DB2, but not Access.


Brian Camire said:
If you only want to find the last (maximum) value of C for each distinct
combination of A and B, you might try a query whose SQL looks like this:

SELECT
[Your Table].[A],
[Your Table].,
Max([Your Table].[C]) AS [Maximum C]
FROM
[Your Table]
GROUP BY
[Your Table].[A],
[Your Table].

If you want the query to also return the values of other fields in your
table from the record with the maximum value of C for each distinct
combination of A and B, you might create a second query that joins the first
query to your table. Assuming you saved the first query as "Query1", the
SQL for the second query might look something like this:

SELECT
[Your Table].*
FROM
[Your Table] INNER JOIN [Query1]
ON
[Your Table].[A] = [Query1].[A]
AND
[Your Table]. = [Query1].
AND
[Your Table].[C] = [Query1].[Maximum C]

You can find examples of this and other approaches at:

http://www.mvps.org/access/queries/qry0020.htm

Hope this helps.

forum,
Can anybody tell me how to find the last records found in a query?
Lets say I have a unique Pk combination of fileds A,B and C. The query
should pick only the last record (1 record only) in every distinct A
Click to expand...
and
B.
eg:
Say I have these 4 records:
-ValueA,ValueB, ValueC1
-ValueA,ValueB, ValueC2
-ValueA,ValueB1,ValueC3
-ValueA,ValueB1,ValueC4
The result of the query should find 2 records:
-ValueA,ValueB,ValueC2 and
-ValueA,ValueB1,ValueC4

Anyone can help?

Thanks,
Djoezz
Click to expand...
Click to expand...
Click to expand...
 
Back
Top