General Questions and advice

[Xiael]

Hello all,


I have been playing and tinkering with a database idea for my company for years now. I finally have had time to look at it a lot more here recently. I've set up my tables how I think they are needed. I've got relationships linking them all, I believe, accurately.



The main problems I feel like I'm having are the forms. My main goal is to have a form that is for all client information, with subforms for the client's guardian information, their doctor information, etc. Any advice?

 

[Galaxiom]

Post your structure. It is impossible to advise on forms without knowing the table structure.

Moreover, it is easy to get the table structure for something like this wrong and a lot of work to fix later.

 

[June7]

If clients can have multiple guardians and doctors, then definitely dependent tables and subforms.

Can someone be a guardian for more than one client? Certainly doctors can associate with multiple clients. These are many-to-many relationships. A m-to-m relationship requires 3 table.

Clients

Guardians

Doctors

ClientGuardians

ClientDoctors

But what if a doctor is also a guardian? People relationships get so complicated! Sometimes just have a single table for all people. Then a table that pulls from the People table to build associations.

 

[jdraw]

Post a copy of the database structure in zip format. The only way anyone can comment on your relationships is to have a list of your business rules.
Create some test data and test scenarios and "test your model" on paper before getting too deep into forms and physical database.
Good luck.

 

[Xiael]

Sorry everyone, I thought I'd posted the database earlier, but apparently it was too large of a file without zipping.


And yes, one client can have more than one guardian.

 

[Galaxiom]

Sorry everyone, I thought I'd posted the database earlier, but apparently it was too large of a file without zipping.

New members have to zip attachments until reaching ten posts. It is a good thing to do anyway.

 

[jdraw]

I've got relationships linking them all, I believe, accurately.

I don't see any relationships in the database???

 

[Xiael]

You just have to hit show all relationships. I hid them as I was going through them to help keep me on track. Once they were hidden I knew they were done as far as I knew how.

 

[June7]

I would not include queries in the Relationships builder.

 

[Xiael]

Alright, so I'd just have them linked to the contact tab or even the cguardian/cdoctor tables? That makes sense, I think.

 

[mike60smart]

Hi

There are many issues with your table structures

There is virtually no Referential Integrity Enforced between tables.

Tables should have a proper name for the PK vice just "ID" and there should be no spaces in a table name ie

Client Information this should look like this:-

tblClientInformation
- ClientInformationID - PK - Autonumber
-(Other fields describing the Client Information)

Why have a table for "Client Information" and then another table "Contact List" with all of the information that should be contained in "tblClientInformation"??

Your Contact List table has a Lookup field "ContactTypeID" this should just be a Text DataType and the Lookup to the table "Contact Types" should be created on the Form displaying the Contacts Details.

 

[Xiael]

I can fix most of those issues pretty easy I think.


The reason I had contact list and client information was due to another suggestion someone gave me before. I need more information on the clients than I do on the generic contact, so I made a separate table for it.



Why would the look up need to be on the form instead of the table? Does it just run better if I'm doing it from the form rather than from the tables?

 

[mike60smart]

Hi

Lookup fields in tables are a No No in Access.

Lookup "The Evils of Lookup Fields in Tables" for an explanation of why you should NEVER use them

 

[mike60smart]

http://access.mvps.org/access/lookupfields.htm

 

[Xiael]

Okay,


I mainly need to update my look ups to be from the forms not from the tables. However, at that point to distinguish types do I just need to have a selection list in the table itself?


Beyond that I need to rename tables. I also need to change my relationships to not involve the queries, but the tables themselves?

 

[mike60smart]

Hi
Instead of creating the list in the table field you need to create a separate table and list the values there as records.

Then on the Form create a Combobox to Lookup the values from the table you have created and store the Primary Key for the value that you select.

 

[Xiael]

I really appreciate all the advice. I obviously was further off than what I thought originally. I decided to break my whole database back down and start from scratch. I figured that was easier than scrapping the relationships and fixing the table names. I will post that here in a bit when I get it finished.

 

[Xiael]

Alright, I've got the new tables and relationships built. However, I definitely ran into a question on the relationships. Almost all of my relationships come from the contactlist table. Sometimes, that led to 2-3 relationships from contactlist to a table. Is that okay? It seemed kind of weird to do.

 

[Mark_]

Very first thing I notice; you have ContactList linked to BOTH CMeds and CMedDiagnosis while CMeds and CMedsDiagnosis are linked together. I am guessing one of the links should not be there.

I also noticed you have generic lookup tables; Error, ContactType and Days. For myself I would have a "Lookup" table to hold these types of values. You would simply need to identify which lookup you are using, then save all of them together.

I'd also guess that your "Emp" records would be normally linked off of Payroll rather than directly off of the ContactList. They are all employment related, so linking them to an employment record would seem more proper.

 

[Xiael]

Alright, see that makes sense and is one of the things that was confusing me when I had it all laid out. I was just trying to link everything with the appropriate ID. Example being the Cmeds and Cmeddiagnosis. The reason I did that one is because some medications may be used for multiple diagnosis, so I thought having the one table specifying which it was for would be needed.


The lookup table makes perfect sense, and would really eliminate a lot of what I felt were extra tables.



I also wondered about the emp links, and the client links as well actually. It would kind of make sense to have the clientinformation be the link to the meds, diagnosis, etc rather than the contact list because only the clients will have that extra information. I would probably link empinfo rather than payroll though.