Append Query updates all fields except one

[yamalady]

I am using an append query to update many fields. It is working fine with all fields except one. It is a price that will not append. I have made sure all the formatting matches, but it still will not update. And since all my other data is appending, I am assuming my joins are correct. I am at a loss at what else I should be looking at. I have made sure that currency with 2 digits is being used for all my tables. Any ideas? Suggestions? Help!!

[Bulzie]

Post the append query SQL code. Also give example of data you want to append and the datatype of that field? Is that field in the same table as the other fields you are appending?

[CJ_London]

not much to go on except a general response

I have made sure all the formatting matches

your problem could be here - it is not formatting that matters - it is the datatype.

I am assuming my joins are correct

does this mean you are trying to append to several tables at the same time?

What happens if you convert the query to a select query? does it provide a value for price?

what method are you using to run the query?

if running via vba have you turned error checking off until this is resolved?

[yamalady]

I hope this answers your questions above....

Here is the SQL code for my append query.

INSERT INTO [TblMaster Data] ( CoCd, [Cust Node], [Profit Center], [Distr Channel], Product, Commodity, Class, [Customer Group], [Shipping Method], Plant, Price, [Jan Qty], [Feb Qty], [Mar Qty], [Apr Qty], [May Qty], [Jun Qty], [Jul Qty], [Aug Qty], [Sep Qty], [Oct Qty], [Nov Qty], [Dec Qty], [CY_A-F_Total Qty] )
SELECT [TblMaster Data-Monitoring].CoCd, [TblMaster Data-Monitoring].[Cust Node], [TblMaster Data-Monitoring].[Profit Center], [TblMaster Data-Monitoring].[Distr Channel], [TblMaster Data-Monitoring].Product, [TblMaster Data-Monitoring].Commodity, [TblMaster Data-Monitoring].Class, [TblMaster Data-Monitoring].[Customer Group], [TblMaster Data-Monitoring].[Shipping Method], [TblMaster Data-Monitoring].Plant, [TblMaster Data-Monitoring].Price, [TblMaster Data-Monitoring].Jan, [TblMaster Data-Monitoring].Feb, [TblMaster Data-Monitoring].Mar, [TblMaster Data-Monitoring].Apr, [TblMaster Data-Monitoring].May, [TblMaster Data-Monitoring].Jun, [TblMaster Data-Monitoring].Jul, [TblMaster Data-Monitoring].Aug, [TblMaster Data-Monitoring].Sep, [TblMaster Data-Monitoring].Oct, [TblMaster Data-Monitoring].Nov, [TblMaster Data-Monitoring].Dec, [TblMaster Data-Monitoring].[CY_A-F_Total]
FROM [TblMaster Data-Monitoring];

The Data Type on TblMaster Data-Monitoring is C Currency, 2 Decimals, Default Value 0, Required No, Indexed No, Text Align General
The Data Type on TblMaster Data is also Currency, 2 Decimals, Default Value 0, Required No, Indexed No, Text Align General

This is just a small portion of the data I need to append to TblMaster Data-Monitoring:

Product Commodity Class Customer group Shipping Method Plant Price Jan Qty Feb Qty Mar Qty Item A 19051510 AF Accessories 190515 Aluminum Fencing 1 D Direct 8300 $0.74 0 0 0 Item B 19051510 AF Accessories 190515 Aluminum Fencing 1 D Direct 8300 $0.00 0 0 1 Item C 19051510 AF Accessories 190515 Aluminum Fencing 1 D Direct 8300 $0.00 0 1 0 Item D 19051510 AF Accessories 190515 Aluminum Fencing 1 D Direct 8300 $0.00 0 0 0
This is the result:

Product	Commodity	Class	Customer group	Shipping Method	Plant	Price	Jan Qty	Feb Qty	Mar Qty
Item A	19051510 AF Accessories	190515 Aluminum Fencing	1	D Direct	8300		0	0	0
Item B	19051510 AF Accessories	190515 Aluminum Fencing	1	D Direct	8300		0	0	1
Item C	19051510 AF Accessories	190515 Aluminum Fencing	1	D Direct	8300		0	1	0
Item D	19051510 AF Accessories	190515 Aluminum Fencing	1	D Direct	8300		0	0	0

[CJ_London]

I hope this answers your questions above....

not these

What happens if you convert the query to a select query? does it provide a value for price?

what method are you using to run the query?

if running via vba have you turned error checking off until this is resolved?

also you mentioned about joins - I don't see any

Clearly the data is not normalised, but that is unlikely to be the reason in this case

[Bulzie]

I don't think there are joins, just a straight append from 1st to 2nd table. All the fields should be the same names and datatypes in both tables. Can you create a test table as the 2nd table and maybe change the datatype of Price in the 2nd table to Text or numeric to see if it appends anything in there at all? You can put a criteria to just append 1 record to test with. Also test it by hardcoding a number in there to append, see if it takes it.