Updating content to form from multiple tables

[jsparker]

I am a novice Access user but have been tasked to create a form that links to a number of different tables and updates fields on the form from those numerous tables.

At the top of my form is employee information: username, full name and week ending.

Next section of the form has time worked on specific software platforms. Multiple spaces are to be linked to multiple tables with the necessary info.

So what I'm trying to do is when the user enters the userid of the employee it pulls from a table the full name and updates it on the Name field. Then the user enters a week ending date. Once that is entered Access searchs the available tables with the week endings to the time worked for each specific software platform and updates those fields from the tables. Each software platform table has week ending columns along with other information that needs to be updated to the form.

Form and Table names:

Form name: frmReport Card
Table names: tblDC Associates, tblWeek Ending, tblsoftware1, tblsoftware2, tblsoftware3

I apologize if this is elementary and I'm just missing it but would appreciate any/all guidance. Please post or email me directly at jsparker@dsthealthsolutions.com

Thanks.
Joshua

 

[boblarson]

Okay, I know you might not be responsible for the design of this but I have to mention it anyay. This database sounds like it needs to be normalized. Table names like tblSoftware1, tblSoftware2, etc. (even if that isn't their actual name) sound like it really needs to be normalized first.

Read here for more about normalization:
http://support.microsoft.com/kb/283878

Also, you can put multiple tables on a single form by using subforms (and I usually will separate them by using a tab control). Then you link the subform containers' Master/Child links on the common id field.

 

[ajetrumpet]

I like the picture, Bob.

I am still not convinced that normalization is all the popular, but I bet it would be if people didn't use Access for structures that it doesn't support....right?

 

[jsparker]

Ok so first things first normalization. I'll have to fight that battle. Considering I understand that to mean creating more tables from one source.

Next thing you are saying that for my form to pull from multiple tables it must have multiple sub-forms on it? One form can not pull information from multiple tables? Again this is probably me showing my Access limitations but I had envisioned one consolidated form pulling from a number of tables updating fields, user enters in their hardcoded metrics and then a command button to calculate it all.

If I understand you correctly you are saying for each table I pull information I have to have a subform? I'm pulling metrics from 6+ different sources.

Any direction or light you can shed would be greatly appreciated.

Okay, I know you might not be responsible for the design of this but I have to mention it anyay. This database sounds like it needs to be normalized. Table names like tblSoftware1, tblSoftware2, etc. (even if that isn't their actual name) sound like it really needs to be normalized first.

Read here for more about normalization:
http://support.microsoft.com/kb/283878

Also, you can put multiple tables on a single form by using subforms (and I usually will separate them by using a tab control). Then you link the subform containers' Master/Child links on the common id field.