INSERT query does not insert all data in query (1 Viewer)

marlan · Sep 7, 2015

Hi all you experts!

I want to insert the result of a query into a Temp table, but some data is omitted: the values of 2 fields in 5 rows

The correct data appears in the query result, not in the table.

Any ideas of what could have happened between the query and the table?

Thanks in advance!

CJ_London · Sep 8, 2015

If you provided the sql to your query, the details (fieldnames and types) of your source data and destination table, plus any vba around running the query, Oh - and what does the query report say about how many records/fields were updated? I could be more specific.

Otherwise my best guess it is something to do with your sql, source data, destination table or any vba you have running around the query.

And have you turned off error suppression if you are using vba so you can pick up any reported errors?

marlan · Sep 8, 2015

Hi CJ,
I'll mock the SQL:

Code:

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

The idea: Update product prices (held in Products table) according to changes in foreign currency rates (held in FC table). All prices are updated, except for:
1) Products with consumerPriceWithVat lower tan a minimum value (FC.MinValForUpdate).
1.5) if null - see consumerPriceWithVat as higher than minimum Value (FC.MinValForUpdate +1)
2) Products manually selected (Products.AvoidUpdate = True)
in these cases, the PostUpdatePrice stays the current WholesalerPrice.

The temp table PricesForUpdate was created by a an INSERT INTO query.

as for VBA: This is part of a sequence of Updates on Products table, eventually run a DELETE Query on PricesForUpdate (a temp table), and the above INSERT query.

Rsults: the expected 5236 Records, mostly with what seems to be fine full data, except for 5 records (All of the same categryID): these have Null in the consumerPriceWithVat field, and therefore there WholesalerPrice gets updated in PostUpdatePrice. the actual data has a price lower than FC.MinValForUpdate, and should not be updated.

My guess would also be the destination table, but what could it be? the table was first created by the source query?...

Minty · Sep 8, 2015

You appear to be referring to [Upd] in your Query which is a calculated field. You can't normally refer to the alias of a calculated field in the same query.

marlan · Sep 8, 2015

Hi Minty,
The calculated field works fine (I first save the query with the alias, then calculate upon it, it usually works fine for me).

What is missing is the first field: PreUpdateConsumerPrice, it is null in these 5 records. Having it null, the calculation is fine. But they shouldn't be null...!
These values appear when I view Query results, but are not inserted...

Minty · Sep 8, 2015

Okay your explanation is sound - but the SQL you posted is therefore not accurate to the what is happening.

Can you post the two queries - the first one where [Upd] is calculated and then the consequent query. I suspect you may be seeing some of these http://allenbrowne.com/ser-45.html side effects

arnelgp · Sep 8, 2015

use Insert query using only one table (Products or FC).
then use Update query using the other.

marlan · Sep 8, 2015

Minty said:
Can you post the two queries - the first one where [Upd] is calculated and then the consequent query.

Thanks again,
It's one SQL I save (crtl+s) the query after creating the aliased field, then calculate upon it, in the same query.

Minty said:
I suspect you may be seeing some of these http://allenbrowne.com/ser-45.html side effects

The fields seem to be calculated fine, and the destination table has correct field types.

It seems to me the error occurs on the PreUpdateConsumerPrice field, because once this field is null, calculation is correct...

marlan · Sep 8, 2015

arnelgp said:
use Insert query using only one table (Products or FC).
then use Update query using the other.

Hi arnelgp, and thanks for replying,
sounds like an extra action, that should work... could you explain why would that be needed?

An other piece of information: the Products - FC relation is technically of NxN. Actually FC has only one record.

Minty · Sep 8, 2015

I am certain this won't work;

marlan said:
It's one SQL I save (crtl+s) the query after creating the aliased field, then calculate upon it, in the same query

Because you will lose the calculation - Your second query can't refer back to the other one if I am understanding what you are doing...
As arnelgp says you need to do this in two stages.

arnelgp · Sep 8, 2015

