Referencing the queries

[tmh86]

Hi,

I am currently building a database. The basic relationship is that one client has multiple visits. I am however having trouble showing the multiple visits on one form. I need to show the very first visit and then the previous 4. Any help would be great please guys.

I am aware i can view all th visits on a form from one of the options on the form wizard('Tabular Version) but i need to build it to my requirements,

Cheers,

[Jim Doherty]

Hi,

I am currently building a database. The basic relationship is that one client has multiple visits. I am however having trouble showing the multiple visits on one form. I need to show the very first visit and then the previous 4. Any help would be great please guys.

I am aware i can view all th visits on a form from one of the options on the form wizard('Tabular Version) but i need to build it to my requirements,

Cheers,

Hi Tm,

Welcome to the scripts

Please post your relevant fieldnames datatypes & table name so that any answer can reference those implicitly

Jim :)

[tmh86]

Cheers,

In the first table i have Client=>ClientI D, ClientName, DOB
In the second Visit=> VisitID, ClientID, Date

Do you need any more. Thanks for your speedy response

[Jim Doherty]

Cheers,

In the first table i have Client=>ClientI D, ClientName, DOB
In the second Visit=> VisitID, ClientID, Date

Do you need any more. Thanks for your speedy response

No give me a minute I'll post it straight back

Jim

[Jim Doherty]

No give me a minute I'll post it straight back

Jim

Assuming your table name is tblVisit (you can edit that)

 
SELECT tblVisit.clientid, tblVisit.[date], tblVisit.VisitID
FROM tblVisit
WHERE (((tblVisit.[date])=(Select Min([Date]) FROM tblVisit b where clientid=tblVisit.clientid) Or (tblVisit.[date])>(Select Max([Date]-4) FROM tblVisit b where clientid=tblVisit.clientid)))
ORDER BY tblVisit.clientid, tblVisit.[date];

Be mindful of the fact that this query will return more than four if you are simply enter the same date more than once into the date field.

If you are entering both date and time into same field then yes it will return just the four.

Is this sufficient?

Regards

Jim :)

[tmh86]

Brilliant,

One more q though. How do i then translate that onto the form.

Previously i have been using MyRec = db.RecordSet(Sq l)

And the Text1.Value = MyRec![VisitID]

how do offset the Record to move onto the next one without putting it into a loop?

[Jim Doherty]

Brilliant,

One more q though. How do i then translate that onto the form.

Previously i have been using MyRec = db.RecordSet(Sq l)

And the Text1.Value = MyRec![VisitID]

how do offset the Record to move onto the next one without putting it into a loop?

How do you mean translate? I visualised you having a single form for your client based on a query to support records for the client and then a listbox showing their visits on screen in a listbox

I have sent you a PM check it out please

Jim :)

[tmh86]

Thankyou for your help,

I have now figured out what i needed to do. For all those interested after executing the sql i put in the data values to the controls on the form,

Text1.Value = MyRec![Field1]

Then used MyRec.Movenext to move onto the next recordset