Relational database - Is it possible?

[Chalkie42]

Hi folks, I'm new here - pretty hot at Excel but a total newbie at Access, So..

OK, I've got my database up and running. It keeps records of about 500 staff - everything is working fine but I now want to do stuff beyond my capabilities.. I've got a master record (Master Staff List) and a main input form (Data entry/amandment form). One of the input controls (via a sub-form) is for recording the time staff have spent working at a higher grade. Now if this was a one-off occurrence I could handle it but any one person could have multiple occasions that need recorded and I don't want to 'overwrite' the previous record. I have tried researching the problem and I think I need a 'one-to-many' relational table and a way to add a new period from the subform when required. The subform would also need to show blank when I opened it.

Any help would be appreciated.

 

[neileg]

Sounds like you're on the right lines.

If you create your subform and drag it onto the main form, the subform wizard will connect the parent/child links for you. You need to hold the primary key field from the main table as a foreign key in the subtable to link the two together. If your primary key is an autonumber in the main table, set the foreign key field to be a number field and with the same name. This prompts the wizard to get the link right.

Not sure why you want the subform to oepn blank. I would have expected it to show previous records if any. However, if that's what you want, set the subform property for Data Entry to Yes.

 

[Chalkie42]

Thanks Neil,
It all works fine except that when I input new dates for a record (staff member) it overwrites the previous ones. I need to add a new line to the table with the same unique ID but a new set of dates.

Any thoughts?

 

[neileg]

Have you set the data entry property to yes? This will prevent existing records being overwritten.

Failing that, post your database and I'll have a look.

 

[Chalkie42]

Neil,

Thanks for your help. A lot of messing around but I have managed to get a result by showing the subform as a continuious form. I'm willing to live with that.

Cheers.

 

[neileg]

Sometimes you just have to do what you can!