How to Link the Fields of my Tables in to a Master Table? (1 Viewer)

[JithuAccess]

Hello Guys,

Can I link the Fields of my Tables in to a Master Table?

I have 5 Tables in my Database and Each 5 Tables have 5 Fields. I have a Master Table which have 25 Fields (Table 1'2 5 Fields + Tables 2's 5 Fields + etc) and at the End of the day's Data Entry, I would run a query (I gave Data Entry Date as Criteria so that I will get each day's Data in to this query). Then I manually copy and paste this query result in to my Master Table and my Master Table would have all these 5 Tables data. Now I want this to be automated. If a user Enter a Data in to Table 1's 2nd field for example, I want to paste that data exactly in the second field of my Master Table. If the user Enter 5th field of Table 3, I want to paste that data exactly in the 15th field of my Master Table.

I am just wondering whether we can do this in Access or do I have to do the copy paste of my data manually?

Thanks a lot

 

[Pat Hartman]

You can do anything you want including this but why? It is generally bad practice to keep the same data in multiple places. Please tell us your business rules objective and we'll see if we can offer an alternative.

 

[plog]

Your post sounds like a guy who is well versed in Excel trying to make the move to Access. Databases are a whole different beast from spreadsheets. In a database, tables are set up properly and data is stored in one location. Then queries are used to bring that data together, not move it around.

Further, you shouldn't have multiple tables with the exact same structure. I fear your 5 sub tables are essentially the same table, differentiated by the table name. You wouldn't have table named 'Monday', a table named 'Tuesday', a table named 'Wednesday', etc. Instead you would have 1 table and then a field to differientiate what day each record is for.

My suggestion is to read up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

.

That's the process of properly setting up your tables. Give that link read. Google a few tutorials, apply what you learn to your database and then post a copy of your database back here and we can help.

 

[JithuAccess]

You can do anything you want including this but why? It is generally bad practice to keep the same data in multiple places. Please tell us your business rules objective and we'll see if we can offer an alternative.

Million Thanks for your reply.

Yes I know this is a very bad practice and this doesn't make any sense to copy paste the same data. But I have to do this

I have 5 Tables

1) Patient Information

2) Physician Information

3) Facility Information

4) Patient Application Information

5) Patient Correspondences Information

We want to display the values of all these 5 Tables in a single Form. So I am pasting the values of these tables in to a master Table and create a Form and give this table as Record Source for that Form.

I thought the simplest method is using a query and connect all these 5 Tables with Patient ID. But we have Many Physician for a Single Patient and many facilities, many Applications for a single patient etc. When I try to create a query and connect this with the Patient ID, we have 1000s of duplicate values. For Example, if a patient have 3 Applications, instead of showing 3 Application details, the query gave me 100 results and I have no idea why. We can't use Patient ID as Primary Key in except Patient Information. So with my limited exposure to Access, I thought to create a master Table and then create a Form and give this Master Table as Record Source then display all these 5 Tables details in a Single Form. I know it's a lengthy and boring procedure. But I have no other choice.

It will be really helpful if you could let me know how to link my 5 Tables fields with the Master Table's Fields

Thanks a lot

 

[plog]

I know it's a lengthy and boring procedure. But I have no other choice.

This is straight out of the Excel guy playbook. First post is littered with normalization errors, replies talk about normalization, poster comes back with vague mandates about doing it incorrectly. Exactly why must this done be incorrectly? Us database guys are lazy, we'd rather do a little more work upfront to save us months/years of work down the road. What you've proposed is going to require more work in the long run, a better structure will avoid that.

But we have Many Physician for a Single Patient and many facilities, many Applications for a single patient etc...
...For Example, if a patient have 3 Applications, instead of showing 3 Application details, the query gave me 100 results and I have no idea why.

Extra records sounds like a common problem with queries involving multiple tables that are poorly structured. Further, I don't see how this will be avoided with your proprosed rube goldberg system.

I suggest you post your existing database with a few rows of fake sample data (Mickey Mouse, George Washington, etc.) in your tables and walk us through the issue.

 

[JithuAccess]

This is straight out of the Excel guy playbook. First post is littered with normalization errors, replies talk about normalization, poster comes back with vague mandates about doing it incorrectly. Exactly why must this done be incorrectly? Us database guys are lazy, we'd rather do a little more work upfront to save us months/years of work down the road. What you've proposed is going to require more work in the long run, a better structure will avoid that.



Extra records sounds like a common problem with queries involving multiple tables that are poorly structured. Further, I don't see how this will be avoided with your proprosed rube goldberg system.

I suggest you post your existing database with a few rows of fake sample data (Mickey Mouse, George Washington, etc.) in your tables and walk us through the issue.

