Append Query Random Failure

[David Barger]

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

[David Barger]

Please disregard my previous question.

As it turns out, the problem is not with my import routine. The
problem lies with my manual input form (a problem I knew about and
solved earlier today.) Unfortunately, the time keeper from the jobsite
in question not only imported some of the time, she also input some of
it manually. Only supervision is routinely input manually, so when I
saw nulls on non-supervision personnel I naturally assumed that an
error was occurring in the import scheme.

I have spent all bloody day looking at those two queries trying to
figure out where it could be going wrong. It would have been nice if
she had mentioned that she hadn't imported all of the records!

My apologies for any inconveniences caused by my question.
Many thank for your consideration.

David M. Barger