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.

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.

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?

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.

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.

Sometimes you just have to do what you can!