you are creating a "cartesian of product" on your first post sql. meaning you double the records that would be inserted on your table (# of records in products x # or record in FC).

marlan · Sep 8, 2015

Hi,
This is the data from the query:

4857 14.0536175251578 16 0 0 9.1868 9.1868 2465
6179 15.0574473483833 16 0 0 9.843 9.843 2465
4132 15.0574473483833 16 0 0 9.843 9.843 2465
3862 15.0574473483833 16 0 0 9.843 9.843 2465
2465 15.0574473483833 16 0 0 9.843 9.843 2465

This is the data from the table:

6179 16 0 -1 9.843 9.31426946535986 2465
4132 16 0 -1 9.843 9.31426946535986 2465
3862 16 0 -1 9.843 9.31426946535986 2465
4857 16 0 -1 9.1868 8.6933181676692 2465
2465 16 0 -1 9.843 9.31426946535986 2465

*** EDIT: You can see these result in Excel format later, at No 14 ***

You can see the in the table Prices missing before the minimum value for update (16), therefor upd is true (-1), and price update is calculated (9.31426946535986 rather 9.843 in 4 of 5 records).
Calculation works fine, it's the simple data that is missing.
All appears fine in the above Query results.

Thanks for your patience

marlan · Sep 8, 2015

arnelgp said:
you are creating a "cartesian of product" on your first post sql. meaning you double the records that would be inserted on your table (# of records in products x # or record in FC).

Yup, but it is actually # of records in products x 1 = # of records in products - FC has only 1 record.

Is the lack of a joining field an issue?

marlan · Sep 8, 2015

Here is the data in Excel

gemma-the-husky · Sep 9, 2015

you said you thought the problem was caused by nulls. In that case, I expect the table to which you are appending the data is set to not allow nulls in the relevant field (required = true)

either change this to required=false, or ensure your query returns a value

marlan · Sep 9, 2015

Hi Dave, and Thank you for your reply!

gemma-the-husky said:
you said you thought the problem was caused by nulls. In that case, I expect the table to which you are appending the data is set to not allow nulls in the relevant field (required = true)

either change this to required=false, or ensure your query returns a value

A Null value is fine (and expected in case of a new product being given a price for the first time).

gemma-the-husky said:
or ensure your query returns a value

The query returns different results when in data-sheet view, and actually inserting the result set.
I will refer to the results in the attached Excel:
- The top set of data is from data-sheet view of the query. Note values in field PreUpdateConsumerPrice as expected, calculated field upd has a False Value, and UpdatedWholesalerPrice has the same value as WholesalerPrice.

- The bottom set of data is what was inserted into the table. Note Null values in field PreUpdateConsumerPrice, NOT as expected, calculated field upd has a True Value, as expected in case of Null value in PreUpdateConsumerPrice, and UpdatedWholesalerPrice receives an updated value, in refer to field WholesalerPrice.

Could understand why would an INSERT query insert Nulls, instead of the raw value? it seems NOT to be a table issue, because calculations are accurate according to this error selection.

Thanks again,
Marlan

Minty · Sep 9, 2015

Can you post the ACTUAL SQL of your Update query and the ACTUAL SQL of your Select query? As stated earlier there are some oddities with calculated fields....

marlan · Sep 9, 2015

Minty said:
Can you post the ACTUAL SQL of your Update query and the ACTUAL SQL of your Select query? As stated earlier there are some oddities with calculated fields....

in post#3 I've posted the actual SQL, Just modified table and field names, to not enclose what field of products these are, and what foreign currency is in the process... calculations are the same

Minty · Sep 9, 2015

In that case I'll re-iterate what I said before;

Code:

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,[COLOR="YellowGreen"] 
Nz([consumerPriceWithVat],[MinValForUpdate]+1)>[MinValForUpdate] And Not [AvoidUpdateEuro] AS Upd[/COLOR], 
Products.WholesalerPrice, IIf([COLOR="Red"][Upd[/COLOR]],[WholesalerPrice]*[FC].[UpdateRate],[WholesalerPrice]) AS PostUpdatePrice, Products.CategoryID
FROM Products, FC

You can't normally refer to an Alias created (In Green) within the same query (In Red).

GinaWhipp · Sep 9, 2015

Have read thru this is I still need some information....

1. Fields Names and Data Type of Table you are appending to.
2. Why are you appending when you have the results you want right there in the query? (If I missed that somewhere my apologies.)
3. I see *CategoryD * and then below *CategoryID*, is that a typo? If so, please confirm the query is correct as it means you typed it in and did not do a copy/paste.

Side note... Yes, you can refer to an Alias within the same query I do it all the time. The only way this becomes a problem is if the Alias is not calculated before the next field needs its results. Please confirm that it is first calculated.

INSERT query does not insert all data in query (1 Viewer)

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

AWF VIP

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Attachments

Super Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Similar threads

Users who are viewing this thread