Avatar of exp vg
exp vg
 asked on

Access - Join on Two Fields, With and Without Third Field in Output

I have two tables with 15 fields, of which 3 are Last Name, First Name, and ID.

I join Last Name from Table A, to Last Name form Table B, and the same for First Name. I also then Group By to get single row values.

When I join just on names, I get an output row count of 1900 (ID is not in the query).

When I join on the two fields, Last Name, First Name - but this time have ID in the output but not joined, I get an output row count of 2100.

What i am trying to understand is why does the count go up when ID is included in Design View, but is not being joined between Tables A and B.

Thank you.
Microsoft Access

Avatar of undefined
Last Comment
Hamed Nasr

8/22/2022 - Mon
exp vg

ASKER
I should add that the row count does not always increase after a third field is in the output (but not joined) - in another instance there was a decrease.

I just want to understand why the number changes after additional fields are joined in design view, but are not directly joined.

Thank you.
SOLUTION
Hamed Nasr

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
exp vg

ASKER
The reduction in count makes sense if there are duplicates, but not when the count increases. I also just tried adding more fields to design view - but not a direct joined.  Now I see the counts increase.

Thanks.
Rey Obrero (Capricorn1)

you have records with the same Last Name and First Name BUT, different ID
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Hamed Nasr

If adding a new field to Group By, adds more records, then expect few duplicates including the fields but last.
exp vg

ASKER
If they are different, will the row count increase or decrease - again these additional fields are not joined.

My initial take is they will increase because more rows are added to account for the different values. However, why do I see a decrease at times?

Thanks.
exp vg

ASKER
Ok - I see what is happening. More fields are added since there is not a definite join - and more likely an increase. The most direct and accurate ways is to join on just Last and First Names to avoid this crossover with additional fields that are not the same.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
exp vg

ASKER
Thanks Rey - please confirm that the code above accounts for the join in design view only happens between first and last name.
exp vg

ASKER
These explanations make sense.

Thanks so much.
exp vg

ASKER
Thank you.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Hamed Nasr

All depends on what you plan to do.

Our comments include assumptions on your behalf which may not be accurate,

If you have tables:
t1  3 records, t2 4 records.

Then INNER JOIN may produce up to 4 records.
Without a join you get 12 records.