How do I return only the first 2 records where an account number .

J

Justk3771

I have a report in access where there are thousands of records I want to pull
on the first two records of each account number by date and I am not sure how
to do it/
 
A

Allen Browne

Simplest is to just draw the account numbers into the main report, and use a
subreport to list the top 2 for each one.

The query for the subreport will look something like this:
SELECT TOP 2 Table1.* FROM Table1 ORDER BY Table1.ID;
 
J

Justk3771

Allen thanks for trying to help but I guess I am just not getting it. Here
is what my data looks like:
Date Acct # Problem-Code
01/02/05 1607-2545 Connectivity
01/04/05 1607-2545 Email
01/05/05 1607-2545 Connectivity
01/06/05 1653-2646 Email
01/07/05 1653-2646 Email
01/08/05 1653-2646 Email

What I want it to return in finished product is
01/02/05 1607-2545 Connectivity
01/04/05 1607-2545 Email
01/06/05 1653-2646 Email
01/07/05 1653-2646 Email
I am not really familiar with reports or sub reports. I really have only
worked with tables, queries and Pivot tables in access. So maybe that is why
I am not getting what you are telling me. I tried selecting only the top
values of 2 int he properties but then of course that only gives you
01/02/05 1607-2545 Connectivity
01/04/05 1607-2545 Email
Please Help
 
A

Allen Browne

Query for main report:
SELECT DISTINCT [Acct #]
FROM Table1;

Query for subreport:
SELECT TOP 2 [Acct #], [Date], [Problem-Code]
FROM Table1
ORDER BY [Acct #], [Date];

The main report then lists each account code as a heading.
The subreport is linked by Acct #, and gives the top 2 items for each Acct
#.

If you want more details and alternative solutions, see:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039

or
How to Create a Top Values Per Group Report
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208822
 
Top