Many thanks for spending your time and effort to read and reply my post. Apologies for corrupting the great Access database with my limited knowledge. Let me create a sample Database and let us crack this.

Once again thanks a lot

 

[JithuAccess]

This is straight out of the Excel guy playbook. First post is littered with normalization errors, replies talk about normalization, poster comes back with vague mandates about doing it incorrectly. Exactly why must this done be incorrectly? Us database guys are lazy, we'd rather do a little more work upfront to save us months/years of work down the road. What you've proposed is going to require more work in the long run, a better structure will avoid that.



Extra records sounds like a common problem with queries involving multiple tables that are poorly structured. Further, I don't see how this will be avoided with your proprosed rube goldberg system.

I suggest you post your existing database with a few rows of fake sample data (Mickey Mouse, George Washington, etc.) in your tables and walk us through the issue.

Hi,

I have created the sample database, please find the attachment.

Now, let us crack our issues. Let us start with a simple one.

I want to display the values of my 7 Tables in to a Single Form. For that, I have created a simple query like this:

And this is what I am getting:

I don't know from where this 796 Records are coming?

In my Rubic Goldberg method, I am using this exact same query with an Extra criteria that is Data Entry Date. If I done 20 Data Entry today, at the end of the day when I run this same query with that Extra criteria, I am getting those exact 20 Entries not the extra 1000s data from no where in my query result.

Could you kindly let me know what was the actual bug in my Method and I will be highly oblige for you and the Forum for this.

Thanks

Could you kindly guide me?

Thanks

 

[Isaac]

Next thing on the docket: Joins

LEFT JOIN, RIGHT JOIN Operations - Microsoft Support

Use the LEFT JOIN and RIGHT JOIN operations to combine source-table records when used in any FROM clause.

support.microsoft.com

MS Access 2003: Create a query using a Left Join

This MSAccess tutorial explains how to create a query using a LEFT JOIN in Access 2003 (with screenshots and step-by-step instructions).

www.techonthenet.com

You also have to take the time to understand what a query is supposed to return.

If you have one table with 5 patients with unique PHN's, and a table with 10 payments--each patient represented twice--and you do a query joining those tables on PHN, you are going to get 10 records. Each patient will be listed twice.

This is a mental exercise/scenario where I'm not discussing a where clause or a join predicate. Just the most basic principle the way I like to think about it:
If you join 2 tables together on a common identifier (like PHN), where the smaller table (Patients) has X number of PHN's, and the bigger table (Payments) has Y number of PHN's, then the query result is going to be at the level of granularity similar to the level of granularity of the bigger table--which is, one record per payment.

If you don't wish this to be the case, then you have to come up with a logical alternative that actually is possible from those 2 tables.
I.E.: If you know your payments table only has 1 payment per patient per date, and you want your query result to have the same number of results as table Patients, then you either need a Where clause or a Join predicate:
- Where Payments.PaymentDate=#5/5/2020# or
- From Patients Join Payments on Patients.phn = Payments.phn and Payments.PaymentDate=#5/5/2020#. Actually I'm not sure if you can do this second method in Access.

But really, isn't this "limitation" or "rule" the same if you pasted all your records into one table? Think about it...if every day you paste your records into one table over and over, you're going to come up with exponentially more records anyway. In Excel, you'd have to create multiple Filters. In Access, you'll create queries with Joins and a Where clause that match what you desire the specific, focused results to be.

The solution IS to properly structure your tables (primary keys, foreign keys), maintain one-to-many relationships where possible (and when you think you need something else, challenge that thoroughly to make sure), and then create Queries based on that.

My suggestion is to start with just two tables and play with those results to understand how it works. Involving 10 tables in your query is something for later down the road.

A typical, normal use case scenario is probably not EVER going to be to display a report to the user that involves all 10 of your tables. It's going to be to either 1) create a report that shows one focused aspect, like "Payments for this customer", or "All Payments This Quarter", and 2) to create Forms (for viewing, editing and adding information) which contain parent forms (for the parent entity, perhaps Patients), and child forms (for the child entity, like all that patient's payments or visits).

 

[MajP]

1. Remove all the IDs you currently have.
2. Make new ideas in all tables. They should be the name of the table and be an autonumber
PaymentID, CorrespondanceID, FacilityID....
This includes the patient table make it PatientID and autonumber seperate from the patient number.
3. Get rid of patient name in other tables beside pateint
4. Any table that one patient could have many records (payments, correspondance...) add a numeric field called
PatientID_FK.
All one to many will relate from PatientID to PatientID_FK

However you need some many to many tables.
A patient can have many doctors and a doctor can have many patients. You need junctions tables
tblPatient_Physician
tblPatient_Physician

