Preventing duplicate records for payroll entries

[crxftw]

Hello, I have working payroll generation database which I am trying to improve.

One issue I am trying to avoid is not to allow duplicate records made for employee more than once in a month, so the idea would be to check if one record already exists with same pay date before the submit macro is called. Using Access 2k10, either macro or vba works for me.

Any ideas how it can be done?

Thanks!

 

[MStef]

What is the look of your table ??

 

[crxftw]

Well the payslips table looks like that, there are few more fields that are custom info about the actual payslip and are not important.

Attached, if it helps.

 

[MStef]

Look at attachment (word, zip).
I think it can help you.

 

[sparks80]

Hi,

You could prevent duplicate records from being entered into the table completely by adding a unique index to the employee name and pay date fields.

Open your table in design view and go to indexes.

You appear to have an Autonumber ID field, so leave this as the primary key.

Add a new index name in the next row, then choose the employee name field in the second column. Change the unique property at the bottom of the dialog box to Yes.
In the next line leave the index name blank, and choose the date field in the second column.

An error will now be generated when you try to enter a new record with the same name and date as an existing record.

 

[jdraw]

In your view, what is it that uniquely identifies a record in the table involved?

These are things you could consider:

Make a unique index on those fields. Trap any "error relating to duplicate record" and give a message or take corrective action.
Or do a DCount on the fields (which make record unique) involved in the table and check for > 0 in a Before Update


You may get ideas from
http://office.microsoft.com/en-us/a...-duplicate-records-in-access-HA001034558.aspx

and/or
http://msdn.microsoft.com/en-us/library/aa211376(v=office.11).aspx

OOOoops: I see I was a little slow with the keyboard.

 

[crxftw]

Thanks for all the replies, that was all I needed

 

[gemma-the-husky]

out of interest, is this just something to manage gross pay?

i would not dream of trying to add a paye/ni calculator myself.

 

[crxftw]

Payslips are just part of this database based on jobs that employees have done on sales. Total pay and all calculations are done through 3 different tables so it's rather complex.