Inserting Index in tables to expedite runtime in Access

[BittenApple]

Hello team,
I am inner joining two tables and it takes a long time. I wonder If I can insert an index on Primary Key to shorten runtime?
Please advise me.
BittenApple

[HansV]

There is always an index on the primary key of a table, because the primary key is an index itself.
Is there an index on the foreign key in the other table? If not, it might help to create one.

[BittenApple]

Hello Hans,
Is converting to SQL sql server would be another option if we don't use index option?
regards,
BittenApple

[HansV]

SQL Server tables need indexes just like Access tables, so I don't see the difference.

[BittenApple]

Hans,
I am looking through my old posts, looking for something and reading my old post to refresh my memory and remember things.
I saw this post here. I do really like indexing the join fields.
So many thanks,
BittenApple

[HansV]

You're welcome!

[Mark L]

Just out of curiosity, how big are the 2 tables you are trying to join in that query? That is, how many records in each table?

[BittenApple]

Hello Mark,
Thanks for you attention, the tables have 140,000 and 50,000 records.
Regards,
BittenApple

[HansV]

With those sizes, it's essential that there is a primary key on the 'one' side of the join, and an ordinary index on the 'many' side.

[BittenApple]

Hello Hans & Mark,
how?
I set the MemberID as PK on Left table (I did a select and Make-table query, is there any other way other than this?); on right hand side table (I did a select and a make-table query as well); I inserted a new field on the right hand side table and called it Visit ID and the type of field was autoNumber; I had to save the table, I got an error message.

What I did was that I copied the structure of make-up table and pasted it, then I added AutoNumber and then updated the table with the content of right hand side table.

Now, I have faced with this question: how the left hand side table which has MemberId as a Pk to be linked to right hand side table with has visitID (AutoNumber as a PK)? How this can be handled? I am in dismal; although I have normalized tables and inserted pk on each table.

Regards,
BittenApple

[HansV]

Does the right-hand table have a field MemberID that corresponds to MemberID in the left-hand table?

[BittenApple]

Yes, it does.
Regards,
BittenApple

[HansV]

So MemberID is the primary key in the left-hand table, and an ordinary field (not the primary key) in the right-hand table. You should link the tables on MemberID.

[BittenApple]

Hans,
Ok, I will.
Why would you say ordinary key instead of FK?
Is there a method to populate the left hand side table with the autoNumber (VisitID) of right hand side table?
Why did we generate PK in this context for right hand side table? We can normalize the left hand side table only and then assign MemberID as a Pk and then link it to right hand side table on MemberID. What is the benefit of generating PK for right hand side table? For referential integrity? We can join through MemberID and enforce referential integrity.
I hope these all make sense.
BittenApple

[HansV]

By ordinary field I meant that the MemberID field in the right-hand table is not the primary key in that table.

As far as I can tell, the left-hand table should not contain a VisitID field.

S324.jpg

With a very small number of exceptions, every table should have a primary key. It is always handy to have a field that uniquely identifies the records in a table.

[Mark L]

Building on Hans' last post, you also need to understand that if you create a relationship between 2 tables and if you Enforce Referential Integrity, then Access creates a hidden index for you on that foreign key in the child table. In Hans' example (if EFI was specified), Access would have created an index on the MemberID field in the Visit table; so there is no need to manually index this field.