Record suppression

[HomeALone]

I would like to diplay only the last 3 visit records of each patient in the subform or subreport. Is it possible to do this in a query, or a VBA must be written? Does anyone have an excample for this? Many thanks!

 

[SBendBuckeye]

From Access97 Help - To get last 3 sort descending should work. See the last example.

To try the following examples in Microsoft Access, create two new tables like those shown below. Create a new query in the Northwind sample database and close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

Clients Table

FirstName ClientID
Bob 1
Adam 2
Beverly 3
Bob 4

Invoices Table

ClientID InvoiceID
1 1
1 2
2 3
2 4
2 5
4 6
4 7

The following example returns all of the records returned by an inner join on the two tables.

SELECT ALL FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID;

Result
Bob
Bob
Adam
Adam
Adam
Bob
Bob

The next example selects only records with unique values returned by an inner join on the two tables.

SELECT DISTINCT FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID;

Result
Bob
Adam

The following example selects only unique records returned by an inner join on the two tables.

SELECT DISTINCTROW FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID;

Result
Bob
Adam
Bob

The next example selects the first five records returned by an inner join on the two tables.

SELECT TOP 5 FirstName FROM Clients INNER JOIN Invoices
ON Clients.ClientID = Invoices.ClientID
ORDER BY Invoices.InvoiceID;

Result
Bob
Bob
Adam
Adam
Adam

Good Luck!

 

[HomeALone]

Thank you for taking a try on this one, but the last query gave me the top 5 records of the combined tables. I am looking for the top 3 records for each client. The end result should be

Bob
Bob
Bob
Adam
Adam
Adam
Casey
Casey
Casey

and so on...

 

[SBendBuckeye]

Try creating the subform query by itself with the above logic in it and then connecting it to your main query or table. Hope this helps.

 

[HomeALone]

Any body else has any ideas on how to do this, or I need to do VBA to loop 5x for each record?

 

[SBendBuckeye]

Sorry it took so long but the following works. I created a form and a subform. The key field connecting the 2 was a field named Group. On the main form I had a text box named txtGroup. I then created the following query and used it as the source for my subform which I created as a continuous subform. It will then run each time you get a new record for your main form.

SELECT TOP 3 tblGroupByTest.Group, tblGroupByTest.Number
FROM tblGroupByTest
WHERE tblGroupByTest.Group=Forms!frmGroup!txtGroup
ORDER BY tblGroupByTest.Group, tblGroupByTest.Number;

If you post an email address, I will be happy to zip up the little example I used to try this out and send it to you.

Good Luck!