SELECT last record having same column data

[Alireza355]

I have two tables:

table1:

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

[FishVal]

Hello.

• Leftjoin table1 with table2.
• Grouping query on that join to return "last" record per group.

[Alireza355]

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

[FishVal]

You can find more details in Access help concerning "GROUP BY Clause".

[NeoPa]

Try :

SELECT   Table1.Number,
         Table1.Explanation,
         Last(Table2.Results) AS LastResults
 
FROM     Table1 LEFT JOIN Table2
  ON     Table1.Number=Table2.Number
 
GROUP BY Table1.Number,
         Table1.Explanation
 
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.

[Alireza355]

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.

[NeoPa]

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

[FishVal]

Something like the following:

Query: qryT1LJT2

SELECT Table1.*, Table2.*
FROM Table1 LEFT JOIN Table2 ON Table1.Number = Table2.Number;
 

Query: qryLastRecordsUIDs

SELECT qryT1LJT2.Table1.Number, Max(qryT1LJT2.uniquenumber) AS LastRecordUID
FROM qryT1LJT2
GROUP BY qryT1LJT2.Table1.Number;
 

Query: qryT1WithT2UID

SELECT Table1.*, qryLastRecordsUIDs.LastRecordUID
FROM qryLastRecordsUIDs INNER JOIN Table1 ON qryLastRecordsUIDs.Number = Table1.Number;
 

Query: qryFinal

SELECT qryT1WithT2UID.*, Table2.Results1, Table2.Results2
FROM qryT1WithT2UID LEFT JOIN Table2 ON qryT1WithT2UID.LastRecordUID = Table2.uniquenumber;
 

Regards,
Fish

[Alireza355]

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.