I have been so confused by this whole thing for the past few weeks. I have deleted and restarted a db more times than I care to remember. But.
I think I might finally have it....
I've read a lot of comments about not using lookup function in Access as it can cause problems down the road.
Are you saying do not use a lookup column in a main table? Instead use a combo box on a form? Which is bound to the field and table originally intended.?
Im sure there was an easier way of putting this, but..
You should have two tables. Create a relationship between the two. Then provide the dropdown on the form as you describe. The relationship provides relational integrity, the combo box helps the user select the right entry.
This is really basic stuff I know. but I'm glad I am on the right track. (Not the same page yet, but at least in the same library )
If I have 2 tables
The Primary Key in tblClients is a foreign key in tblGuardians
So is that the relationship right there.
Or do I need to go into database tools, relationships, and drag ClientID from tblClients to tblGuardians (so there is a line between them.)
I think this is the part that is confusing me.
Well that makes much more sense to me now...
What if you have 20 tables.. They all won't have physical relationship to your main tblClients..will they? Though they all 'may' have a logical relationship?
I don't know why I am finding this so difficult... Maybe I'm just too old for this.. heh
My suggestions (presuming there is no data issues to worry about) and bearing in mind that I don't know your application needs like you would, but from looking at that ERD, this is what I would recommend:
* Change Guardian.GuardianType to RelationshipID.
* Delete Relationship.GuardianID
* Delete Substance.ClientID
* Delete Staff.ClientID
* Move Substance.FrequencyOfUse to the ClientSubstance table -- IF this is to do with how often the client takes the substance and not how often the substance should be taken.
*The Clients.MedicationID, MedicationDosage and MedicationTime fields look like they should be in a Medication table... or amalgamated with the Substance table. Hard to say without clear goals.
* The Clients.Substance and FrequencyOfUse look out of place too, possibly they should just be deleted.
As for Client and Guardian, well it depends on the nature of the relationship.
Can one client have many guardians (that you want to record)?
Can one guardian oversee many clients (that you want to record)?
If you answer both of those a YES, then you're going to need a ClientGuardians table to form the many-to-many relationship.
The more I read your suggestions, the more clearer it becomes. Thank you so much.
I have revised my table structure, and its looking much... nicer? (for want of a better word)
I have added a couple of fields to ClientSubstance.
These are the drug of choice for the client based on their personal preference.
They may use alcohol as their primary substance, and maybe MaryJ only sometimes which would be secondary... etc...
I need to track the frequency of use (daily, twice a week, weekly etc..) for each of the fields. Primary, secondary and tertiary.
Would I then need 3 additional fields, primaryfrequency, secondaryfrequency and tertiaryfrequency.
I would have thought Substance:FrequencyofUse would have done it, but it's not. Or I did something wrong, which is far more likely
Pootle is right, following the rules of first, second, third and fourth normal forms will be a great thing to study to get this all under control... but I find that a little experience is needed as well.
I also like to help those who appreciate it, so, here's my second round of suggestions. Always remember to backup first!
* ClientSubstance.ClientSubstanceID is not needed.
* ClientSubstance.ClientID and ClientSubstance.SubstanceID are THE primary key (composite key). Select BOTH fields and then hit the key button.
* Re-create the ClientSubstance<-->Client relationship so that ClientID is the linked field for both tables.
* Rename ClientSubstance.PrimarySubstance to PreferenceLevel (which will contain primary, secondary or tertiary).
* Delete ClientSubstance.SecondarySubstance.
* Delete ClientSubstance.TertiarySubstance.
* Move Substance.FrequencyOfUse into the ClientSubstance table.
* Delete Clients.CurrentAge (this is always a calculation based on DOB, not a field).
* Delete Clients.Substance.
Don't know what ChartQA is, so not sure what to do with that one.
If both ClientSubstance.ClientID and ClientSubstance.SubstanceID are THE primary key in the ClientSubstance table, shouldnt it be the same in the Guardian table? with both GuardianID and CLientID being the Primary key?
Re: Client.CurrentAge If there is no field then it can't be stored anywhere? So it doesn't need to be stored? Which is neither here nor there really, just trying to understand.
Re: Client.ChartQA (Quality Assurance)
Every few weeks I have to go through each clients paper chart and figure out what is missing, signatures, vital info, documentation etc. This list or report is then emailed to that particular client's primary counselor for review.
I'm betting I need another table for that info.
I will work on this..
You are my favourite StarTrekker and Pootle Flumps...
Back to being confused again...
Regarding the substances a client uses. Substance.SubstanceType is pretty static. (alcohol, cocaine, MaryJ, OTC, Prescription etc...)
As is the Frequency of use (1-3 times a week, 4-6 times a week, daily, 3 times a month, 1 a month.... etc)
I was hoping to use these 2 fields as a picklist using a combo box.
As it is I am unable to store this info anywhere. I can pick from Substance.SubstanceType, But I'm stuck from there. If I add a field to ClientSubstance called SubstanceType, could it be stored there? Or am I off on the wrong track again....