Data entry for dates (dates as a relationship)

[Norwich]

Hi,

I wonder if I can pick a few brains...?

I'm writing an access db (form based entry) to capture work against days. For example, a supervisor will pick a day and add work totals for particular memebers of staff.

I started writing this as a database where for each staff member record, there was a related record representing the work (this record would hold the date, description, amount etc). There would only be one of these records per staff member.

Seemed to be appropriate enough until it came to added records via a form. The form would use a calendar control to select all records for that day and display them in a grid for editing.

The problem was if there was no record for that staff member, the form would have to create it. If there was, it would have to edit it and not allow another to be added. All got a bit complicated and tricky to design - and I was wondering if there is a better underlying database structure I could use?

Note: not every staff member will have a record on each day. Pre-creating a table with a field for every day of the year seems OTT.

Any ideas more then welcome.

 

[johnnymac43]

In relation to the Work record, you say 'There would only be one of these records per staff member.' Is this a typo, or what?

Assumptions:
> There is a Work record for each staff member for each date on which work was done by the staff member.
> There is a business requirement that the entry of new Work records and the modification of existing Work records for a date must be done on the same form (If not, then don't do it).

If you have the following database structure:-
> Staff table, containing one record for each staff member;
> Work table, containing one record for each staff member for each day on which work was done by the staff member;
then you can choose whichever of the following scenarios is most appropriate:

> If most staff members have a Work record on most working days, display one line for each staff member for the date selected, with either blank fields or existing data in the Work area. When the form is saved, write all non-blank Work records to the database, overwriting any existing records for the date.

> If most staff members do not have a Work record on most working days, display staff member details only for those with existing Work records for the date selected, and allow the user to add new records by first selecting a staff member who does not yet have a Work record for the date.

HTH

John

 

[Norwich]

>>In relation to the Work record, you say 'There would only be one of these records per staff member.' Is this a typo, or what?

Yup Meant one record per staff member per day (so, one record indicating 5 hours, rather than five seperate records with an hour each... Doh.

Your assumptions are correct, and what I wsa looking to do was your first option - where most staff members will have one record per day. However, I'm no coder, and I couldn't see how to perform that sort of update from the form using the standard controls fronting access queries. Is it possible to perform that sort of update/write sugguested with the standard kit or will I have code it up behind?

Thanks

 

[johnnymac43]

I am sure you will need code segments to populate your form and to save the records after update. I suggest you restate your requirements based on what we have discussed and re-post on the Access Forms forum. Give some thought to a subject heading that will get the attention of the programming gurus.

Cheers

John

 

[Norwich]

Thanks, I will.