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.

Sample db:

SiteID1 AdjacentID1 AdjacentID2 ........AdjacentID12
SiteID2 AdjacentID1 AdjacentID2 ........AdjacentID12
SiteID3 AdjacentID1 AdjacentID2 ........AdjacentID12
.
.
.
SiteIDn AdjacentID1 AdjacentID2 ........AdjacentID12

Can anyone help me write a VBA app that will do this?


Kindest Regards,

FB

 

[THoey]

It is not really clear what the query should do. Can you please give some example data and the expected results? Terry

 

[Fbomb]

Terry,

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
2004 2057 2005 2007 2002 2003 2008
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.




Does this make sense?

 

[THoey]

Makes sense. Can I suggest something? Instead of have the table the way you have it, how about the following:

tblSiteAdjacency
SiteID
AdjSiteID

This table would have multiple records for each site:

SiteID   AdjSiteID
2002     2003
2002     2004
2002     2005
2003     2002
2003     2005

If you want to have a primary key on this table, make it based on both fields. By doing it this way, You only have to query down the table, not down and across. This query is not going to be an easy one to write, I would try not to make it any more difficult. (Then again, there may be another reason it is this way).

I'll go play with a demo and see what I can come up with...

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Fbomb]

Thanks Terry! I like your "tblSiteAdjacency" suggestion. Basically the table can be any format as long as the correspondence of the Fields does not change.

Let me know what you come up with.


Thanks Again,
Frank

 

[Fbomb]

Anybody else have input???