More than 15 left joins for matching rows (1 Viewer)

jaryszek · Feb 6, 2019

Hi Guys,

i would like to find existing rows in one table and not existing in another,
i used the fastest native way in Access : left join with null condition.
Problem is that Access has restrictions, not more than 15 left joins in one query.

Query like here:

But this is not question about this specific query but generally - how to do it in other, fast way to compare 2 tables and use something different than joins?
(find all rows in 1 table not matching with 2 table?)

What do you use?
Best,
Jacek

June7 · Feb 6, 2019

Not something I've ever had to do.

Sounds like will need VBA opening and manipulating recordset objects.

isladogs · Feb 6, 2019

This seems like 'deja vu' ... but all over again.
We covered this a few weeks ago.

Looking at the table design t2 has no PK field
Redesign one/both tables so you have a UNIQUE PK or UNIQUE INDEX in each.

Then apply your left join and null to those fields ONLY
Its basically an unmatched query for which there is a wizard if you wish to use it

jaryszek · Feb 6, 2019

Hi Guys,

thank you. VBA instead of nulls? hmm...

Colin,
yes, we did and i remember but here we have DISTINTROW without PKs.
And i can not add any unique numbers to my tables because they can be changed. this is like your example from site by trying to use more than 16 joins...

Thank you,
Jacek

isladogs · Feb 6, 2019

jaryszek said:
Hi Guys,

thank you. VBA instead of nulls? hmm...

Colin,
yes, we did and i remember but here we have DISTINTROW without PKs.
And i can not add any unique numbers to my tables because they can be changed. this is like your example from site by trying to use more than 16 joins...

Thank you,
Jacek

That's why I said 'deja vu'
You can do this either using queries or SQL statements in VBA

I'm not suggesting you add additional fields.
What I'm saying is to identify a combination of fields that taken together will be a unique combination of values
e.g. in a student report grade table, I can create a composite INDEX on the fields StudentID, ClassID, ReportSession
e.g. 11057; 09A/Ma3; 201803
Though each student, class and report session will have multiple records in the table, there will only ever be one record with that exact combination

jaryszek · Feb 6, 2019

Hi Colin,

thank you. Sounds good to me. But this field will be very long.

Best,
Jacek

jaryszek · Feb 6, 2019

One more thing,

should i add index on this combined field?

Best,
Jacek

isladogs · Feb 6, 2019

But this field will be very long.

Do you mean it will need quite a few fields in the index?

should i add index on this combined field?

That's exactly what I'm saying. Then just use those fields in your table joins.
You should also have a PK field on t2.

jaryszek · Feb 6, 2019

thank you Colin,

no i mean that i will have a lot of concatenated fields in one unique field.

But i think this is not a problem,

One more question.
How steps should look like?:
I have 2 tables: Import and Access Table.

So for Access table i am creating query where i am adding concatenated field.
Later i am creating another query for Imported Table with contatenated field as key.
I can join them and use delete query.

When should i add index? To query?

Best,
Jacek

isladogs · Feb 6, 2019

There is no reason to concatenate the fields.
Just use a composite INDEX on those fields as in the highlighted example below where it includes 2 fields but you can have more if needed

Review your tables and add indexes as necessary.
Then see if you can do the next step yourself - it should be straightforward for you.
If not, then return with further question(s)

jaryszek · Feb 6, 2019

Thank you but kill me - i do not understand,

Assume that i have 2 tables:

TableAccess and TableImport.

Assume that they have more than 16 joins to perform.
In example only 3.

I added indexes to both tables:

and how to create delete query on them?
Can i use somehow select query also to see unmatched records in both tables using indexes?

Best,
Jacek

isladogs · Feb 6, 2019

There is no difference in the queries other than you will only need to join the fields being indexed so it will be far less than 16 joins.

In fact the limit is 10 fields in any one index (or PK) with up to 32 indexes allowed per table

jaryszek · Feb 6, 2019

But I would have in this case all fields (3 in example) joined so how I can have less number of joins? Andif the limit is 10 fields for one index how this can be helpful for 16< fields ?

jaryszek · Feb 6, 2019

Sorry I Am on mobile I will review your attachment asap and let you know

isladogs · Feb 6, 2019

After 20 years working with Access I've never needed to use more than 5 fields to make a unique composite index. I cannot imagine ever needing 10 let alone 16 fields to ensure uniqueness.

If you are telling me that even 10 fields in an index would not be enough to ensure uniqueness, I would suggest the table design is almost certainly flawed.

The_Doc_Man · Feb 6, 2019

After looking at your relationship diagram, a couple of things pop up in my mind.

Your diagram obfuscates the purpose of this exercise. Your statement says you want to find rows in one recordset that are not in another recordset. But you don't say what constitutes being "not in" the recordset. Is it that EVERY FIELD must be present because even one empty field constitutes a mismatch? Or are you looking at some key field that represents everything else and if the key item isn't there, that is a mismatch BUT if the key is present, other fields can mismatch but not count against your test? What constitutes a mismatch?

jaryszek · Feb 6, 2019

Thank you Colin,

aa so indexes are speeding up when we are joining 3 fields within indexes?
How this is working?

I have more than 16 because i have one big table with different attributes and i am getting it from my Client. I can not change this. It is possible.
So join on more than 16 fields can happen.

Best,
Jacek

isladogs · Feb 6, 2019

Indexes store the location of your data so it can be retrieved quickly.

I'm sorry but I do not believe the data needs more than 10 fields to be unique.
If I'm wrong then by all means concatenate your fields.
That will have to be a text field and it is quite likely that 255 characters will not be sufficient.
So you'll then need a long text AKA memo field.
Now comes your next set of problems including....
Memo field cannot be PK fields though Access will let you index a memo field and set it as unique.
You can't sort a memo field in datasheet View
Most importantly of all for your purposes, you can't join memo fields in a query.
You cannot solve this without reviewing your data structure.

MajP · Feb 6, 2019

I agree that this seems strange to have to look at 16 fields for uniqueness. But you can do this in a Cartesian join

Something like

Code:

SELECT 
 Employees.EmployeeID, 
 Employees.FirstName, 
 Employees.Title, 
 Employees.BirthDate
FROM Employees, EmployeesStr AS B
WHERE 
 (Employees.LastName=[b].[last_Name]) AND (Employees.FirstName=[b].[First_Name]) AND (Employees.BirthDate=CDate([B].[Birthdate]);

MajP · Feb 6, 2019

Also from what I have read using a where clause instead of specific joins is often the same performance and not less efficient, which I would have thought.

Inner Joins and the Query which use the Where clause are similar. SQL Optimizer will identify this and will either convert the where clause to a join or vice-versa depending on the cost of the query

However a specific join is clearer and you are less likely to code an error I would think.

More than 15 left joins for matching rows (1 Viewer)

Registered User.

Attachments

AWF VIP

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Attachments

Registered User.

Attachments

MVP / VIP

Attachments

Registered User.

Registered User.

MVP / VIP

Immoderate Moderator

Registered User.

MVP / VIP

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread