By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Referencing the queries

P: 10
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,
Dec 23 '07 #1
Share this Question
Share on Google+
7 Replies


Jim Doherty
Expert 100+
P: 897
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 :)
Dec 23 '07 #2

P: 10
Cheers,

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

Do you need any more. Thanks for your speedy response
Dec 23 '07 #3

Jim Doherty
Expert 100+
P: 897
Cheers,

In the first table i have Client=>ClientID, 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
Dec 23 '07 #4

Jim Doherty
Expert 100+
P: 897
No give me a minute I'll post it straight back

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT tblVisit.clientid, tblVisit.[date], tblVisit.VisitID
  3. FROM tblVisit
  4. 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)))
  5. ORDER BY tblVisit.clientid, tblVisit.[date];
  6.  
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 :)
Dec 23 '07 #5

P: 10
Brilliant,

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

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

And the Text1.Value = MyRec![VisitID]

how do offset the Record to move onto the next one without putting it into a loop?
Dec 23 '07 #6

Jim Doherty
Expert 100+
P: 897
Brilliant,

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

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

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 :)
Dec 23 '07 #7

P: 10
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
Dec 24 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.