Append Query is all or nothing

D

DamonECii

Hello Everybody,

I'm using A97 on W2K and having problems with the results of my query.
Bringing in ALL records from the tbl_ImpRawFile with no WHERE works
great, but when the criteria IS NULL to avoid duplicates, no records
are appended.

INSERT INTO tbl_Fuel_Trans_ALL ( TransDate, SiteNum, TransNum,
TransTime, Employee, VehEquipNum, Odom_Hrs, PumpNum, ProdNum, Gallons)

SELECT Mid([TransDate],5,2) & "/" & Right([TransDate],2) & "/" &
Left([TransDate],4) AS Expr2, tbl_ImpRawFile.SiteNum,
tbl_ImpRawFile.TransNum, tbl_ImpRawFile.TransTime,
tbl_ImpEmployeeFile.Field2, tbl_ImpRawFile.VehEquipNum,
tbl_ImpRawFile.Odom_Hrs, tbl_ImpRawFile.PumpNum,
tbl_ImpRawFile.ProdNum, [Gallons]/1000 AS Expr1

FROM tbl_ImpRawFile LEFT JOIN tbl_ImpEmployeeFile ON
tbl_ImpRawFile.Employee_ID = tbl_ImpEmployeeFile.Field1
ORDER BY tbl_ImpRawFile.SiteNum, tbl_ImpRawFile.TransNum;

WHERE (((tbl_Fuel_Trans_ALL.TransDate) Is Null) AND
((tbl_Fuel_Trans_ALL.SiteNum) Is Null) AND
((tbl_Fuel_Trans_ALL.TransNum) Is Null) AND
((tbl_Fuel_Trans_ALL.TransTime) Is Null))

ORDER BY tbl_ImpRawFile.SiteNum, tbl_ImpRawFile.TransNum;

The background for this task involves a text file that's generated by
a fuel station data concentrator. After importing the data wanted I
then append the 'new' records to the tbl_Fuel_Trans_ALL table. In the
process I reformat the TransDate and Gallons fields.

Thanks in Advance.
 
J

John Vinson

SELECT Mid([TransDate],5,2) & "/" & Right([TransDate],2) & "/" &
Left([TransDate],4) AS Expr2,
Click to expand...

If TransDate is a date, or you're trying to import into a date, you
might want to use DateSerial instead:

DateSerial(Left(TransDate, 4), Mid(TransDate, 5, 2), Right(TransDate,
2))

will return a Date/Time value without the extra conversion.
tbl_ImpRawFile.SiteNum,
tbl_ImpRawFile.TransNum, tbl_ImpRawFile.TransTime,
tbl_ImpEmployeeFile.Field2, tbl_ImpRawFile.VehEquipNum,
tbl_ImpRawFile.Odom_Hrs, tbl_ImpRawFile.PumpNum,
tbl_ImpRawFile.ProdNum, [Gallons]/1000 AS Expr1

FROM tbl_ImpRawFile LEFT JOIN tbl_ImpEmployeeFile ON
tbl_ImpRawFile.Employee_ID = tbl_ImpEmployeeFile.Field1
ORDER BY tbl_ImpRawFile.SiteNum, tbl_ImpRawFile.TransNum;
Click to expand...

There's an extra semicolon there which terminates the query prior to
any of your WHERE clause - and in addition, you're left joining
tbl_ImpEmployeeFile instead of what I expect you intended to join,
tblFuelTransAll. If you join by these four fields you would only need
to check any one of them rather than all four.
WHERE (((tbl_Fuel_Trans_ALL.TransDate) Is Null) AND
((tbl_Fuel_Trans_ALL.SiteNum) Is Null) AND
((tbl_Fuel_Trans_ALL.TransNum) Is Null) AND
((tbl_Fuel_Trans_ALL.TransTime) Is Null))

ORDER BY tbl_ImpRawFile.SiteNum, tbl_ImpRawFile.TransNum;
Click to expand...

John W. Vinson[MVP]
 
D

DamonECii

SELECT Mid([TransDate],5,2) & "/" & Right([TransDate],2) & "/" &
Left([TransDate],4) AS Expr2,
Click to expand...

If TransDate is a date, or you're trying to import into a date, you
might want to use DateSerial instead:

DateSerial(Left(TransDate, 4), Mid(TransDate, 5, 2), Right(TransDate,
2))

