By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

SELECT last record having same column data

Alireza355
P: 86
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
Feb 28 '09 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello.
  • Leftjoin table1 with table2.
  • Grouping query on that join to return "last" record per group.
Feb 28 '09 #2

Alireza355
P: 86
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

FishVal
Expert 2.5K+
P: 2,653
You can find more details in Access help concerning "GROUP BY Clause".
Feb 28 '09 #4

NeoPa
Expert Mod 15k+
P: 31,419
Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT   Table1.Number,
  2.          Table1.Explanation,
  3.          Last(Table2.Results) AS LastResults
  4.  
  5. FROM     Table1 LEFT JOIN Table2
  6.   ON     Table1.Number=Table2.Number
  7.  
  8. GROUP BY Table1.Number,
  9.          Table1.Explanation
  10.  
  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

Alireza355
P: 86
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, 99 views)
Mar 1 '09 #6

NeoPa
Expert Mod 15k+
P: 31,419
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

FishVal
Expert 2.5K+
P: 2,653
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;
  3.  
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;
  4.  
Query: qryT1WithT2UID
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.*, qryLastRecordsUIDs.LastRecordUID
  2. FROM qryLastRecordsUIDs INNER JOIN Table1 ON qryLastRecordsUIDs.Number = Table1.Number;
  3.  
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;
  3.  
Regards,
Fish
Mar 1 '09 #8

Alireza355
P: 86
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.