General Questions and advice (1 Viewer)

Xiael

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
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

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 03:53
Joined
Mar 9, 2014
Messages
5,423
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

Super Moderator
Staff member
Local time
Today, 07:53
Joined
Jan 23, 2006
Messages
15,361
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

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
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.
 

Attachments

  • HS18.zip
    240.1 KB · Views: 69

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Jan 20, 2009
Messages
12,849
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

Super Moderator
Staff member
Local time
Today, 07:53
Joined
Jan 23, 2006
Messages
15,361
I've got relationships linking them all, I believe, accurately.
I don't see any relationships in the database???
 

Xiael

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
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

AWF VIP
Local time
Today, 03:53
Joined
Mar 9, 2014
Messages
5,423
I would not include queries in the Relationships builder.
 

Xiael

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
Alright, so I'd just have them linked to the contact tab or even the cguardian/cdoctor tables? That makes sense, I think.
 

mike60smart

Registered User.
Local time
Today, 11:53
Joined
Aug 6, 2017
Messages
1,899
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

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
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

Registered User.
Local time
Today, 11:53
Joined
Aug 6, 2017
Messages
1,899
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
 

Xiael

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
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

Registered User.
Local time
Today, 11:53
Joined
Aug 6, 2017
Messages
1,899
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:53
Joined
Feb 19, 2002
Messages
42,970
The others have given you lots of work to do so I won't add to that at this stage but I'll try to alleviate some confusion regarding relationships.

Relationships between tables are used to enforce Referential Integrity (RI). The vast majority of tables in any schema will be related to at least one other table. Some central elements might be related to dozens. For example a client will be related to multiple other tables such as diagnosis, medications, appointments, etc. Wheres tables like Days will be related only to Schedule.

The purpose of RI is to ensure that all rows that exist in the database have valid relationships to other tables. For example, it makes no sense to have a row in cMeds that doesn't include a valid ClientID to point the the client and a MedicationID to point to a medication. RI in addition to preventing invalid adds, also prevents deletes that will create orphans. So, if you don't have Cascade Delete specified, you will not be able to delete a diagnosis if there are any records in cDiagonsis. This was just an example. It is almost certainly wrong to specify cascade delete on that relationship because if you deleted the cancer diagnosis, that would celete all the CDiagnosis records for ALL clients which would almost certainly be a disaster. So Cascade Delete is a very powerful setting and should only be used in cases where you want to delete child records. An example of that would be the child tables of a Client. If your application logic allowed clients to be deleted (most would not), then you would want to delete all the lower level dependent data when you delete the client.

When you create a query, you draw join lines between tables and queries. These just tell the query engine how to join the tables. They do not in any way have anything to do with Referential Integrity. All the query engine cares about is that the two columns you join have compatable data types. You could create a query that joins the employees table to the Clients table on the two ID fields. It makes no sense at all but the query builder will allow it because both columns are long integer. You will get a resultset that shows client a with employee joe and client b with employee mary, whatever. It won't provide any valid information but the query engine will do it happily.

And finally, RI always ONLY exists in the BE database. The reason for this is because it is enforced by the Jet or ACE database engine. It is not enforced by Access because Access doesn't even need to be installed on a computer for an application to use Jet or ACE. For example, you can use asp to create a web page and the web page can get and update date in your BE database. You could have an Access FE (application) linked to that same database. It will be up to ACE to control what each FE does with the data in the shared BE. Neither Access nor ASP (the front end applications) have anything to do with RI. They may have application logic that enforces business rules but if the rules are implemented differently
 
Last edited:

Xiael

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
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

New member
Local time
Today, 04:53
Joined
Mar 13, 2019
Messages
9
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.
 

Attachments

  • HS2019.zip
    71.3 KB · Views: 53

Mark_

Longboard on the internet
Local time
Today, 04:53
Joined
Sep 12, 2017
Messages
2,111
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.
 

Users who are viewing this thread

Top Bottom