469,072 Members | 1,802 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,072 developers. It's quick & easy.

SELECT last record having same column data

I have two tables:


number explanation
10 something here
11 something here
12 something here
13 something here
14 something here
20 something here
24 something here
45 something here
122 something here

table 2:

number results
10 something here
10 something here *
11 something here
11 something here *
12 something here *
14 something here *
24 something here
24 something here *

I want to have a SELECT query to return all "number"s and all "explanation"s from table1 and the last "result" of each "number" in table2 (the ones I put * next to are the ones I want) (the *s are not actually in the table, I have put them here to show which records I want)

note that some "numbers" appear only once in table 2 and some others apprear twice.

this is what I want:

number explanation results
10 something here something here
11 something here something here
12 something here something here
13 something here
14 something here something here
20 something here
24 something here something here
45 something here
122 something here
Feb 28 '09 #1
8 4890
2,653 Expert 2GB
  • Leftjoin table1 with table2.
  • Grouping query on that join to return "last" record per group.
Feb 28 '09 #2
Dear FishVal,

Thanx for your reply. I am not an expert developer. Please provide more details. I honestly did not undrestand.

I know how to do the left-join part, but nothing about the grouping...

And one important thing I forgot to say is that there is also a unique numbering field in table1 (autonumber)

Perhaps this field can be helpful

Thanx a lot
Feb 28 '09 #3
2,653 Expert 2GB
You can find more details in Access help concerning "GROUP BY Clause".
Feb 28 '09 #4
32,154 Expert Mod 16PB
Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT   Table1.Number,
  2.          Table1.Explanation,
  3.          Last(Table2.Results) AS LastResults
  5. FROM     Table1 LEFT JOIN Table2
  6.   ON     Table1.Number=Table2.Number
  8. GROUP BY Table1.Number,
  9.          Table1.Explanation
  11. ORDER BY Table1.Number
When searching Access Help for SQL related issues, navigate to a top-level entry including "Microsoft Jet SQL" from the Table of Contents.
Feb 28 '09 #5
Dear NeoPa,

Thank you so so so so so so much for your reply. I am not yet getting the results I want, because it is the Last of each "number" in Table2 that I want to see the "result" for. The "result" field sometimes is null, so if I try the "Last" thing on the result, I will get the previous record, in which the "result" is not null.

I have made a simple database with only the two tables I am working on. The database is attached. Please be kind enough to take a look at the tables, and see if you can help me with this query.

In Table2 of the attached database, the records with uniquenumbers 302, 304, 306, 307, 309, 311, 312, 313 and 314 are the ones I want.

Thank you very very very very much.
Attached Files
File Type: zip db1.zip (8.1 KB, 114 views)
Mar 1 '09 #6
32,154 Expert Mod 16PB
It seems that all aggregate functions (Count, Last, Sum, etc) ignore Null entries. This is usually good, but in your case it causes a problem. We will either need to use theAutoNumber field to resolve this or create an extra TimeStamp field. I will have another look later.

PS. I'm really pleased you created that database of just what was needed. That's exactly how it SHOULD be done :)
Mar 1 '09 #7
2,653 Expert 2GB
Something like the following:

Query: qryT1LJT2
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.*, Table2.*
  2. FROM Table1 LEFT JOIN Table2 ON Table1.Number = Table2.Number;
Query: qryLastRecordsUIDs
Expand|Select|Wrap|Line Numbers
  1. SELECT qryT1LJT2.Table1.Number, Max(qryT1LJT2.uniquenumber) AS LastRecordUID
  2. FROM qryT1LJT2
  3. GROUP BY qryT1LJT2.Table1.Number;
Query: qryT1WithT2UID
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.*, qryLastRecordsUIDs.LastRecordUID
  2. FROM qryLastRecordsUIDs INNER JOIN Table1 ON qryLastRecordsUIDs.Number = Table1.Number;
Query: qryFinal
Expand|Select|Wrap|Line Numbers
  1. SELECT qryT1WithT2UID.*, Table2.Results1, Table2.Results2
  2. FROM qryT1WithT2UID LEFT JOIN Table2 ON qryT1WithT2UID.LastRecordUID = Table2.uniquenumber;
Mar 1 '09 #8
Dear FishVal and Neopa,

Thank you very much for your kind help.

I figured it out this way:

first, making a query which selects the LAST of "Numbers" in Table2

Then, making another query, which LEFT JOINs all the Numbers in Table 1 with the ones already selected by the first query.

Thank you so much.
Mar 2 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Dan Matthews | last post: by
4 posts views Thread by adam | last post: by
2 posts views Thread by Chris Plowman | last post: by
22 posts views Thread by MP | last post: by
5 posts views Thread by Matthew Wells | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.