INSERT query does not insert all data in query

[CJ_London]

The query returns different results when in data-sheet view, and actually inserting the result set.

when you view an insert query in 'datasheet view' then you are effectively viewing a select query - the update action 'happens afterwards'

you haven't said what message you get when the update query is run - I'm assuming you are running it manually and not via vba. You should see something like this

As I asked originally and Gina has now asked for - what are your destination table fields and datatypes? Implication is that your field called PreUpdateConsumerPrice either does not exist or is not a double number type

 

[marlan]

Thanks all for replying!

you haven't said what message you get when the update query is run

I get no message, not when running in VBA, and not when testing it manually. Would there be a way I'm blocking these messages?

As I asked originally and Gina has now asked for - what are your destination table fields and datatypes? Implication is that your field called PreUpdateConsumerPrice either does not exist or is not a double number type

• code - Double (?! should be Long. This is how the Create Table query created it...)
• PreUpdateConsumerPrice - Double (the first thing I checked when found this problem)
• MinPriceForUpdat - Double
• ManualAvoidUpdate - Boolean
• upd - Integer (I use it as boolean)
• WholesalerPrice - Double
• UpdatedWholesalerPrice - Double
• CategoryID - Long

Gina - CategoryD is a typo... thanks

 

[arnelgp]

have you tried pasting the sql query in design view.

 

[marlan]

Hi, back from holiday,

Again, the SQL is fine, it gives me fine results in datasheet view. The problem appears when data is inserted to the table.

The difference between results in datasheet view and inserted table are presented in post #14

 

[GinaWhipp]

What is the Data Type of consumerPriceWithVat in Products? Is Products a table?

 

[marlan]

What is the Data Type of consumerPriceWithVat in Products? Is Products a table?

Products is a table, consumerPriceWithVat is a Double.

 

[spikepl]

Using Double to hold money amounts, instead of Currency, makes you much more liable to rounding effects and hence to penny-errors, where fx a sum differs from desired result by a single penny or two.

 

[marlan]

Thanks spikepl for replying!

Using Double to hold money amounts, instead of Currency, makes you much more liable to rounding effects and hence to penny-errors, where fx a sum differs from desired result by a single penny or two.

I rarely store products costing a few 1000 NIS, most products cost less than 100. I havn't noticed any rounding effects problems in the past few years the system is working. is this relevant information, or should I expect penny errors?...

In any case, the result of the INSERT query is of of no data in this field. Could you please add your insight? most of the information in posts #3, #14 and #22.

Have you ever seen such a case?

TIA

 

[Minty]

Please post up the actual SQL of the INSERT query completely.
It will be different from the SELECT query you run to trial the data.
We still haven't seen it despite asking for it.

 

[marlan]

Please post up the actual SQL of the INSERT query completely.
It will be different from the SELECT query you run to trial the data.
We still haven't seen it despite asking for it.

Hi,
This was posted in #3:

INSERT INTO PricesForUpdate ( code, PreUpdateConsumerPrice, MinPriceForUpdat, ManualAvoidUpdate, Upd, WholesalerPrice, UpdatedWholesalerPrice, CategoryID )
SELECT Products.code, Products.consumerPriceWithVat AS PreUpdateConsumerPrice, FC.MinValForUpdate AS MinPriceForUpdat, Products.AvoidUpdate AS ManualAvoidUpdate, Nz([consumerPriceWithVat],[MinValForUpdate]+1)>[MinValForUpdate] And Not [AvoidUpdateEuro] AS Upd, Products.WholesalerPrice, IIf([Upd],[WholesalerPrice]*[FC].[UpdateRate],[WholesalerPrice]) AS PostUpdatePrice, Products.CategoryID
FROM Products, FC

It is one SQL: INSERT INTO TableName (Fields... ) SELECT(Fields...) ...

The data pasted in post #14 (here is the data)is not of a trial SQL, but the datasheet view of the actual Insert SQL.

Or do I misunderstand you?

 

[spikepl]

You are expending a lot of effort on something simple. Use data in your insert query to make a table instead. Select it and copy it to excel: so simple.

Then insert(append) from that table into you target table. Inspect the records not inserted. Insert them by hand one by one and see where the system complains.

 

[Minty]

Hi CJ,
I'll mock the SQL:

INSERT INTO PricesForUpdate ( code, PreUpdateConsumerPrice, MinPriceForUpdat, ManualAvoidUpdate, Upd, WholesalerPrice, UpdatedWholesalerPrice, CategoryD )
SELECT Products.code, Products.consumerPriceWithVat AS PreUpdateConsumerPrice, FC.MinValForUpdate AS MinPriceForUpdat, Products.AvoidUpdate AS ManualAvoidUpdate, Nz([consumerPriceWithVat],[MinValForUpdate]+1)>[MinValForUpdate] And Not [AvoidUpdateEuro] AS Upd, Products.WholesalerPrice, IIf([Upd],[WholesalerPrice]*[FC].[UpdateRate],[WholesalerPrice]) AS PostUpdatePrice, Products.CategoryID
FROM Products, FC

As you said - it's a mock - so I assumed this wasn't the Actual SQL.
As Spike has said dump the results of your insert into a new table. See what isn't there and look at the data in those records.

If your SQL / data was correct then this would work, but it doesn't, so by definition either your SQL is wrong or your data is wrong or both are wrong.

 

[marlan]

You are expending a lot of effort on something simple. Use data in your insert query to make a table instead. Select it and copy it to excel: so simple.

Then insert(append) from that table into you target table. Inspect the records not inserted. Insert them by hand one by one and see where the system complains.

Well, I did that, the system accepted all the data, and didn't complain!...

As you said - it's a mock - so I assumed this wasn't the Actual SQL.
As Spike has said dump the results of your insert into a new table. See what isn't there and look at the data in those records.

If your SQL / data was correct then this would work, but it doesn't, so by definition either your SQL is wrong or your data is wrong or both are wrong.

Mock SQL = change table and field names, fields and formulas are the same.
The SQL is correct, it gave me all tha data, and the table is fine, all the data was appended to it.
It is somthing else.
Do you have a clue?...