If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
'current' record from many side of relationship
I have two tables, tblPatient and tblReferral, with a one to many
relationship. One patient can have many referrals. However one patitent can only have one current referral record. Is the best way to do this to put a field into the tblPatient named "CurrPatRef" (Current Patient Referral)? I thought about putting a "Current" check box field into the tblReferral table however how would I stop all Referral records of one patient being checked - giving many current referrals for that one patient. Cheers |
#2
|
|||
|
|||
'current' record from many side of relationship
Is the current referral the most recent one?
If so, you could just add a ReferralDateTime field (date/time type) to tblReferral. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "noodleBrain" wrote in message ... I have two tables, tblPatient and tblReferral, with a one to many relationship. One patient can have many referrals. However one patitent can only have one current referral record. Is the best way to do this to put a field into the tblPatient named "CurrPatRef" (Current Patient Referral)? I thought about putting a "Current" check box field into the tblReferral table however how would I stop all Referral records of one patient being checked - giving many current referrals for that one patient. Cheers |
#3
|
|||
|
|||
'current' record from many side of relationship
Thanks Allen,
Yes the current referral is the most recent one so I will use my RefDate field to filter. Next question - what criteria do I use in my query to show the latest date of the RefDate field - I searched the site but couldnt find any solution that helps. Cheers "Allen Browne" wrote: Is the current referral the most recent one? If so, you could just add a ReferralDateTime field (date/time type) to tblReferral. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "noodleBrain" wrote in message ... I have two tables, tblPatient and tblReferral, with a one to many relationship. One patient can have many referrals. However one patitent can only have one current referral record. Is the best way to do this to put a field into the tblPatient named "CurrPatRef" (Current Patient Referral)? I thought about putting a "Current" check box field into the tblReferral table however how would I stop all Referral records of one patient being checked - giving many current referrals for that one patient. Cheers |
#4
|
|||
|
|||
'current' record from many side of relationship
There are 4 solutions in this article:
Getting a related field from a GroupBy (total) query at: http://www.mvps.org/access/queries/qry0020.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "noodleBrain" wrote in message news Thanks Allen, Yes the current referral is the most recent one so I will use my RefDate field to filter. Next question - what criteria do I use in my query to show the latest date of the RefDate field - I searched the site but couldnt find any solution that helps. Cheers "Allen Browne" wrote: Is the current referral the most recent one? If so, you could just add a ReferralDateTime field (date/time type) to tblReferral. "noodleBrain" wrote in message ... I have two tables, tblPatient and tblReferral, with a one to many relationship. One patient can have many referrals. However one patitent can only have one current referral record. Is the best way to do this to put a field into the tblPatient named "CurrPatRef" (Current Patient Referral)? I thought about putting a "Current" check box field into the tblReferral table however how would I stop all Referral records of one patient being checked - giving many current referrals for that one patient. |
#5
|
|||
|
|||
'current' record from many side of relationship
Thanks Allen the article was great.
Cheers "Allen Browne" wrote: There are 4 solutions in this article: Getting a related field from a GroupBy (total) query at: http://www.mvps.org/access/queries/qry0020.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "noodleBrain" wrote in message news Thanks Allen, Yes the current referral is the most recent one so I will use my RefDate field to filter. Next question - what criteria do I use in my query to show the latest date of the RefDate field - I searched the site but couldnt find any solution that helps. Cheers "Allen Browne" wrote: Is the current referral the most recent one? If so, you could just add a ReferralDateTime field (date/time type) to tblReferral. "noodleBrain" wrote in message ... I have two tables, tblPatient and tblReferral, with a one to many relationship. One patient can have many referrals. However one patitent can only have one current referral record. Is the best way to do this to put a field into the tblPatient named "CurrPatRef" (Current Patient Referral)? I thought about putting a "Current" check box field into the tblReferral table however how would I stop all Referral records of one patient being checked - giving many current referrals for that one patient. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Primary Keys | potter | Database Design | 11 | January 31st, 2006 07:25 PM |
Adding tables | Gertjan | Running & Setting Up Queries | 1 | December 19th, 2005 04:20 PM |
Current date in last subform record | BruceM | Using Forms | 5 | October 11th, 2005 01:12 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Prevent Blank Records being written. Need Help. | Robert Nusz @ DPS | Using Forms | 4 | December 29th, 2004 05:15 PM |