exp vg
asked on
Access - fewer records after left join tablea and tableb
I create a left join - from tablea to tableb. I am having a reverse situation than a previous question asked - how can it be that i have fewer records in tablea after the join than before.
tablea has 100 records. i join on tableb on last name and first name, and now tablea indicates 90 records.
The join indicates to have all records in tablea, and only those in tableb that are a match to tablea.
Hope this makes sense.
Thank you
tablea has 100 records. i join on tableb on last name and first name, and now tablea indicates 90 records.
The join indicates to have all records in tablea, and only those in tableb that are a match to tablea.
Hope this makes sense.
Thank you
Dale Fye
Post your SQL, I'm sure it has to do with the way you did the join or an associated WHERE clause.
ASKER
SELECT [TRI 2015].[Last Name], [TRI 2015].[First Name ]
FROM [TRI 2015] LEFT JOIN [TRI MAY PAYOUT] ON ([TRI 2015].[First Name ] = [TRI MAY PAYOUT].[First Name]) AND ([TRI 2015].[Last Name] = [TRI MAY PAYOUT].[Last Name])
GROUP BY [TRI 2015].[Last Name], [TRI 2015].[First Name ];
FROM [TRI 2015] LEFT JOIN [TRI MAY PAYOUT] ON ([TRI 2015].[First Name ] = [TRI MAY PAYOUT].[First Name]) AND ([TRI 2015].[Last Name] = [TRI MAY PAYOUT].[Last Name])
GROUP BY [TRI 2015].[Last Name], [TRI 2015].[First Name ];
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
glad to help.
Have you considered my comment about tables with years and month in their names? This is really bad database design because it requires you to create new tables, forms, queries, and reports as you move forward. Ideally, you would simply add an extra column (Tri_Year) to your [TRI] table and maybe two ([Tri_Year] and [Tri_Month]) to your [TRI Payout] tables. This would give you the ability to use the same tables forever by simply filling in the Year and Month columns in those tables.
Failure to do this early in your project will eventually lead to headaches!
Have you considered my comment about tables with years and month in their names? This is really bad database design because it requires you to create new tables, forms, queries, and reports as you move forward. Ideally, you would simply add an extra column (Tri_Year) to your [TRI] table and maybe two ([Tri_Year] and [Tri_Month]) to your [TRI Payout] tables. This would give you the ability to use the same tables forever by simply filling in the Year and Month columns in those tables.
Failure to do this early in your project will eventually lead to headaches!
ASKER
Yes - I have considered your suggestion, and am looking into this. How I manage works for now - but I am all open to suggestions that will help in the future.