SELECTing records

[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

 

[Brian Camire]

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 and B.

 

[Phil Hunt]

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

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 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

 

[Brian Camire]

The second query in my previous post is supported in Access.

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

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 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

 

[Phil Hunt]

You are right. I was thinking about adding join criteria using constant.

The second query in my previous post is supported in Access.

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

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

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

 

[Guest]

Thanks Brian, I'll give it a try...

-Djoezz