UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Possible Many-to-many Relationship & Form Design, Access 2010    
 
   
abeck4
post Jan 8 2018, 07:45 PM
Post#1



Posts: 17
Joined: 15-December 15



I apologize; I wasn't sure whether this topic was best suited for this board or for the Tables board...if it should be moved please let me know!



I work in a population/community health agency within a hospital. We help connect our clients to local agencies that can assist with any unmet needs (whether that be medical or social). When our agency was started a few years ago, it was pretty experimental, and initially staff were using an Excel sheet to track visits. Eventually they created an Access 2010 database which Iíve since inherited. Iím no database designer, but I do like tech stuff and data far more than other staff so itís become my pet project. Iíve taught myself quite a bit and have a pretty good understanding of SQL & VBA. I still canít really write any overly-involved VBA on my own, but Iím pretty good at adapting code examples that I find, and piecing together different examples to get what I want.

My hospital was recently acquired by another, and weíre losing our database on Feb 1 when we officially become the new hospital . On the bright side, Iíve got the green light to go ahead and design a new Access database from scratch. Iím trying to get this nailed down because 1) itís better to have a solid working database from the start, rather than making changes as we go and 2) Iím having form design issues in my test database.

Iíve got several tables as some are lookup tables, but there are four essential ones:

tblClient:
ClientID (PK)
Clientís demographic info, contact info, etc


tblVisits: Each client has at least one visit (a contact with one of our staff)
VisitID (PK)
fkClientID (FK to client table)
Concerns presented during that visit, date, staff, how the client was contacted, etc

tblAgencies: A list of local agencies
AgencyID (PK)
Agency Name

tblReferrals (this would probably be more aptly named Visits2Agencies, or somehow indicating itís a junction table):
ReferralID (PK)not sure if I need this, some examples do and some donít
fkVisitID (FK to Visit table)
fkAgencyID (FK to Agency table)


During each visit, a client is referred to at least one community agency. More often than not, a single visit with a client consists of a number of referrals. For instance, maybe a client needs help accessing a primary care provider. I look at their options given their insurance and presenting concerns and give them information on multiple clinics that would fit their needs.


So I think that there is a many-to-many relationship between visits and agencies, as each visit can consist of referrals to multiple agencies, and each agency can be used as a referral source for multiple visits. However, Iíve also been mulling over the possibilities for way too long and may have thought myself in circles.



In the database I inherited, the referrals were a multivalue field in the visits table. We are hoping to upsize to a new program within the next couple years, so Iím trying to avoid using that ďfeature.Ē However, in the test database Iím using, Iím having difficulty designing a form that is able to present as much information as weíre currently able to view on a single screen with the multivalue field. In the test database I have a many-to-many relationship set upÖwhich as noted, Iím now questioning, so I may have a whole other problem to look into first! I have went ahead and attached some photos of my very rough new main form.



The example form design for the new database is just a first go-round; Iím in no way married to it. I donít know how I feel about having to add/edit both the main form and subform in separate forms, though I believe I could change the main form to allow data entry/addition/edit/etc and would only lose some of the aesthetic value.

I was thinking I could somehow use one of the many well-known concatenation scripts, and maybe using a pop-up form for visit data entry, but I canít get it to work properly, nor do I know if thatís even really the way to go.

I almost got the concatenation to work by setting the subform referral control source to a formula (=ConcatRelated("AgencyName","qryConcat","fkVisitID = " & Nz([txtVisitID],0))), but I canít get that to consistently work- I know it would require a separate data entry form. Iíve also tried having another form open on double-clicking of that field, in which the agencies are selected and would populate that field (i.e., the rest of the visit data is entered on the main subform, and the popup form is a listbox of agencies). Iíve tried so many other ideas that I canít even remember anymoreÖso much so that Iím tempted to say forget it and use the MVF, but I honestly donít even care for how that looks other than the space it saves in presenting the information.

