Greetings,
It appears that an Append Query I run in Access XP is randomly failing
to append a field.
I have payroll data being entered into a payroll database. This data
is exported daily to two csv files. (One for updated data, and the
other for unupdated data.)
The CSV files are attached to my Jobcosting database. After the CSV
files are updated, I use an append query to put that data in a table.
This is my standard billing data. However, if Futa/Suta limits have
not been met on an individual I recoup the cost of the Futa/Suta. So,
I use another append query to append the data in that table to itself
if limits have not been met (as shown in another table.) This is where
the process is breaking down.
Once all data is in the transient table, is use another append query to
append all that data, along with the billing rates in my permanent
Jobcost table.
The problem is that while appending the Futa/Suta data, the append
query fails to post the craft on random records. I can't detect a
pattern. It doesn't appear to be related to the name, employee number,
or craft. It just skips roughly 40% of the crafts. SQL is posted
below.
AppendQueryFutaSuta
INSERT INTO [LABOR IMPORT] ( CATAGORY, [JOB NUMBER], [ACTIVITY NUMBER],
[DAY CODE], [DATE], [WEEK ENDING], SSAN, [EMPLOYEE NUMBER], [EMPLOYEE
NAME], CRAFT, [REG HRS], [OVT HRS], [DBL HRS] )
SELECT DISTINCTROW "FutaSuta" AS Expr1, [LABOR IMPORT].[JOB NUMBER],
[LABOR IMPORT].[ACTIVITY NUMBER], [LABOR IMPORT].[DAY CODE], [LABOR
IMPORT].DATE, [LABOR IMPORT].[WEEK ENDING], [LABOR IMPORT].SSAN, [LABOR
IMPORT].[EMPLOYEE NUMBER], [LABOR IMPORT].[EMPLOYEE NAME], [LABOR
IMPORT].CRAFT, [LABOR IMPORT].[REG HRS], [LABOR IMPORT].[OVT HRS],
[LABOR IMPORT].[DBL HRS]
FROM ([LABOR IMPORT] INNER JOIN WeeklyEmployees ON [LABOR
IMPORT].[EMPLOYEE NUMBER] = WeeklyEmployees.[EMPLOYEE NUMBER]) INNER
JOIN tblJobs ON ([LABOR IMPORT].[ACTIVITY NUMBER] = tblJobs.[ACTIVITY
#]) AND ([LABOR IMPORT].[JOB NUMBER] = tblJobs.[JOB #])
WHERE (((WeeklyEmployees.Futa)=Yes) AND (([LABOR
IMPORT].CATAGORY)="A-LABOR") AND ((tblJobs.[SUTA and FUTA])=Yes));
and the query that appends to my JOBCOST table and adds the rates
according to the craft (which if the craft is null, so is the rate.):
INSERT INTO JOBCOST ( CATAGORY, [JOB NUMBER], [ACTIVITY NUMBER], [DAY
CODE], [DATE], [WEEK ENDING], SSAN, [FIELD 1], [FIELD 2], [FIELD 3],
[COST 1], [COST 2], [COST 3], [REG BILL RATE], [OVT BILL RATE], [DBL
BILL RATE], ADDENDUM, TRANSFER, REVISION, [MONTHLY BILL RATE],
[POSITION CODE] )
SELECT DISTINCTROW [LABOR IMPORT].CATAGORY, [LABOR IMPORT].[JOB
NUMBER], [LABOR IMPORT].[ACTIVITY NUMBER], [LABOR IMPORT].[DAY CODE],
[LABOR IMPORT].DATE, [LABOR IMPORT].[WEEK ENDING], [LABOR IMPORT].SSAN,
[LABOR IMPORT].[EMPLOYEE NUMBER], [LABOR IMPORT].[EMPLOYEE NAME],
[LABOR IMPORT].CRAFT, [LABOR IMPORT].[REG HRS], [LABOR IMPORT].[OVT
HRS], [LABOR IMPORT].[DBL HRS], DLookUp("[COST 1]","rates_" &
[tblJobs]![RATE SCHEDULE],"[FIELD 3] = '" & [CRAFT] & "' AND [RATE
CATAGORY] = '" & [LABOR IMPORT]![CATAGORY] & "'") AS Expr1,
DLookUp("[COST 2]","rates_" & [tblJobs]![RATE SCHEDULE],"[FIELD 3] = '"
& [CRAFT] & "' AND [RATE CATAGORY] = '" & [LABOR IMPORT]![CATAGORY] &
"'") AS Expr2, DLookUp("[COST 3]","rates_" & [tblJobs]![RATE
SCHEDULE],"[FIELD 3] = '" & [CRAFT] & "' AND [RATE CATAGORY] = '" &
[LABOR IMPORT]![CATAGORY] & "'") AS Expr3, No AS Expr4, No AS Expr5, 0
AS Expr6, DLookUp("[COST 4]","rates_" & [tblJobs]![RATE
SCHEDULE],"[FIELD 3] = '" & [CRAFT] & "' AND [RATE CATAGORY] = '" &
[LABOR IMPORT]![CATAGORY] & "'") AS Expr7, DLookUp("[FIELD 2]","rates_"
& [tblJobs]![RATE SCHEDULE],"[FIELD 3] = '" & [CRAFT] & "'") AS Expr8
FROM [LABOR IMPORT] INNER JOIN tblJobs ON ([LABOR IMPORT].[JOB NUMBER]
= tblJobs.[JOB #]) AND ([LABOR IMPORT].[ACTIVITY NUMBER] =
tblJobs.[ACTIVITY #]);
This has to be something simple, but I just can't see it.
Any and all help is much appreciated.
David M. Barger
