Inconsistent results - append query

  • Thread starter Thread starter Chace
  • Start date Start date
C

Chace

I am baffled by this problem. When I execute an append query in vba by the
db.execute strSQL, dbfailonerror command I am only getting 76 out of the
expected 81 records dumped to the destination table. I debug.printed the
strSQL statement and pasted it in the SQL query design window and viewed it
in design view. Looked great. When I run it from there I get all 81 records
as expected! I deleted the records from the destination table and tried
again….same problem. I have done this multiple times, compiled the vba code,
compacted the database multiple times, but problem persists. Any idea what
could be going on?

Thanks,
Chace
 
The vba code is as follows:
db.Execute CurrentDb.QueryDefs("qapnd_OrderandWTOs").SQL, dbFailOnError

The Select Query’s SQL that is used in the above Append query is as follows:
SELECT dbo_so_dtl_tbl.sa_created_by, dbo_so_dtl_tbl.sa_modified_by,
dbo_so_dtl_tbl.so_dtl_key, dbo_so_dtl_tbl.so_dtl_stats,
dbo_so_prod_tbl.im_pack_key, dbo_so_dtl_tbl.so_dtl_cpono,
dbo_so_dtl_tbl.so_hdr_key, dbo_so_dtl_tbl.so_dtl_shpws,
dbo_en_ship_tbl.en_ship_name, dbo_so_dtl_tbl.so_dtl_shsdt,
[dbo_en_item_tbl].[en_item_key] & " " & [dbo_en_item_tbl].[en_item_desc] AS
Item, dbo_en_item_tbl.en_item_key, dbo_en_item_tbl.en_item_desc,
-Sum(IIf([dbo_so_prod_tbl].[im_pack_key]="
",1,[dbo_en_itmpk_tbl].[en_itmpk_filqt])*[so_dtl_ordqt]) AS Amount,
dbo_en_item_tbl.en_class_key
FROM (((dbo_so_dtl_tbl INNER JOIN dbo_so_prod_tbl ON
dbo_so_dtl_tbl.in_prod_key = dbo_so_prod_tbl.so_prod_key) LEFT JOIN
dbo_en_itmpk_tbl ON (dbo_so_prod_tbl.im_pack_key =
dbo_en_itmpk_tbl.im_pack_key) AND (dbo_so_prod_tbl.in_item_key =
dbo_en_itmpk_tbl.en_item_key)) LEFT JOIN dbo_en_item_tbl ON
dbo_so_prod_tbl.in_item_key = dbo_en_item_tbl.en_item_key) LEFT JOIN
dbo_en_ship_tbl ON dbo_so_dtl_tbl.ar_ship_key = dbo_en_ship_tbl.en_ship_key
WHERE (((dbo_so_dtl_tbl.so_dtl_shpdt) Is Null) AND
((dbo_so_dtl_tbl.so_dtl_stats) Not Like 'C*') AND
((dbo_so_dtl_tbl.gl_cmp_key)='SR'))
GROUP BY dbo_so_dtl_tbl.sa_created_by, dbo_so_dtl_tbl.sa_modified_by,
dbo_so_dtl_tbl.so_dtl_key, dbo_so_dtl_tbl.so_dtl_stats,
dbo_so_prod_tbl.im_pack_key, dbo_so_dtl_tbl.so_dtl_cpono,
dbo_so_dtl_tbl.so_hdr_key, dbo_so_dtl_tbl.so_dtl_shpws,
dbo_en_ship_tbl.en_ship_name, dbo_so_dtl_tbl.so_dtl_shsdt,
[dbo_en_item_tbl].[en_item_key] & " " & [dbo_en_item_tbl].[en_item_desc],
dbo_en_item_tbl.en_item_key, dbo_en_item_tbl.en_item_desc,
dbo_en_item_tbl.en_class_key;

I did find that by adding a criteria for the en_class_key solved the
problem, but I still don’t know why I was getting the inconsistent results to
begin with. So for now I seem to have resolved the problem, but if you have
any insight into the root of the issue I would appreciate it.

Thanks,
Chace
 
Back
Top