Patient_Physician_ID	PatientID_FK	PhysicianID_FK
1​	1​	1​
2​	1​	2​
3​	3​	1​

In this example Patient 1 has doctors 1 and 2. Patient 3 has doctor 1. The image below has a mistake in that the PatientNum should still be a seperate field.

 

[JithuAccess]

1. Remove all the IDs you currently have.
2. Make new ideas in all tables. They should be the name of the table and be an autonumber
PaymentID, CorrespondanceID, FacilityID....
This includes the patient table make it PatientID and autonumber seperate from the patient number.
3. Get rid of patient name in other tables beside pateint
4. Any table that one patient could have many records (payments, correspondance...) add a numeric field called
PatientID_FK.
All one to many will relate from PatientID to PatientID_FK

However you need some many to many tables.
A patient can have many doctors and a doctor can have many patients. You need junctions tables
tblPatient_Physician
tblPatient_Physician

Patient_Physician_ID	PatientID_FK	PhysicianID_FK
1​	1​	1​
2​	1​	2​
3​	3​	1​

In this example Patient 1 has doctors 1 and 2. Patient 3 has doctor 1

Thanks a lot for the suggestion.

 

[JithuAccess]

1. Remove all the IDs you currently have.
2. Make new ideas in all tables. They should be the name of the table and be an autonumber
PaymentID, CorrespondanceID, FacilityID....
This includes the patient table make it PatientID and autonumber seperate from the patient number.
3. Get rid of patient name in other tables beside pateint
4. Any table that one patient could have many records (payments, correspondance...) add a numeric field called
PatientID_FK.
All one to many will relate from PatientID to PatientID_FK

However you need some many to many tables.
A patient can have many doctors and a doctor can have many patients. You need junctions tables
tblPatient_Physician
tblPatient_Physician

Patient_Physician_ID	PatientID_FK	PhysicianID_FK
1​	1​	1​
2​	1​	2​
3​	3​	1​

In this example Patient 1 has doctors 1 and 2. Patient 3 has doctor 1

Unfortunately, we don't have time for redesigning and reconstructing the Table at this time. And I don't understand the concept completely. First, let me finish the data entry and continue the current process flow. Once the data entry is done I would have plenty of time and I will first try to understand the concepts and then reconstruct the Table.

Thanks a lot for your time and effort.

 

[MajP]

You may have more Many To Many. If there is a group of facilities and a patient can be assigned to multiple facilities over a period of time. Then you need a junction table and a Facility table. The facility table needs details of the facility. The junction table would have patientID_FK and FacilityID_FK along with something like dates arrival and departure. If a patient is assigned a single facility, then in the patient table you need a foreign key to a facility.

 

[MajP]

Unfortunately, we don't have time for redesigning and reconstructing the Table at this time.

You can redesign afterwards and do append queries to put in the new format. But you cannot really do anything with your current design. It is not "band-aidable".

 

[Pat Hartman]

You have a number of SEPARATE 1-many relationships. They CANNOT be handled in a single query. You are ending up with a Cartesian Product which is tbl1 * tbl2 * tbl3, etc. THAT's how you get to 796 recores.

You need a main form with several subforms.

 

[Isaac]

