Append and Update criteria

[gheaney]

Hi All, hope someone can help.



I have a number of tables that hold staff names and hours worked. These are then appended to the MASTER table, however a staff member can work in multiple areas and therefore can appear in the MASTER table multiple times. I now need to get the data into a format for import to the payroll system(Lets call table PAYROLLIMPORT), this I can do however where I have a staff member entered multiple times i need to enter their (first set) of hours in the PAYROLLIMPORT.basic field and their (second set) of hours PAYROLLIMPORT.basic2 field and so on.
I thought I should do an initial append and then follow with an update however the first value is used onthe update so i need to find someway of excluding the first value for the second update query and so on. Any assistance greatly appreciated.

[orange]

It would appear that your tables are not normalized. It will make programming and processing more difficult because you have to work around structure shortcomings.

a staff member can work in multiple areas and therefore can appear in the MASTER table multiple times.

what is a Master table that has replicated data?

Can you provide a jpg of your tables and relationships?

[gheaney]

Hi Orange tks for reply.

Im using access to manipulate data not as a Database per se. I have a number of Rosters (10) covering different areas held on excel spreadsheets, these rosters calculate totals for basic, night hrs, sun, etc the totals page is imported into Access 10 in all, I then append the 10 to the MASTER table hence multiple staff names, reason not normalised. I then need to get the data into a format for uploading into a PAYROLL system however I can only import each staff memeber once.
If Staff A has worked in their main centre then hrs go to field BASIC if they have worked in an additional cost centre then their second entry on the table for hrs should go to BASIC2 in the payrollimport table. Problem is I have set up an append to get first entry and really need advice on the next step as in how to I get the second and subsequent entries to go to Basic2 where BASIC already has an entry.
Hope that is clearer

[orange]

Do you have a table consisting of fields

Staff and mainCostCentre

where the Staff is only identified once? Seems this would be a control type table.

Any HrsWorked info involving staff and cost centre could be compared to such a table. If the HrsWorked were against that Cost centre then that goes to BASIC, any thing else goes to BASIC2.

At least that's what I'm getting from your info.
Hope it helps.

[gheaney]

I have staff/costcentre info as a hidden sheet in the rosters (excel sheet) using a vlookup to ensure consistancy, easy enough to get that as a constant in access. I wil have a look at using it and doing comparassion. thanks

[orange]

I think some where in your processes you have to determine what you are using Excel for and what you are using Access for.
Ideally you'll have a model of that part of your environment for cleansing/organizing data that you're sending to Payroll. You need some consistent and repeatable processes to make sure PayCheck is accuarte back to time sheets etc.

You can have an Authoritative table for Staff and HomeCostCentre , but you must maintain it, and copy it when needed to the other environment (Access->Excel or Excel->Access) But you have to be absolutely consistent.

Seems to me you are dealing with HoursWorkedTransactions.

Any/Every Staff who does HoursWorked has the info linked to a CostCentre.
So you can tell if it's the HomeCostCentre or some other CostCentre.

How involved is the Payroll system to the HoursWorked transactions? Why is some stuff in Excel and some in Access, and where (what platform) is the payroll system?

[gheaney]

I currently use excel for the construction of Rosters in 10 different locations. These work of 'planned; Rotas with exceptions entered as and when they arise e.g. sickness, annual leave. The exceptions are trapped and fed to the Final Top Sheet which displays basic hours worked, sickness, a/l, Sun hrs, Sat hrs twilight hrs.......Thats all good to this point.

I then need a flat sheet to export to the Payrole (Quantum).However a staff member could appear on more than one roster (more than one cost code) I need to grab all that data together and consolidate, that is currently doen via access, I import flat data from excel and combine all the data into one table (not normalised), Now I have a table with some multiple entries for some staff i could import the data at this point if i didn't have staff more than once, so i need to create a table with Staff name once but with a new field of BASIC2 which will take data (hrs) from the second entry for a staff memeber where it is not their primary cost centre, I get the BASE table idea, just struggling as to how to put it all together, havn't looked at it since Fri (child care) so wil get back on it tonight/tomorrow
Thanks