Value of a field should be unique over 2 identical tables - but ho

[SB]

Hi,
I've got 2 tables - which are basically the same (same fields). Both have
one (also the same name) key. One of these tables is linked with other tables
via 1 to n relations an other has no links. This is the reason why I created
2 of them.
However when a record is added with e.g. key =AA in table 1 I want the
system to check if key=AA alredy exists in table 2 - in that case there can
be no creation of the record (and vice versa). The check should be on the
tables (no forms) and preferably no programming.

Any ideas - rules? ...

 

[Lynn Trapp]

Any ideas

Yeah -- DON'T DO THAT!

rules? ...

Yeah, a table should represent one type of entity -- People, Orders, Items,
etc. That being the case, there should not be any need for 2 tables. Now on
to your specifics:

I've got 2 tables - which are basically the same (same fields). Both have
one (also the same name) key. One of these tables is linked with other
tables
via 1 to n relations an other has no links. This is the reason why I
created
2 of them.

I'm afraid you have lost me entirely here. The idea of having 2 tables with
the same fields and having the same key doesn't make sense. Why would you
want to do that? Let me have you start by describing the business problem
you are trying to solve. I think if you will do that, you will quickly
realize there is no need for the 2 tables you are describing.

 

[SB]

Hi Lynn,

I understand your comment. Let me explain why 2 tables that are identical

Background: integration of take-over

We have a reporting structure that is defined via 1 to n relationships, the
company taken over has not - we cannot change their data structure and they
push it directly in our databases (zero control on our part). I'm not going
into the specifics of why this is the way it is - just accept it (I know I
have to).

So, now the example

Products are delivered via a plant 5501 OR <>5501 (never and)
If plant <>5501 -> we have 1-N structures grouping them if not M-N (thus no
structure at all)

I'm creating a table PRODUCTS_<>5501 for products NOT coming from 5501
I'm creating a table PRODUCTS_5501 for products coming from 5501
These are identical

The table PRODUCTS_5501 is then linked to a table grouping them (1-N), that
table is again linked to another table doing the same (2-level tree structure
grouping products). Simple! Remark: it's a bit more complex this table has
many links (bot that doesn't matter for this purpose)

The table PRODUCTS_<>5501 has no links (no grouping)

If a product is entered in PRODUCTS_5501 it cannot be entered in
PRODUCTS_<>5501 (and vice versa) that is what I want to set up in the
database structure.

A UNION of both tables still gives me a full product list (no duplicate
because of the above rule I want in the database).

Is this more clear? If you have another idea so 2 tables are not necessary I
certainly would like to know. However, you cannot touch the data coming from
the 5501 plant! (I know its ...)

Would love to hear your input

Gr.,

 

[BruceM]

Since there has been no posting in this thread since Saturday I am going to
step in for a moment to suggest that a single table have an additional field
(a yes/no field) to indicate whether or not 5501. Queries or reports may be
used for sorting and grouping data. Which one you use depends on various
details of your database, but the point is that tables are not in any case
used for sorting or grouping. Data in tables may be arranged in certain ways
when you viewing the table directly, but sorting in tables is not the way to
arrange the data in other objects such as reports.
Avoiding duplicates in a single table can be done quite readily. I cannot
address how to treat two tables as a single table for the purposes of
avoiding duplication, but assuming it is possible I very much doubt it is the
best way to solve your problem. A single table as the basis for either a
single query with multiple parameters or two or more separate queries would
very probably be a better approach.