Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2018, 05:01 AM   #1
Leyton01
Newly Registered User
 
Join Date: Jan 2018
Posts: 37
Thanks: 5
Thanked 0 Times in 0 Posts
Leyton01 is on a distinguished road
Mixed fields design suggestions

I am trying to figure out the best way to design a table and form to achieve a result.

I have 3 fields which I need users to enter:
[Facility type] - one of 5 options: hospital, racf, community service, GP and Other.
I have a set list for the first 3 options which I want to restrict users to selecting. The last 2 options a user can enter anything but I want to store what is entered for a possible future match.
The 2nd field is [Facility name]. As mentioned above this is either a set list which must be selected or can be entered for future retrieval depending on first field.
The 3rd field is [doctor name]. This is only required if the facility type is GP and similarly a user can enter anything but should be provided with previously entered options linked to the facility name (2nd field) ie set doctors work at a practice but there can be more than 1.

The 3rd field was only just added and is where I am struggling. My current setup for the first 2 fields is:

I have 2 tables - a reference table with the 5 different facility types (tblFacilityTypes) and another (tblFacilities) with all the data (fields include ID, FacilityType, FacilityName, DoctorName and Suburb)

[Facility type] is a combo box with a form-level lookup of the reference table.
With the OnChange event of this combo box it sets the row source of the second combo box to the tblFacilities matching the facility type of the first combo. This second combo has a NotInList event which either prevents entry of the facility type if it is one of the first 3 or prompts the user to create a new entry if it is one of the latter facility types and then inserts that detail into the tblFacilities.

This has worked reasonably well until I now need to add the doctor name and link it to the facility name but only for GP facility types.

Do I just persist and run another row source setting and another NotInList event for the final combo box or is there a better way? One of the issues I have is that this then creates 2 new entries - one from the NotInList event from combo box 2 and another from the same event in combo box 3 (the first without the GP name). I still need the second combo box to insert a new entry as the 3rd combo is only used specifically for GPs and, as such, is optional.

Am I going about this the right way it do I need to split the facilities table to have a separate table for the doctor's names linked to the facility names? (One facility can have many doctors)

As a bonus I would actually like the suburb either stored if previously blank or retrieved if available from the tblFacilities (similarly the suburb is available and set for the first 3 types but not the last 2 types).

Any guidance would be appreciated.

Leyton01 is offline   Reply With Quote
Old 06-14-2018, 06:38 AM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,279
Thanks: 62
Thanked 1,145 Times in 1,046 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Mixed fields design suggestions

First, a side comment just to get it out of the way. Don't make fields have names with spaces in them. It is far easier to type run-together names when coding in VBA as well as in SQL, since names with spaces ALWAYS need to be bracketed whereas names without spaces can drop the brackets in most cases. So three less characters every time (the space and two brackets).

OK, the main problem: The fact that you are potentially adding a doctor with a specific affiliation makes this a case where a pop-up form might be needed, since you have not only a write-in for the doctor but then you also have to select the affiliation to a facility. Obviously, once you select the [Facility Name], you have the name for doctor affiliation. Do you have a preset list of [Doctor Name] for the "no write-in" facility types where a [Facility Name] is only allowed to come from a fixed list? I.e. do you also have a list of known doctors for your hospitals? I have run across cases where a doctor has both hospital and private practice affiliations. If that can happen, you have a sudden many-to-many relationship, which would scream "junction table."

You say the logic of blocking arbitrary data entry for two types but allowing entry for the others is working for you, but I wonder whether a small pop-up form might give you better flexibility and control. I'm thinking along the lines of "isolation of function" so that you can avoid cross-contamination of functional requirements in your coding. Of course, that is using advice from that wise old sage, Julius Caesar, he of "Divide and Conquer" fame.

As to the "suburb" problem, since you are subject to that same complex logic, you might also need to consider a pop-up form.

Of course, any time you update an underlying data source for combos or list boxes, a control.Requery is required after the exit of the pop-up. But I infer that you either knew or have discovered that.

That discussion of [Facility Type] vs. [Facility Name] is a little bit confusing. Are you saying that once someone selects a facility type, there are some lists that cannot have an arbitrary name but for other types, a write-in [Facility Name] is permitted and is then permanently associated with that [Facility Type]? That is what I got out of it. (This is just a question for clarity.)
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Leyton01 (06-14-2018)
Old 06-14-2018, 03:41 PM   #3
Leyton01
Newly Registered User
 
