Query for Reciprocating Data Fields

[Fbomb]

I am challenged with trying to compare data from a row of inputs with another row of inputs for a reciprocating match. Basically, I have a row of data with the first cell being the let's say "SiteIDs" and up to 12 cells that on the same row that are affiliated to the "SiteIDs" that are called “AdjacentIDs”. I need to find which "SiteIDs" are in each others database or Row in this case.

OK, here goes. Here is a sample from the actual database.

SiteID ADJ1 ADJ2 ADJ3 ADJ4 ADJ5 ADJ6
2002 2003 2004 2005 2057 2007 2008
2003 2002 2005 2004 2007 2057 2008[/b]
2004 2057 2005 2007 2002 2003 2008[/b]
2005 2007 2008 2004 2002 2003 2057

I want to Identify all Sites that are not in each other's adjacency list. Example: Site 2002 has Site 2003 in its list, But is Site 2003 in Sites 2002s list as well? In this case yes, but I want to run a query that queries the whole array.

 

[Tranman]

Fbomb,
I can see how you could do this, but not with a single query. How about a single function?

What I would do is to query all of the table into a recordset and set up a "Do while not eof loop" that hit each row one at a time. Inside that do loop, would be another do loop that hit each column of that row, one at a time. In the inner loop would be another query that went something like select * from table where siteid = currentfieldvalue and ADJ1 = current siteid or ADJ2 = currentsiteid or ADJ3....

You could then just look at EOF for the second recordset. If it is true, there was no reciprocal match, and you could record that somewhere like maybe append a digit or an asterisk or whatever to the end of the current field to indicate that the match was not reciprocated.

This looks like fun, and I'd be willing to work with you on it if you haven't solved it yet. I'm guessing it would not take more than 1 hour to write, but I don't want to waste that hour if you have already solved the problem (This post is about 2.5 weeks old).

Let me know.

Paul

 

[GloverK]

Fbomb, if you get this message, pls contact me. I am working on something similar with 700,000 rows and 14 columns of data. I have the basic principle of how to do it using strings, sql and loops but need a way to make it faster.......

 

[pendragons10]

I'm not sure if this will deliver but It looks very similar - Access provides a crosstab query which works like a pivot table chech out the TRANSFORM statement. You'll find a good explanation at

http://www.bris.ac.uk/is/services/software/packages/access/#introdoc

Best of luck!