Duplicate Recs problem(could end in divorce)

[Griffin]

I am quite frustrated at not being able to solve this problem in a Db I am developing for my wife. I have searched through a number of solutions to similar thread questions. I tried using "Distinct" in the SQL statement, Grouping using Max, Min, First and Last all without success.

So my db is a simple 2 table set up with a one to many relationship as follows:

tblPatient Details (RecNo (PK), Surname, FirstName, DOB, Postcode)

tblVisit Details (VisitNo(PK), VisitDate, RecNo(FK), Location, Category, Diagnosis, Drug1, Drug2, etc)

Whenever I run a query I am getting duplicated records of Visit information which should happen. However, I would like to have only one of the visits displaying (first or last doesnt matter.) The Visit No is a unique autonumber.

Can anyone help, any assistance would be greatly appreciated.

 

[Jon K]

You can do it with a series of two queries.

I have attached a sample database. You can run the second query to retrieve the most recent visit details.
.

 

[Griffin]

Thankyou Jon!

Thanks so much for all your help; I definately wouldn't have worked it out by myself!

It was good to learn about how to apply the use of multiple queries. I have applied your primary query to 11 others; all with success