Solved

MS Access - Query - return 2 records for each

Posted on 2012-04-11
9
269 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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
among the numerous records of 1 's (for example)  what criteria are applied to get the two records?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:keschuster
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
where is the field VIN coming from?

better if you can upload a sample db..
0
 

Author Comment

by:keschuster
Comment Utility
see atttached
sample.accdb
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
Interesting approach....  you win.  Thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now