Join Date: Jan 2018
Posts: 37
Thanks: 5
Thanked 0 Times in 0 Posts
Leyton01 is on a distinguished road
Re: Mixed fields design suggestions

Quote:
Originally Posted by The_Doc_Man View Post
First, a side comment just to get it out of the way. Don't make fields have names with spaces in them.
Sorry this was autocorrect on the phone - the fields are named without spaces (didn't autocorrect them in the 4th paragraph for some reason)


Quote:
Originally Posted by The_Doc_Man View Post
Do you have a preset list of [Doctor Name] for the "no write-in" facility types where a [Facility Name] is only allowed to come from a fixed list?
No the [DoctorName] is only used for the "GP" facility type which is a 'write-in' field. It is not used for any of the first 3 fixed-list facility types. So it is always "write-in" but should include the option of previously entered doctors for that facility name.

Quote:
Originally Posted by The_Doc_Man View Post
You say the logic of blocking arbitrary data entry for two types but allowing entry for the others is working for you, but I wonder whether a small pop-up form might give you better flexibility and control. I'm thinking along the lines of "isolation of function" so that you can avoid cross-contamination of functional requirements in your coding. Of course, that is using advice from that wise old sage, Julius Caesar, he of "Divide and Conquer" fame.

As to the "suburb" problem, since you are subject to that same complex logic, you might also need to consider a pop-up form.
This was the purpose of the thread was to see if I am making this too complex or if there is a better way - the only concern I have at this stage with a pop up is the data entry becomes a bit slower. Right now it is type, tab, type, tab etc and a when I have watched users they tend to reach for the mouse when a pop up arrives, even when focus is set correctly. (need better users? )

Quote:
Originally Posted by The_Doc_Man View Post
Of course, any time you update an underlying data source for combos or list boxes, a control.Requery is required after the exit of the pop-up. But I infer that you either knew or have discovered that.
Yes and I hate to have to requery so often - the other issue is when I move a record the old row source stays for that control and I need to re-set the row source using the on current event. More code and more requeries

Quote:
Originally Posted by The_Doc_Man View Post
That discussion of [Facility Type] vs. [Facility Name] is a little bit confusing. Are you saying that once someone selects a facility type, there are some lists that cannot have an arbitrary name but for other types, a write-in [Facility Name] is permitted and is then permanently associated with that [Facility Type]? That is what I got out of it. (This is just a question for clarity.)
Yes that is correct. We have a set list of hospitals and community services. The user must select these when entering data (they are associated with a specific code that is used for stats). There is no variation allowed in this list. The GPs and doctors, however, are an ever evolving list and this is where the user can enter anything but I think they should at least be presented with a (possible) prior entry to select from to keep it as consistent as possible.


Last edited by Leyton01; 06-14-2018 at 03:47 PM.
Leyton01 is offline   Reply With Quote
Old 06-14-2018, 08:16 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,279
Thanks: 62
Thanked 1,145 Times in 1,046 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Mixed fields design suggestions

Quote:
to see if I am making this too complex
We might have trouble answering that question because it requires us to know a LOT about your problem. In ALL cases, though, the code must be as complex as the problem. EXACTLY as complex - because your goal is a 1-to-1 correspondence between the map and the territory, so to speak. Your goal is to model the problem in code so you cannot simplify unless the piece you work on is simple.

As to requerying so often... if you are doing a variant on cascading combo boxes, that frequent requery is a cost of doing business.

Quote:
More code and more requeries
But at least if you are doing that, it is because you have discovered that you needed to do so? Nobody ever said making a database app was easy. You have a LOT of hard work to determine what needs to be done. Once you have the problem scoped out, though, the implementation isn't so bad. Trust me - this phase where you are asking all sorts of "would it be better to ..." questions is normal. Aggravating - but normal.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Design Suggestions Please linkaccess Theory and practice of database design 8 02-01-2013 03:59 PM
Design Suggestions Please terryvanduzee Theory and practice of database design 2 09-25-2008 08:04 AM
Design suggestions mergle Queries 3 03-12-2008 12:20 PM
Suggestions on design/structure Designer156 Theory and practice of database design 1 02-03-2008 09:32 AM
Design Suggestions Please dfwcomputer General 1 04-09-2005 03:00 AM




All times are GMT -8. The time now is 04:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World