Relationship between tables ?

i'm a bit confused because this form will now be based on 5 tables. Would i be right in saying that i should base the main form on tblStaff and then use 4 subforms to get the data into the other tables ?

There are a couple of ways you could approach it, depending on how you want to view/enter the data. You could have a main form based on tblStaff, with a sub form based on tblTrainingRecords. This sub form would likely have a sub form of it's own based on tblTrainedSOPData. The other two tables (tblJobs and tblSOPList) would not be directly involved here. They would simply provide the data for combo boxes. With this form you would select a Staff member, and then view/enter the Training related to that Staff member.

Or, you could have a main form based on tblJobs, with a sub form based on tblTrainingRecords. Again, this sub form would have a sub form of it's own based on tblTrainedSOPData. With this form you would select a Job, and then view/enter the Staff members that have been/are being trained for that Job.
 
Emma

Did you look at the attachment in post #17?
 
Cheers Beetle....i think part of my problem is that i'm still not 100% confident setting up relationships properly...even though i have read up on them several times. I understand what you're saying about setting up the form and i think i'm a good part of the way there but there's still something i'm not doing right.
When i select an employee's name fromn the combo box i thought that their employee number would automatically appear in the text box next to it ?. Also, when setting up the combo box, do i need to tell it to save the selection to tblStaff ? I've attached the latest version.

RainLover....yes i did download your example database...thank you. I just couldn't seem to transfer the ideas to the file i'm currently working on. I'm still a bit thick when it comes to Access so please forgive me coming back with these probably obvious questions

Thanks guys
 

Attachments

I got a copy of 2007 to look at your design,

Please tell me even though you may have answered this before.

What is the name of the form you want to display your data.

And which Table or Tables has that data. With those tables could you make sure that they have at least 5 rows of data.
 
I thought i'd converted it to the 2003 version ?....i can't even do that right:rolleyes:

I'm looking to use frmAddNewRecord to input employee training details. I didn't put any records in there because i'm not sure i have everything right yet. tblStaff contains a list of employees and their employee numbers and tblTrainingRecords will contain most of the training information.

There's also a tblTrainedSOPData which is supposed to contain records of SOP's (Standard Operating Procedures) which are associated with each job....at least i think that's what Beetle intended

Thanks
 
The 2003 2007 bit is most likely because of this lap top I borrowed.

Do you think you could spend 5 minutes adding some records.

Back tomorrow.
 
When i select an employee's name fromn the combo box i thought that their employee number would automatically appear in the text box next to it ?. Also, when setting up the combo box, do i need to tell it to save the selection to tblStaff ? I've attached the latest version.

When using a combo box in this manner (i.e. searching for a record), it's important that the combo box be unbound (not bound to any field in its Control Source), and you just use a bit of code in the After Update event of the combo box to find the appropriate record. You can do this via the wizard if you're not comfortable writing the code yourself (when you add a combo box to a form the wizard should come up and you can use the option to "find a record on my form..."). In the case of the sample file that I am posting back up here, I just wrote the bit of code for it. If you leave a combo box like this bound to a field in the underlying record source, you're actually overwriting existing data when you make a new selection in the combo box, and that's not what you want.

Also, I recommend that you store First and Last Names separately in the table. You can always concatenate them together where needed (like in the Row Source query of a combo box). In addition to that, I thought from earlier posts in this thread you indicated that each training record could have multiple SOP's. If that's the case, it seems like you would want your SOPList sub form to be continuous so you can see multiple records at once.

Anyway, I made a few minor changes. Take a look at the modified sample and see if this is similar to what you are trying to achieve. When you open frmAddNewRecord you won't see any training records at first (because you have the training sub form coded to open to a new record) but if you use the back button (the one you created for the sub form) you should see two training records that I added under the name Holland.
 

Attachments

Cheers Beetle that looks exactly like what i'm trying to do. I tried creating the query again (before i say thanks and then realise i'm still in trouble) and it seems to be giving double results. The employee you used 'Holland', is appearing 4 times in the query when he only has two training records to his name ?

RainLover...sorry i couldn't enter any records because i was having trouble with the combo box

Em
 

Attachments

The employee you used 'Holland', is appearing 4 times in the query when he only has two training records to his name ?

Only two records in the Training table, but those records each have related records in the SOPData table (a total of 4), which you have included in your query, hence, "Holland" appears 4 times in the query. That's not necessarily a problem though. If the purpose of the query is to be the basis for a report, then this will be resolved in the report via Grouping and Sorting.
 
Thanks again Beetle....the grouping and sorting in the report sorted everything out. It's looking good now....just need to do some formatting and make it look more presentable.

I really appreciate both yourself and RainLover giving up your time to help me....you guys are the best

Cheers
Em xx

(if i'm back in a couple of days don't be surprised :))
 

Users who are viewing this thread

Back
Top Bottom