Comparing 2 fields from 2 different tables

[woodyinoz]

Hi all,

I have 2 seperate tables, table1 and table2. These are linked by a unique id number in table2, this is table2's key field. Table1 has no unique fields and therefore no key field.

Table1 holds a list of items and Table2 holds the material that those items will be cut from. Not all items in Table1 have been assigned a material id yet. This is were my problem lies, I need to make sure that the thickness of material if the same as the thickness of item to be cut out of it when the item is assigned a particular material id.

There are many pieces of material the same thickness and many items of the same thickness and the only unique field in either table is the materialid in Table2.

Can anyone help me on this?

Thanks,

Woody.

 

[footpad]

Woody,

Remember that primary keys can consist of multiple tables. Thus, you may not have one field that's unique, but the combination of two (or three) field values may yield a unique value within the unkeyed table.

If that's not the case, then you punt by creating an arbitrary key on the second table and then create secondary indexes to use a linking fields within forms and report.

As far as finding the records that have different thicknesses for the same material, that's generally called a "not-in" query, for you want to see the records that aren't in the control (or lookup) table.

There's a pretty comprehensive FAQ available at

http://www.thedbcommunity.com/faq/18.htm

that descrtibes several variations on not-in queries. I believe you're after one of those.

Hope this helps...

-- Lance

 

[woodyinoz]

Lance,

I don't actually want to search for different thicknesses, I don't want the user to be able to assign an item of say 30thk to a plate 25thk in the 1st place. I want Paradox to look at it and say "The plate and item thicknesses are different" -ERROR.

 

[Guest_imported]

This doesn't sound like a high volume, large user app, so a clunky solution may be ok.

If you have the thickness as a field in both table1 and table2, the options are:

Option1: place thickness fields from both tables on the form (uncheck the run time visible property). Do the comparison in a bit of code somewhere (perhaps on a validation pushbutton)?

Option2: When the user picks the item to be made, do a setFilter() or setGenFilter() for table2, based on the thickness from table1.

Option3: Make table2 have a composite index of uniqueID and thickness. Link table1 and table2 on the composite index.