I was convinced that I had my tables/relationships properly configured, which is why I went ahead into form designÖ.but now Iím so overwhelmed by the design that Iím questioning everything! Any insight into my tables & relationships and/or ideas on how to best display this information is seriously appreciated.

Attached File(s)
Attached File  Current_DBform.PNG ( 56.77K )Number of downloads: 38
Attached File  New_Relationship.PNG ( 20.12K )Number of downloads: 22
Attached File  DesignAttempt.PNG ( 46.06K )Number of downloads: 38
 
Go to the top of the page
 
doctor9
post Jan 9 2018, 09:44 AM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Here are a couple of basic ideas of how I'd design the data entry:

First, you probably already have a form for entering data for each visit, basedon the tblVisits table. This should be a Single form. To enter multiple referrals for a single visit, one option would be to create a Continuous subform based on the tblReferrals table. You'd use the VisitID/fkVisitID fields as the subform's Parent/Child links. You'd have a combobox that's bound to the fkAgencyID field, and use the tblAgencies table as the RowSource to show the list of agencies. With this setup you can just select each agency that was set up as a referral during that visit.

If the list of agencies is short, another option would be to use a multiselect ListBox instead of a continuous subform. This would require a bit of coding, but basically you could have a list of agencies that are all visible at once, and the user can select/deselect agencies on the listbox during the visit. You'd use code called from the form's Current event to populate the listbox with the current visit's referred agencies, and code in the listbox's AfterUpdate event (or maybe the form's BeforeUpdate event) to convert the selected agencies into records in the tblReferrals table.
Hope this helps,

Dennis
Go to the top of the page
 
abeck4
post Jan 9 2018, 02:25 PM
Post#3



Posts: 17
Joined: 15-December 15



Thank you for taking the time for feedback; it's appreciated!

Either a continuous subform or listbox were my initial thoughts, but we're used to being able to see 10+ visits at once/immediately on the current form (which uses a datasheet subform and the MVF referrals field). I know that I can't completely replicate that unless I somewhat denormalize my tables and relationships, which is silly.

I've also toyed with the idea of doing two synchronized subforms on the main form (one based on visits and one based on referred agencies), and having it such that when a visit is selected the other subform shows what agencies were used. Maybe the second subform could instead be a listbox, but there are about 250 possible agencies and it would be a pain to scroll through all of them.

I also had the nefarious notion that I should just design it to be as obnoxious as possible so that maybe we get new software sooner evilgrin.gif But as much as it drives me crazy, I have this love-hate relationship with Access and think I'll miss it.
Go to the top of the page
 
doctor9
post Jan 9 2018, 02:35 PM
Post#4


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


abeck4,

Your idea for synched forms could be simplified a bit. Since you're using a continuous form to view visits, you likely can't see EVERY detail about a visit on that form. So, include a command button within the detail section that allows the user to open a popup form that shows the full details of the selected visit, including the list of referrals in a continuous subform of the popup form. The user can view/edit the visit, then close the popup form to go back to working with the continuous form view of visits.

Think of the continuous form you're using now as the "wide angle view" and the popup form as the "zoomed in view", if you will.

Hope this helps,

Dennis
Go to the top of the page
 
tina t
post Jan 9 2018, 04:52 PM
Post#5



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


PMFJI, one comment re the Relationships screenshot you posted: recommend you remove the field AgencyName from tblReferrals. that is a duplicate of field AgencyName in tblAgencies; it's redundant data that is not needed and doesn't belong in tblReferrals.

hth
tina
Go to the top of the page
 
abeck4
post Jan 9 2018, 08:00 PM
Post#6



Posts: 17
Joined: 15-December 15



Yep, I actually caught that early on but forgot to delete the field from my test database. Thank you!
Go to the top of the page
 
tina t
post Jan 9 2018, 08:18 PM
Post#7



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


you're welcome, good luck with your db! :) tina
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 09:39 AM