Question Import specific data cells from excel into Access 2016 (1 Viewer)

[wolves1]

Hi Colin,

Testing a small batch with the actual DB, but when I run the query, it said it's going to update 23 rows instead of my batch of 9. I have a feeling it might create duplicate incorrect data like the first time when I tried on test data. Any advice?

Thanks,
Carmen

 

[isladogs]

Carmen
Can I suggest you read my article on synchronising data on my website
See http://www.mendipdatasystems.co.uk/synchronise-data-1/4594514001
The second part may be particularly relevant to your situation as it covers issues where the import table has no PK field, possible duplicates with null values in some fields and much more.

Next check that you have the correct records either using a SELECT version of the query or by looking at the DATASHEET view before updating.

Does it show what you expect?
Does the update query SQL include DISTINCTROW?

You could make a backup copy of the destination table
If in any doubt suggest you show us the update query design and supply the SQL view code.
I may also ask to see the data in the two tables if that's OK

 

[isladogs]

Mike
Following on from my tests in post #20, I decided to compare how Access ran the update queries in both the inner join version and its Cartesian equivalent.
To do so I used the JET Show Plan feature which prints out the query execution plan which Access uses to optimise the saved query

These are the 2 queries I ran

They are functionally equivalent but INNER join runs very slightly faster
Here is the ShowPlan for the INNER join version:

Now here is the equivalent Cartesian join version:

If you check you will see these are identical
I've highlighted the most important part here. Access has imposed an inner join even though technically its not in the query

So I'm guessing that the slightly slower time for the Cartesian is that needed by Access to work out how to do it most efficiently

Hope that helps

P.S. If you want to know more about JET Show Plan see this article on my website: http://www.mendipdatasystems.co.uk/show-plan-go-faster/4594460516

 

[mike60smart]

Hi Colin

Many thanks for the info.

It will take me some time to digest.

 

[isladogs]

No problems. Do ask if you have any questions
JET ShowPlan is useful for occasional investigations such as this.
Certainly not for everyday use

You may be interested in this parallel thread: More than 15 left joins for matching records

 

[wolves1]

Hi Colin,

Turns out after speaking with a staff, they forgot to tell me a key detail when updating the data to the empty cells. The primary key was not the Company number, but instead a different column called Notice Number. This Notice Number column's numbers are unique based on a different column's Year.

I sent a picture of how the columns are supposed to be. Need some help on how to update the AR Transaction number matching the Notice Number, but also based on Year. I don't want data to be updated on the same Notice Number, because that number can be the same but if it's a different year, it's different data. Hope that makes sense

Thanks for the help!

 

[wolves1]

Hi Colin,

So I attempted to join the Year column together and it worked. I guess crises averted!

Thanks again,
Carmen

 

[isladogs]

Whilst I'm clearly pleased this works for you, your screenshot shows duplicate values in the Notice Number field. Is that a query or the actual table?

 

[wolves1]

Actual table because the Notice Number is only unique based on the year. The same number can show "x" amount of times as long it is on different years. There shouldn't be two exact Notice Numbers in the same year.

For example, in 2014 there should only be one "0001" Notice number. Then in 2015, there is another "0001" number because it's a near year and the generated number resets from the beginning. Hopefully that makes sense.

Thanks,
Carmen

 

[isladogs]

In your screenshot there are 3 records with 0001 for that field.
Two in 2018 & one in 2019.
But it doesn't matter whether these are in the same year or not.
If Notice Number is a 'solitary' PK field, there can be no duplicates in that field.
So either its not the PK or you have a composite PK of two or more fields

 

[wolves1]

HI Colin,

Thanks for the heads up. I'll check with the staff to make sure everything is correct as it seems like information doesn't get translated right away...

Thanks,
Carmen