Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
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.
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
The difference, may be because you have duplicate records in Last_Name, and First_Name
Adding ID to the Group By reduces the duplicates.
If ID is unique, you get the same total number of records when included in group by.
Check your data for duplicate field values in Last_Name, and First_Name.
Adding ID to the Group By reduces the duplicates.
If ID is unique, you get the same total number of records when included in group by.
Check your data for duplicate field values in Last_Name, and First_Name.
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.
Thanks.
If adding a new field to Group By, adds more records, then expect few duplicates including the fields but last.
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.
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.
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.
with these records
ID Lastname FirstName
1 A B
2 A B
with this query
select lastname, firstname
from table
group by lastname, firstname
will give you one (1) record
Lastname FirstName
A B
if you include the ID in the query
select ID,lastname, firstname
from table
group by ID,lastname, firstname
you will get
ID Lastname FirstName
1 A B
2 A B
as result
ID Lastname FirstName
1 A B
2 A B
with this query
select lastname, firstname
from table
group by lastname, firstname
will give you one (1) record
Lastname FirstName
A B
if you include the ID in the query
select ID,lastname, firstname
from table
group by ID,lastname, firstname
you will get
ID Lastname FirstName
1 A B
2 A B
as result
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Thanks Rey - please confirm that the code above accounts for the join in design view only happens between first and last name.
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I just want to understand why the number changes after additional fields are joined in design view, but are not directly joined.
Thank you.