I hold a little bit different of a view on Junction tables. I don't want to start a big thing, but will just post my view.
I think that automatically creating a specially designated "Junction Table" every time you see a many-to-many relationship between any entities or events is not the way to think of it.
Rather, follow the principle to (usually - I'm generalizing) create a table for every unique Entity, as well as Transaction or Event. Those tables naturally serve as "Junction tables" - but not as much "because we need a junction table between every theoretically many to many", but rather, because any of the following is true:

- You have Patients and Doctors. One P can have many D's, and vice versa. But you should ultimately end up with things like tblAppointments. That table will hold foreign key value for Patient and foreign key value for Doctor. That will suffice.

- You have Orders and Products. One O can have many P's, and one P can have many O's. But you should ultimately end up with things like tblOrderDetails. Those records will have foreign key values - one for the Order, and one for the Product. That will suffice.

- You have Employees and Projects. One E can have many P's, and one P can have many E's. But, I also know that I need a table for ProjectDetails. These records will, of course, hold FK values that relate to both E and P. You can create a Junction Table in addition to ProjectDetails, but why? If you ever need a list of employees and their projects, or vice versa, you'll be querying the ProjectDetails table.

You can think of these as "junction tables" if you want, but I would rather follow the trail of creating tables for entities, transactions and events. You will end up with the correct tables for what you need for reporting. Rather than automatically creating "tblJunction_ProjectsEmployees" (or whatever) to try to immediately represent all of them.

This view may not hold true 100% of the time, but I think it covers 90% of the instances where people immediately create Junction Tables.
A quick search reveals that almost every legitimate example I see out there for why we need a junction table ends up not really just being a junction table, it's a logically necessary table that should already be being created anyway, to hold events, transactions, etc.

Perhaps my thinking is mostly semantics...but not entirely.

 

[JithuAccess]

1. Remove all the IDs you currently have.
2. Make new ideas in all tables. They should be the name of the table and be an autonumber
PaymentID, CorrespondanceID, FacilityID....
This includes the patient table make it PatientID and autonumber seperate from the patient number.
3. Get rid of patient name in other tables beside pateint
4. Any table that one patient could have many records (payments, correspondance...) add a numeric field called
PatientID_FK.
All one to many will relate from PatientID to PatientID_FK

However you need some many to many tables.
A patient can have many doctors and a doctor can have many patients. You need junctions tables
tblPatient_Physician
tblPatient_Physician

Patient_Physician_ID	PatientID_FK	PhysicianID_FK
1​	1​	1​
2​	1​	2​
3​	3​	1​

In this example Patient 1 has doctors 1 and 2. Patient 3 has doctor 1. The image below has a mistake in that the PatientNum should still be a seperate field.
View attachment 84916

Hi,

I have redesigned the Table as you suggest. But I am not getting the Query Result.

Could you kindly check this please.

Thanks,

 

[MajP]

Not sure what you expect to show in one tabular view. Does not make sense to me.
Assume a patient has 10 payments, 3 doctors, and 7 correspondence. How could you show this in a single view? What would this even look like?
You could have a query showing patients and all of their doctors, of a patient and all of their payments, or a patient and all of their correspondence. You could do this in a report where it shows Each patient and a subform showing their doctors, and a subform showing their payments, and a subform showing their correspondances.

 

[plog]

I assume we are talking about QryForum_v1. For that to generate records every single link you have must make a match. No match between tblForum Correspondence and tblForum Pateint, no data in the whole thing. No match between tblForum Physician and tblPatient_Physician, no data in the whole thing.

I suggest you start again and piece it together. Add tblForum Patient and link it to tblForum_Payments, run it and make sure it produces the expected results. Then add tblForum Correspondence and run it to make sure it produces expected results, then the next table then the next table. When you encounter a problem you know the table which caused it.

Also, you linked tblPatient_Physician to tblFourm Patient incorrectly. Lastly, remove the spaces in names (table and field: tblForum Physician->tblForumPhysician, strPatient City->strPatientCity ) makes discussing them hard and also makes writing code and queries that more difficult because you have to use brackets [].

 

[JithuAccess]

Not sure what you expect to show in one tabular view. Does not make sense to me.
Assume a patient has 10 payments, 3 doctors, and 7 correspondence. How could you show this in a single view? What would this even look like?
You could have a query showing patients and all of their doctors, of a patient and all of their payments, or a patient and all of their correspondence. You could do this in a report where it shows Each patient and a subform showing their doctors, and a subform showing their payments, and a subform showing their correspondances.

Apologies for making you confused.

This is my Master Table and Yes I am manually pasting the values from the queries and pasting it in the Master Table.

And this is what I want I am getting Exactly what I need in the Access Form:

My question was that instead of manually copying the data in to the Master Table, is there any method to link the Fields of my sub Tables with the fields of Master Table. So that when I doing the data entry, these data will copy in the Master Table automatically.

Let's forget about the Primary Keys, Foreign Keys, Data Normalization and all those advanced and Professional stuffs, since our business need is just creating a report based on the values and make it sweet and simple. It would be highly appreciated if you could kindly let me know if there is any way to connect the links of my sub Table with my Master Table. Problem Solved.


Thanks

 

[JithuAccess]

I assume we are talking about QryForum_v1. For that to generate records every single link you have must make a match. No match between tblForum Correspondence and tblForum Pateint, no data in the whole thing. No match between tblForum Physician and tblPatient_Physician, no data in the whole thing.

I suggest you start again and piece it together. Add tblForum Patient and link it to tblForum_Payments, run it and make sure it produces the expected results. Then add tblForum Correspondence and run it to make sure it produces expected results, then the next table then the next table. When you encounter a problem you know the table which caused it.

Also, you linked tblPatient_Physician to tblFourm Patient incorrectly. Lastly, remove the spaces in names (table and field: tblForum Physician->tblForumPhysician, strPatient City->strPatientCity ) makes discussing them hard and also makes writing code and queries that more difficult because you have to use brackets [].

Thanks a lot for your reply.


I guess I need to go through this.

How to Link Multiple Tables in Access 2016 - Data to Fish

Suppose you created/imported multiple tables into Access and you want to link them. In this post, I'll show you how to link multiple tables in Access 2016

datatofish.com