Solved

MS Access - Query - return 2 records for each

Posted on 2012-04-11
9
271 Views
Last Modified: 2012-04-11
I need to return only 2 records for EACH [ExpenseAccount] in @groupedStrings from [Source_Data].  [Source_Data] has thousands of matches for each value in @groupedStrings

The query I have below returns all.  I only want 2 records for each.  How do I do that?


SELECT [@groupedStrings].ExpenseAccount, Source_Data.AssetNumber
FROM ([@groupedStrings] INNER JOIN ExpenseAccount_RAMBO ON [@groupedStrings].ExpenseAccount = ExpenseAccount_RAMBO.[Payable Expense Number]) INNER JOIN Source_Data ON ExpenseAccount_RAMBO.lessor = Source_Data.LessorCode;

Open in new window

0
Comment
Question by:keschuster
  • 5
  • 3
9 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 37834212
SELECT TOP 2 [@groupedStrings].ExpenseAccount, Source_Data.AssetNumber
FROM ([@groupedStrings] INNER JOIN ExpenseAccount_RAMBO ON [@groupedStrings].ExpenseAccount = ExpenseAccount_RAMBO.[Payable Expense Number]) INNER JOIN Source_Data ON ExpenseAccount_RAMBO.lessor = Source_Data.LessorCode;
0
 

Author Comment

by:keschuster
ID: 37834221
That only returns 2 records TOTAL.  I need 2 records from the joined table [Source_Data] for EACH matching record in @groupedStrings
0
 

Author Comment

by:keschuster
ID: 37834235
@groupedStrings contains the values

1
2
3

Source_Data contains

1
1
1
1
2
2
2
2
2
2
3
3
3
3
3

I want back just 2 records for each match.  So from source data I want

1
1
2
2
3
3
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37834455
among the numerous records of 1 's (for example)  what criteria are applied to get the two records?
0
 

Author Comment

by:keschuster
ID: 37834490
Let me better illustrate

The more I think about it my original stab at the sql may be too complicated.

The table looks like this

ExpenseAccount     | VIN
1                             | 10
1                             | 11
1                             | 12
2                             | 20
2                             | 21
2                             |22


So for a single ExpenseAccount there can be many VIN's.  Vins are unique

What I want to return is for each unique ExpenseAccount any 2 Vins
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37834675
where is the field VIN coming from?

better if you can upload a sample db..
0
 

Author Comment

by:keschuster
ID: 37834783
see atttached
sample.accdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37834952
try this query

SELECT tblFeed_Rambo.ExpenseAccount, Max(tblFeed_Rambo.VIN) AS MaxOfVIN
FROM tblFeed_Rambo
GROUP BY tblFeed_Rambo.ExpenseAccount
Union ALL
SELECT tblFeed_Rambo.ExpenseAccount, Min(tblFeed_Rambo.VIN) AS MaxOfVIN
FROM tblFeed_Rambo
GROUP BY tblFeed_Rambo.ExpenseAccount
Order By 1
0
 

Author Comment

by:keschuster
ID: 37835000
Interesting approach....  you win.  Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question