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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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? ...
 
Any ideas

Yeah -- DON'T DO THAT!
rules? ...
Click to expand...

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.
Click to expand...

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.
 
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.
 
Back
Top