Query? SQL? help!

[Mrs.Smith]

How do I collect and calculate data from two contacts tables (lets call them Contacts1 and Contacts2) that are not related except for their relationship with a third table (ParticipationTable)?

I need to list all participants on the Participation (omitting duplicate entries - because any given contact can have multiple entries on the participation table), and I need to list all non-participants (from both contact tables - Contacts1 and Contacts2) that are NOT on the ParticipationTable. And i need to create a percentage of contacts (by site, which is a field in each of the contacts tables) for how many have participated (in the participation table). I'm having difficulty getting my head around this...maybe I've been staring at it too long. I know I need a SELECT SQL Query, but i'm just not sure how to write it to get the information I want. Or maybe there's a better way... Any guidance would be appreciated! :banghead:

 

[Mrs.Smith]

... Maybe i just need a union select query with a where clause.... ?

 

[Pat Hartman]

A union query may solve the immediate problem but the larger one is why do you have two tables for contacts? Shouldn't all the contacts be in the same table perhaps with a code to identify whether they are type 1 or type 2.

 

[Mrs.Smith]

Hi Pat,

Yes, i know it's bad form, I mentioned exactly what you said to the owner of the database earlier this week. I didn't design that aspect of the database - just trying to keep the data intact and avoid a complete redesign for the owner ... it's definitely not ideal to work with though. :s

 

[Mrs.Smith]

I figured out the sql statment for contacts1 for both involved and not involved participants - got the contacts2 sql statment for involved, but not uninvolved - for some reason with uninvolved sql i'm having a mismatched datatype issue.. :s any way around that?

 

[Pat Hartman]

The correct solution is to fix the tables to make the data types the same. If you can't do that, use functions in the select queries to fix the data.

 

[Mrs.Smith]

Hi Pat,

Yes, i know the correct solution, but as I mentioned before, i cant change the way it's set up. But it's ok - I actually figured it out earlier this morning. All worked out. Thank you though!

I created a query based off a query and a table with an inner join on last name, unique records (to omit duplicate ID's, but not omitting duplicate last names), and an if null statement to get the non-participants to display.

Took a bit of concentrated thought to get to where i needed to get, but it worked.