473,388 Members | 870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Append Query Random Failure

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

Nov 13 '05 #1
1 2454

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

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: M. Clift | last post by:
Hi All, I have tried to come up with a way to do this myself and all I end up with is very long code. What I have is a say item1, item4, item2, item1 etc... What I want to do is append to...
1
by: Randy K | last post by:
I have a table with some 35000 records and I need some help sorting it out. The goal is to get counts of failures modes oraganized by serial number. the table is set up roughly like this. s/n ...
2
by: Danny | last post by:
I want to extract a subset of fields from one table into another the master table has many fields the subset has about half, but still many. Is there a way I can just append the master into the...
1
by: Aaron | last post by:
Hello fellow programmers, I am trying to run an append/update query from code, a command button on a form initiates the queries. the format i am using is; ...
1
by: Kurt | last post by:
For some reason I cannot use an append query to append records to a design master replication table that has the index key on a random Long Integer. I checked all the text fields in the Master...
3
by: ielamrani | last post by:
Hi, An append query is giving me the following error: qryPar can't append all the records in the append query qryPar set 0 field(s) to null due to at type conversion failure, and it did not add...
2
by: durgapur | last post by:
hi, i am trying to round off the value of a text field and then append it to a decimal field, but it's giving type conversion failure, below is the query plz help :- IIf(Len(CDbl())<8 And...
3
by: Francogrex | last post by:
Hello, I am new to C++, have some knowledge of programming in splus(statistics). I am trying to append values output by a loop (code below) into a vector (by appending value), that I can eventually...
1
by: MLH | last post by:
Anyone remember if A97 append query failure would ever report data breaking validation rule when such was not the case. I have an old SQL statement - several years old now. I've encountered a case...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.