Exclude records if they appear in a table

[ckai]

This has tripped me up all day...

I have a Clients table which stores all my clients (obviously). Just recently I've added another table for a certain type of client (private instead of a company). The reason I have this separate table is because they need more information stored about them than the companies do. Let's call it Private_Details

So I created a separate layout and it works well in only showing the private clients. However, now I want the original Client layout to only show the Companies and exclude those that appear in Private_Details as well.

I thought it was going to be a simple matter of using a TO for the Private_Details and creating a relationship where the client ID from that was not equal to that in the Clients table. Seems not unless I need to do something else.

I could just filter the table to exclude but I would ideally like to have it as a relationship.

Any ideas or other work-arounds?

[Vaughan]

Yes... store them all in the one table. A couple of extra fields here and there doesn't matter.

One day you're going to want to something really simple -- like print a list of all clients -- and you'll regret having them split between multiple tables.

[ckai]

All the clients are in one table at the moment and I'll leave them there as well. I just have the extra details for the private clients in another. The reason for taking out the private info is because the private clients only make up about 10% of our total clients.

Also I wanted the layout different for the private clients. I have a client type field and was hoping to use these, after it's selected, to show the private clients fields. I was just easier doing a full layout for them.

[Vaughan]

I have a client whose previous developer split tables. It's a nightmare to understand and maintain because stuff is everywhere. While I'm sure the developer had good reasons to split it that way, I cannot work out what they were and I cannot see any benefits, only negatives.

You can have a separate layout for the privates if you want without splitting the fields between tables.

now I want the original Client layout to only show the Companies and exclude those that appear in Private_Details as well.

This can be done by omitting all the company records as they are viewed -- an easy thing to do with script triggers and custom menus but awkward and quite a bit of work. It means the record count at the top will show a found set, and the user navigation will need to be managed carefully.

Other posters may have a different view and better suggestions. :

[ckai]

I see where you're coming from Vaughan. It's the only table I've taken specific details out and put them in a separate table. I put it down to my previous experience in Access :

My original plan was to have a set of radio buttons that would filter the clients from private and companies but then I didn't like the idea of having the extra fields that I needed for the private clients showing for the company ones.

I'm trying to keep this database simple but I seem top be failing :