1 to1 relationship keeps becoming unsynced

[skyview chick]

Very stupid question - I know it! But I have a database where they are separated into front and back ends with 2 main tables - A client table and an Installation table. The tables are in a 1 to 1 relationship. But if a sales person puts in a client without an installation or an installation without a client, it screws up the database because the ID numbers then don't match. I have Referential integrity turned on and Cascade Delete Related Records but I can't turn on Cascade Update Related Fields (although I'm not sure that will make a difference). Is there anyway to keep this 1 to 1 relationship and force it to create a blank record to avoid mismatched ID's???


Thanks much!!

[orange]

Please tell us more about your tables, and the back ends and front ends.

[skyview chick]

sure they're really basic -
the back end is basically just that a client table and a installation table with a couple of smaller tables that just hold installer names, promo codes and part numbers that are auto populated in the linked regions.
the front ends are 2 different types, and installer end and a Salesperson end. The salesperson end is the one I have trouble with. It has queries to pull all the info from the tables, as well as forms for input. The main input form pulls from both the client and the installation table, and at the bottom has a table of all the clients in the whole database.
does that make sense or do you need more information.
It really is basic because I do have limited knowledge.
I have limited knowledge of SQL and no VBA experience at all.

[kagoodwin13]

Do you have a form linked to the client and installation tables? Is it a search form or an input form?

An input form would need to have some method of requiring fields to be entered before submitting. This is similar to when you enter in your address without a zip code while trying to order something on Amazon - it stops you from submitting until you enter the correct information into the form. Not sure how to do this, but I wanted to throw the idea out there.

A search form should have unbound text boxes and be tied to a query, not directly to a table. If you enter information into a search form tied to a table, it will input data into the table, and you'll have a bunch of junk records storing attempted search terms.

[skyview chick]

Ah yes, it is an input form that is linked to both the client and installation tables. That makes sense that I should have required fields - I'm not sure how to do it though either - but hopefully there's an answer on Google. Thanks - at least I have a solution to look for now!

[kagoodwin13]

Hopefully this should get you an answer:

http://office.microsoft.com/en-us/ac...010283188.aspx

I'm not exactly sure how your forms are set up, but it sounds like you aren't linking your client tables and installation tables correctly. On your client table, you might want to have a field like "Installation number" linked to an AutoNumber field on the Installation table. On your installation table, your first field should probably be "Client Number" linked to the Client table.

Having come from sales, you may want to make your Client Number/ID something that the client will know and that the sales person can easily look up - eg the client's 10-digit phone number. If the salesperson asks the client for their client number, and it's something non-sense like 58216, it puts stress on both parties. This is especially bad if salesperson can't look up their record without that non-sense number - it doesn't really help either party. You know you've had this experience before if you've ever called a government agency or cable company. If you think you can work it somehow, try creating a search form (technical term: Query By Form) to create an easy method of looking up client records.

Try to automate this data entry process as much as possible. The client form and installation form should probably be all one big form, linking each field in the form to the appropriate table. This way, it looks and feels like an easy one-step process, rather than a drawn-out two-step process. It will also serve as a mental reminder to the salesperson that they need to enter information for both parts.

I'm not saying these ideas are must-haves, but I wanted to get the wheel spinning on how a salesperson might view a database. It should be a tool to make selling and relationship management easy, not a digital bureaucracy.