How to Select only one record from group of records in ms access?

Question

0.00/5 (No votes)

See more:

I have a table named datafile that contains records as below:

FamilyCode Name Age
1 ABC 23
1 DEF 25
2 GHI 58
2 JKL 23
2 PQR 56

And another table SCHEMETRN that contains records as below:

FamilyCode SchemeId
1 2
2 10

Now I have written query as
"SELECT distinct d.FamilyCode,first d.FullName,d.Age from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode "

This gives me result as

FamilyCode Name Age
1 ABC 23
1 DEF 25
2 GHI 58
2 JKL 23
2 PQR 56

I only want one record of familycode 1 and one record of familycode 2..
How to get this?

Posted 25-Jul-13 13:37pm

Rachna0309

Add a Solution

Comments

syed shanu 25-Jul-13 21:00pm

Hi,Do you need only Family code ,If so your query is correct ,because from your table different name and age for same Family code .
"SELECT distinct d.FamilyCode from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode"

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chui PuiKwan · Answer 1 · 2013-07-25T15:29:00

Solution 3

SQL

select B.FamilyCode,B.Name,B.age from datafile B,
(SELECT max(age) age,FamilyCode FROM datafile A WHERE A.FamilyCode IN(SELECT FamilyCode  FROM SCHEMETRN )
group by FamilyCode) C
where B.FamilyCode=C.FamilyCode and B.age=C.age

i got it in my sqlserver,and SCHEMETRN2 is SCHEMETRN !

the result is :

1 DEF 25
2 GHI 58

Posted 25-Jul-13 15:29pm

Chui PuiKwan

Updated 25-Jul-13 20:21pm

v3

Comments

berrymaria 25-Jul-13 23:29pm

Not sure if that's the exact query OP wants. But hey! You did output it!
Well done! My +5! :)

Chui PuiKwan 26-Jul-13 1:49am

ths.

berrymaria 26-Jul-13 2:15am

you're welcome! :)

Rachna0309 26-Jul-13 6:56am

Its not working in ms access

Ganesan Senthilvel · Answer 2 · 2013-07-25T15:13:00

You need to modify SQL code and you will get the expected answer

Original Query
SELECT distinct d.FamilyCode,first d.FullName,d.Age from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode

Modified Query

SQL

SELECT distinct d.FamilyCode,first d.FullName,d.Age from SCHEMETRN2 s2 inner join datafile d on s2.FamilyCode = d.FamilyCode

Ganesan Senthilvel · Answer 3 · 2013-07-25T15:13:00

Solution 2

You need to modify SQL code and you will get the expected answer

Original Query

SQL

SELECT distinct d.FamilyCode,first d.FullName,d.Age from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode

Modified Query

SQL

SELECT distinct d.FamilyCode,first d.FullName,d.Age from SCHEMETRN2 s2 inner join datafile d on s2.FamilyCode = d.FamilyCode

Posted 25-Jul-13 15:13pm

Ganesan Senthilvel

Updated 25-Jul-13 15:14pm

v2

Comments

berrymaria 25-Jul-13 23:24pm

Post only once. Removed the other.
Besides, your query provided doesn't answer the OP.

How to Select only one record from group of records in ms access?

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us