July 5, 201015 yr 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?
July 5, 201015 yr 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.
July 5, 201015 yr Author 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.
July 5, 201015 yr 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. :
July 5, 201015 yr Author 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 :
Create an account or sign in to comment