Adding records via a form

[Neil_Pattison]

I have set up an attendance database with tables for each month. Each of these tables has its corresponding form. I want to be able to add a command button onto the forms to add a new record. Obviously this is easily done using the wizard but my problem is I want it to be added over a number of tables.

For instance when a new member of staff arrives and is added to the database I want there record to be added over all the subsequent tables not just the one attached to the current form. It would also be beneficial if I could do the same for deleting staff (ie someone leaves they are deleted off the current table and all other tables)

Is there a way of doing this.

Any help would be greatly appreciated

 

[Ashfaque]

Hi Neil,

Try using Append Queris with conditional criteria. They are great for these kind of jobs. Similarly You can also delete the records thru Delete queries.

Regards,

Ashfaque

 

[Sergeant]

Is there a way of doing this.

Yes...don't have a separate table for each month.
There is no way that you should have staff members loaded in different tables. You have a staff table with a unique ID for each, and you have an attendance table with a staff ID in each row that you add.

This is one of those reasons why planning your table structure is so important...makes things easy later on.

 

[Neil_Pattison]

I understand where your coming from sergeant. I need to be searching on a daily, weekly and monthly basis so are you saying I should have a field for every day of the year in the same table, I am wanting to run this until the end of 2007 so that would mean a lot of fields in one table

 

[Sergeant]

How about this...

You don't need to have a field for every day. I think you have a spreadsheet idea in your head, and Access isn't a spreadsheet. You don't need an "X" for every day that someone showed up...
You can track attendance by exception, using absence.

tblAbsence
- abID (Autonumber, PK)
- StaffID (Foreign Key on tblStaff)
- abStartDt
- abEndDt
- abType

If someone is (or was) absent for a period, they get an entry (a row, or record) in the absence table. You can keep putting in entries until the year 2525 and beyond...without changing the db structure. You could delete or archive your old info after it has passed, if you want.

Now, I'm not sure what you need to derive from this database, but I am confident that this storage method will serve you far better than having a table for each month.

What d'ya think?

Sarge.

 

[Neil_Pattison]

I need to be able to monitor when staff are On Site, when they are on holiday, or sick or in training and a number of other criteria. The thing is due to the high amount of staff here and the nature of the job there will be an absence of some type or another every single day

 

[Sergeant]

So be it. Let there be a row for each absence in the absence table.
You don't need a row for each day...you need a row for each definable absence. You will find that you can do all sorts of things with the data when it is in the right format.
With your current method, how do you show a one-week span that covers two "month" tables? (Like this week)

It's your thing...but if you want to stick with what you've got, you might as well use a spreadsheet. At least that will be graphical in nature without any manipulation.

 

[jebi_babu]

Sergeant you are right, this is bcoz of the excel mania(spread sheet mania) in their head. Come out of it, learn basics of access, and start after that. that is the best solution.