How to Link the Fields of my Tables in to a Master Table?

[plog]

Let's forget about the Primary Keys, Foreign Keys, Data Normalization and all those advanced and Professional stuffs,

Let's forget about the cracked foundation, the backed up sewer system, the fire in the attic and can you guys just help me with painting over all this black mold so my living room looks nice? Once that gets done this place is habitable.

Why not just have that form based on the one table you have your heart set on using? Why even use those helper tables?

 

[JithuAccess]

Let's forget about the cracked foundation, the backed up sewer system, the fire in the attic and can you guys just help me with painting over all this black mold so my living room looks nice? Once that gets done this place is habitable.

Please be a Professional

 

[Pat Hartman]

The form picture assumes a 1-1 relationship between all the tables. That is not the case or you wouldn't be getting duplication. Your database doesn't include the forms and the tables are still not normalized.

 

[JithuAccess]

The form picture assumes a 1-1 relationship between all the tables. That is not the case or you wouldn't be getting duplication. Your database doesn't include the forms and the tables are still not normalized.

Thanks. But I am okay with this. My only question was that how to link the sub table fields with Master Table fields. I am not at all bothered about the Normalization and the PK and FK. We are using Access nit as our Main Database.

Could you kindly let me know how to link the fields.

 

[MajP]

You should be able to join your tables, but only those that have a single record per patient. However, according to you that is not true. If you have multiple doctors, multiple correspondence, multiple payments it will not work. Your has no way to show multiple doctors or payments, correspondence, in your form. This is normally done with sub forms.

If I have 10 patients, and each patient has 2 payments a query will create 20 records. If they all have 2 correspondence you will get 40 records. That is likely how you got what seemed like a lot of records.

So the answer is there is no way to do this if you include any tables that have multiple records per patient. If they do not then not sure what the issue is.

 

[JithuAccess]

You should be able to join your tables, but only those that have a single record per patient. However, according to you that is not true. If you have multiple doctors, multiple correspondence, multiple payments it will not work. Your has no way to show multiple doctors or payments, correspondence, in your form. This is normally done with sub forms.

If I have 10 patients, and each patient has 2 payments a query will create 20 records. If they all have 2 correspondence you will get 40 records. That is likely how you got what seemed like a lot of records.

So the answer is there is no way to do this if you include any tables that have multiple records per patient. If they do not then not sure what the issue is.

Thanks a lot I just want to link the sub tables with my Master Tables fields. I am not going to worry about Tables and relations now. Let me start to worry about this in my Next Project.

Many thanks for your response and please accept my apologies for making you confused and drag down your expertise to my level, Sorry

Thanks

 

[Pat Hartman]

Let me start to worry about this in my Next Project.

Kicking the problem into the corner isn't going to solve your problem. You have a mess and no one should ever make any management or financial decision based on it.

The master/child links are what will control the linkage between the main forms and the subforms but this won't work unless your subforms are continuous and show when there are multiple records.

 

[JithuAccess]

Kicking the problem into the corner isn't going to solve your problem. You have a mess and no one should ever make any management or financial decision based on it.

Thanks a lot for your reply.

We are not going to use this Database and it's Tables any more. Once I have done the Data Entry and create the Reports and Charts in Excel, we are going to Delete this Database

Thanks

 

[Pat Hartman]

Good thing

 

[JithuAccess]

Kicking the problem into the corner isn't going to solve your problem. You have a mess and no one should ever make any management or financial decision based on it.

The master/child links are what will control the linkage between the main forms and the subforms but this won't work unless your subforms are continuous and show when there are multiple records.

And I just seek the help from the Seniors from this Forum just to guide me how to link my sub Tables with the Master tables to save my copy paste time and I guess I will do this manually.

Thanks

 

[Pat Hartman]

You came for advice but ignored it. Good luck.

 

[JithuAccess]

You came for advice but ignored it. Good luck.

Apologies if you think I IGNORED the advice.

I just came with a simple question, "How to link the fields of a Table with Another Table" and I got tons of advises and suggestions for the Data Normalization, the use of a PK and FK.

I was looking for a quick, simple and Smart way to simplify the Copying and paste of data from the query result of my Database to Another Table in that same Database. I repeatedly mentioned, I am not using Access for what Access actually using for and I am not at all worried about copying data from one Table to another as we are never going to keep this data in Access, we will delete the Access Database once we have prepared the Reports and Charts . I just need Access as a Temporary method to filter the data and get the data in Excel. Unfortunately, I guess the Seniors of this Forum understand my question incorrectly and I am begging apologies for this.

At the end of the day, I still don't know how to link the Fields from a Table with Another Table in the Same or a Different Database. But, I will find it. Because in the first reply you mentioned "You can do whatever you like in Access".

Thanks a lot Pat Hartman for your reply, and once again, I am sorry if you think I Ignored your Advice.

Have a wonderful Weekend

 

[Isaac]

Smart way

 

[Pat Hartman]

I did tell you how to link the subforms. You use the master/child links. Click on the subform control and look at the properties and you'll see them. You didn't include the forms in the db you posted or I would have fixed them for you or Access would have helped you and set them automatically for you if you had created relationships between the tables using the relationship diagram before building the form. But you can set them manually after the fact.

We still don't know why you want separate tables if everything is in one table even though the data has 1-m and m-m relationships. The one thing we do know that is wrong is keeping the same data in multiple places and we don't have any clue why you want to do that. You have come up with what you think is the solution but you have taken a path that none of us would have suggested because it involves storing one set of data in at least two places and you want us to make your solution work. We are trying to help but we don't have anything to work with.

If you have a spreadsheet as input, you can create append queries that select some columns and append the row to tblA and other queries that select other columns and append them to other tables. So, you could easily take that one flat table and normalize it if that is what you are trying to do.

Is this a one-time effort or are you using Access as a way to get from the data structure of one app to the data structure of a different app?