You're definitely on the right track. You only need 1 DealerT if the information contained in it is the same or similar for both the Auto and Parts Table just linking the DealerID in each table. And as for bringing it in twice, without actually seeing what your query is doing, that sound right.

If nothing else, make a backup, try it. If it doesn't work or messes anything up, restore, try something else, repeat. And if you get truly stuck, you know where to find help. Just show screenshots of what you're trying to do as in how you're trying to combine the records to get one dataset.

Thank you Donald, I think I am thinking of ad hoc queries when I say bring it in twice. I just got it mixed up. I think What I would do is bring the table in twice and make 1 relationship outer join for Dealer and bring the table in again and make a relationship with outer join for the parts/service also. I think that would be proper, does that sound better or correct I should say?

Yeah, that's what I thought you meant :) I think I may not have been clear, lol. Not knowing how you're planning to use the data, what you've suggested might work, it might not.

If for example, you're building a form focused on a Dealer and which autos and which parts/services he has sold, you only need the dealer table once and it can link to the other two tables with just one copy of the table in the QBE.

On the other hand, if one of the other two tables is the main focus and you're just pulling the dealerT in for extra information, then you will likely need to pull it in for each table that wants it's information.

Donald Thank you I am thinking along the correct ways I believe.

You're both on the right track. One Dealer table is the correct design. Just store DealerID in any table that needs to reference that dealer (Auto, PartsService, etc.).

And yes, in a query you can absolutely bring the Dealer table in more than once. Access will automatically give it aliases like DealerT and DealerT_1. That does not duplicate the data. It simply lets you join the same table to different fields so you can pull dealer information for each relationship.

For example, if a table had both SellingDealerID and ServicingDealerID, you would join DealerT once for the selling dealer and again for the servicing dealer. Same table, two roles.

I do something like this all the time with employees. You might have an EmployeeT table that stores all your staff. Then in other tables you reference that same table multiple times but with different field names.

For example, a CustomerT table might have SalesRepID, ServiceTechID, and CustomerServiceRepID. Those are three different foreign keys, but they all point back to EmployeeT. In a query you would simply join EmployeeT once for each role so you can pull the appropriate employee name or information.

The important thing is that the data only lives in one table (DealerT or EmployeeT). That keeps the database normalized and prevents duplicate records.

Multiple Joins

This is one of those times when I thought, oh this would make a pretty cool video, and then I searched for it and realized I already made a pretty cool video for it. LOL. I do that a lot.