Tough Query AnyOne??

[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.

Example:

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.

 

[CajunCenturion]

What is the definition of the data structure upon which this query will execute?

Will this be against a two dimensional array within a program,

or are we dealing with a database table, and if so, what is the record structure layout ?

 

[Fbomb]

Hello,

I believe this will be a two dimensional array that will run in Access. The table is quite simple as the example is shown above. I need help writing the code to execute a query in a field.

Any Ideas?

 

[CajunCenturion]

As I understand it, you have one table with 13 columns, with the first column containing a SiteID, and the next tweleve columns containing and AdjSiteID.

If this is correct, then you have created an inherent 1 to many relationship within a specific record of a database table, which is not a good thing. In relational database terminology, its a violation of first normal form.

And the difficulties of this query is one reason why its generally a good idea to normalize a database.

My first approach would be to investigate the possiblity of a database re-design. I understand that this may not be possible due to some other application functional requirements and/or deployment and conversion issues.

Understand, that this suggestion is based soley on the above situation, but if it is possible, then I would create two tables.

The first table (SiteMaster) would contain a record for each Site containing a SiteID and a SiteName, along with other data pertinent to a site.

The second table would contain two columns and both columns would contain and SiteIDs. If the application requires reflexsivity between adjacent sites, then by definition, your current problem goes away, as the existence of a pairing in the second table places both sites adjcent to each other.

If Reflexsivity is permitted, then the relationship goes from the first column (PrimarySite) to the second column (AdjSite), and the query to find these exceptions becomes much simpler.


SiteMaster RelatedSites

2002 2002 2003
2003 2002 2004
2004 2002 2005
2005 2002 2057
2057 2003 2004
... ...


If a re-design is not an option, then you've got a real brute force problem on your hands. Maybe someone a lot smatter than I can help out.


Good Luck

 

[Fbomb]

OK, I you've got me thinking! I followed you up to the '"Reflexsivity" part. I'm not so sure what that means. Breaking up the table is not a problem at all. I can even break up the table into 13 different tables with One SiteID master and the 12 Individual adjacencies with "SideID" as a primary key in each.

Now, would you mind explaining the "Reflexsivity" part where I can compare the first column (PrimarySite) to the second column (AdjSite)? I don't quite know how to query the linked tables. I'm almost there!

TIA

Frank

 

[CajunCenturion]

As I understand it, the issue at hand is the find that is site 2003 is defined as an adjacent site to 2002, then we want to know whether or not site 2002 is an defined as adjacent to 2003.

In the existing table, this would like

2002 2003 2004 2005 ...
2003 2005 2002 2006 ...
...

The question of reflexsivity is based around the assumption that if 2003 is adjacent to 2002, then by definition, 2002 is adjacent to 2003. They both always have to be adjacent to each other.

If in the real world this is true, then the two tables would like this:

SiteMaster Related Sites

2002 2002 - 2003
2003

Only one relationship record is needed if the relationship is reflexsive. - it works both ways

However, in the real world, that may not be the case. In other words, 2003 may be adjacent to 2002, but 2002 may not be adjacent to 2003

The above example would reflect this since the relationship only works one way -- in this case from 2002 to 2003.

If the relationship is by definition NOT reflexsive, but in our case 2002 is adjacent to 2003, and 2003 is adjacent to 2002, then our tables would look as follows:

SiteMaster Related Sites

2002 2002 - 2003
2003 2003 - 2002

Here we would need both related records, because the relationship only works one way.

The first question to ask is: Can there be any site adjacent to a second site, where the second site will not be adjacent to the first site. If the answer is yes, then the relationship is by definition not reflexsive, and the Related Sites table would be one-way and contain each relationship.

If the answer is no, then our relationship is reflexsive, and we've just cut the site of the Related Sites table in half, and we will never have the problem identified in the original post.


Hope this helps

 

[Fbomb]

Let me think about this and try it first thing tomorrow. I appreciate you help greatly! Thanks for being patient and hope to return the favor, if not you, someone else in need.

Regards,

Frank