will return a Date/Time value without the extra conversion.
tbl_ImpRawFile.SiteNum,
tbl_ImpRawFile.TransNum, tbl_ImpRawFile.TransTime,
tbl_ImpEmployeeFile.Field2, tbl_ImpRawFile.VehEquipNum,
tbl_ImpRawFile.Odom_Hrs, tbl_ImpRawFile.PumpNum,
tbl_ImpRawFile.ProdNum, [Gallons]/1000 AS Expr1

FROM tbl_ImpRawFile LEFT JOIN tbl_ImpEmployeeFile ON
tbl_ImpRawFile.Employee_ID = tbl_ImpEmployeeFile.Field1
ORDER BY tbl_ImpRawFile.SiteNum, tbl_ImpRawFile.TransNum;
Click to expand...

There's an extra semicolon there which terminates the query prior to
any of your WHERE clause

- and in addition, you're left joining tbl_ImpEmployeeFile instead of what
I expect you intended to join,tblFuelTransAll. If you join by these four
fields you would only need to check any one of them rather than all four.
WHERE (((tbl_Fuel_Trans_ALL.TransDate) Is Null) AND
((tbl_Fuel_Trans_ALL.SiteNum) Is Null) AND
((tbl_Fuel_Trans_ALL.TransNum) Is Null) AND
((tbl_Fuel_Trans_ALL.TransTime) Is Null))

ORDER BY tbl_ImpRawFile.SiteNum, tbl_ImpRawFile.TransNum;
Click to expand...

John W. Vinson[MVP]
Click to expand...

John, Thanks for your response.

Using DateSerial worked great.

When I removed the simicolon A97 put is back in. Could this be caused
by working in the Design View?

Regarding the join's. The tbl_ImpEmployeeFile allows me to put a name
to the number retrieved from the employee ID badge. I can drop this
all together; it's not really needed.

When I try to join all four fields I get a type mismatch on the date
fields. My thought now is "Can I convert the text data during the
import process?"

Also, when I join on three remaining fields I'm getting the same
results; all or nothing.

Again, Thanks In Advance. . .
 
J

John Vinson

When I removed the simicolon A97 put is back in. Could this be caused
by working in the Design View?

Regarding the join's. The tbl_ImpEmployeeFile allows me to put a name
to the number retrieved from the employee ID badge. I can drop this
all together; it's not really needed.

When I try to join all four fields I get a type mismatch on the date
fields. My thought now is "Can I convert the text data during the
import process?"

Also, when I join on three remaining fields I'm getting the same
results; all or nothing.
Click to expand...

I'm very perplexed. Please open your Query in SQL view and copy and
paste the entire SQL text here. If you have a Date/Time field in one
table, and this text date in another, then of course they won't match;
do you? You'll need to do the conversion on import, indeed. And if the
"all or nothing" is due to differences in the date, then you're
getting exactly the results I'd expect!

John W. Vinson[MVP]
 
D

DamonECii

I'm very perplexed. Please open your Query in SQL view and copy and
paste the entire SQL text here. If you have a Date/Time field in one
table, and this text date in another, then of course they won't match;
do you? You'll need to do the conversion on import, indeed. And if the
"all or nothing" is due to differences in the date, then you're
getting exactly the results I'd expect!

John W. Vinson[MVP]
Click to expand...

Here ya go:

INSERT INTO tbl_Fuel_Transactions_ALL ( TransDate, SiteNum,
TransNum, TransTime, Employee, VehEquipNum, Odom_Hrs, PumpNum,
ProdNum, Gallons)
SELECT
DateSerial(Left(TransDate,4),Mid(TransDate,5,2),Right(TransDate,2)) AS
Expr2, tbl_ImpRawtransFile.SiteNum, tbl_ImpRawtransFile.TransNum,
tbl_ImpRawtransFile.TransTime, tbl_ImpEmployeeFile.Field2,
tbl_ImpRawtransFile.VehEquipNum, tbl_ImpRawtransFile.Odom_Hrs,
tbl_ImpRawtransFile.PumpNum, tbl_ImpRawtransFile.ProdNum,
[Gallons]/1000 AS Expr1
FROM tbl_ImpRawtransFile INNER JOIN tbl_ImpEmployeeFile ON
tbl_ImpRawtransFile.Employee_ID = tbl_ImpEmployeeFile.Field1
ORDER BY tbl_ImpRawtransFile.SiteNum, tbl_ImpRawtransFile.TransNum;


John, I got things working by making the primary index in
tbl_Fuel_Transactions_ALL the first 4 fields. Ignoring the erros the
query will only append the new records; not making duplicates.

Can I do things the hard way or what . . ? Damon
 
Top