Table interacting

[Guest]

I have 3 tables and an Input form for table 1:
Table 1 = all contacts
Table 2 = Clients Product A
Table 3 = Clients Product B

In Table 1 are all people we contacted. Out of this table are coming
customers which have to be transmitted into the different Product tables
(Table 2 and 3).

The Input form for Table 1 has to Yes/no checkboxes for Product A and
Product B.
If I run the Macro on that, the clients are getting transferred into the
different Products tables. The problem is, that all other contacts (other
than the transferred) are not shown anymore in the Input form (they are still
in the Table 1 but not on the Input record form).
How can I keep the records which are not transferred from Table 1 as well in
the form?

 

[Rick B]

You should not move records from one table to another. It defeats database
normalization rules. You also should not include data in a field name
(productA).

All your clients should be in one table. Then, you should have a field
where you can select "prospect, Product A, or Product B".

If a prospect could end up being a product A *and* a product B customer,
then you would need a new table and you would end up with a many-to-many
relationship. Post back if you need help with that.

Almost any time you have to build a macro or append query to move data
around, you are doing something that does not meet relational database
normalization rules. Not always, but almost always.

 

[Guest]

Dear Rick
thank you for your answer - YES, you are correct but I do not see any other
possibility to solve my problem. I will not run into the many-to-many table
problem because actually I only need three.
I'll tell you why.
In one table are coming all people we are in contact with. Until that time
they are only contacts (not customers). We have only to products for the
people. So, I need my "all contacts" table and if a contact becomes a
customer he will go in one of those two product tables. I have to split the
customers because it is necessary for further developpement of this program.
So I made a form to input my contacts into a table. I created two checkboxes
which will be marked (depending which product) if the contact becomes a
customer - and transferred to the specific tables.
My problem is that normally I always can see the resting contacts in the
"all contacts" table. But with me it is, as soon as I close the form, I do
not see any record anymore in this form whereby the "all contacts" table is
correct. How can I see those record as well in the form? Or do you have a
better idea to solve this problem in total?
Thanks for your help
Klaus

 

[Rick B]

Again, you would only use one table. Why do you think that you need to move
the records to a new table?

If you want to keep your two checkboxes (Product A and Product B) then so be
it, but you don't need to move them to another table. I wonder what will
happen if you add a Product C and a Product D and later a Product E, etc.

I would put all the records in one table, then filter them based on your
checkboxes. If you need to pull all Product A customers, then filter your
records to only pull the records where that field is checked. If you want
only prospects, then pull all records where both checkboxes are not checked,
etc.

I can't see any reason to move records around in your scenario. As you
point out, doing so is causing you problems already. They will only grow as
you continue to develop a database that is not "normalized".

 

[Guest]

Sorry Rick,
you might be correct because you are the specialist. Maybe my english is not
good enough to explain everything correct - anyway,thank for trying to help
me.
Klaus