compare two tables

[nopposan]

Hi, this is my first post here. 'Complete newb.

I'm learning as I go on a project here at my workplace. It's fun but I do get stuck sometimes.

'Trying to figure out how to compare the list of identifiers provided as a field in every record of one table to the same ID field in a related table; i.e., I'd like to get found sets that could be represented by a Venn diagram. . . common, list A exclusive, list B (from separate related table) exclusive.

Please give me any guidance you can.

Thanks.

Edited March 24, 2008 by Guest

[FestiveEmbalmer]

Trying to figure out how to compare the list of identifiers provided as a field in every record of one table to the same ID field in a related table

I am not sure exactly what you mean, more context would help. If you are comparing two groups of people, for example, then you might want to have one table not two. Are the tables related already or do they just share attributes? For example, People and Equipment can both belong to the circus but they may not be related for your purposes, so "circus" is just a value in a category field that they happen to share. Are you wanting to be able to enter a value on a layout to determine what records share that value in multiple tables or do you need to find all other records that share the values in the record you are looking at?

I'm pretty sure what you are trying to do can be accomplished pretty easily with the right relationships but it would help if you could describe your specific situation.

matt

[nopposan]

I am not sure exactly what you mean, more context would help. If you are comparing two groups of people, for example, then you might want to have one table not two. Are the tables related already or do they just share attributes? For example, People and Equipment can both belong to the circus but they may not be related for your purposes, so "circus" is just a value in a category field that they happen to share. Are you wanting to be able to enter a value on a layout to determine what records share that value in multiple tables or do you need to find all other records that share the values in the record you are looking at?

I'm pretty sure what you are trying to do can be accomplished pretty easily with the right relationships but it would help if you could describe your specific situation.

matt

Thanks, Matt. I am sorry for being less than clear. I have two tables that I've related by a match field equanimity TableA::ID = TableB::ID. I just want to find out which ID's are common between the two tables and which ID's are exclusive to each table. I'm not concerned with a query form for this right now; I'll be happy to do it just using the "find" feature in a typical column/row table layout. Yes, I suppose I'm trying to be able to enter a value on a layout to determine what records share that value in multiple tables; I need to find out whether one table contains ID's in common with another and which ID's those are.

Thanks again.

Edited March 25, 2008 by Guest

[mr_vodka]

If you put a copy of the related table's field on the parent layout, then do a find for * in it, it will return all records that have a matching related record.

To show the records in the parent table that did NOT have a match, just show the Omitted records.

To show the records in the child table that do not have a match to the parent table, then either go to the layout of the child table occurence and do the same two steps, or you can do a Go to related record script step starting from the parent TO layout and when you get the found set of matching child records, then show Omitted Only.

[nopposan]

Thank you! That's very useful. 'Wish I'd thought of it myself . . . but oh well.

Cheers.

[nopposan]

Interesting that it doesn't work if I try to search directly for empty records in the related table by using an equal sign, =, followed by nothing.

'Guess that makes sense though, as there aren't any records in the related table that have that field empty.

I'm so brainy.

}:(