Join 3,555 readers in helping fund MetaFilter (Hide)


A Link to the Reference Table Past
August 11, 2010 11:10 AM   Subscribe

MS Access question. I have a table of references and a query that returns case numbers. Another table lists the references and case numbers that are linked to each other. I need to find a way to list the references for each case that are NOT linked to that case. Difficulty level inside.

The query gets its list of case numbers from another database, and depending on the criteria may change from time to time. Because of this, the link table is a record of connections only.

Essentially, I need to be able to list each reference for each case minus the pairs that are already matched in the link table. Either due to fatigue or lack of knowledge, I haven't been able to figure this out.

Access version is 2007 but it has to work with 2003.
posted by charred husk to Computers & Internet (6 answers total)
 
In Access the fastest way to do this is to do a left join and take the records where the linked table is null.

Create a query, join the reference table to the case number query, double click the join and make it a left join ("include ALL records in the reference table and only those..."), then drag the case number field down and type "Is Null" in the criteria.
posted by zixyer at 11:34 AM on August 11, 2010


zixyer: "Create a query, join the reference table to the case number query, double click the join and make it a left join ("include ALL records in the reference table and only those..."), then drag the case number field down and type "Is Null" in the criteria."

The issue here is the other table involved. The reference table and case number query have nothing in common, they can't be joined. There is a table that I call a "link table" that lists case numbers and references that have been associated with one another. I'm trying to list all reference/case number pairs that are not paired in the "link table".
posted by charred husk at 11:49 AM on August 11, 2010


Create a new query which joins the reference table with the "link" table; do a left outer join such that ALL values from Reference Table are returned, and only matching values from Link Table. The null values in this table will be the unpaired reference numbers you're looking for.

Create ANOTHER new query which joins the case number table with the "link" table; do a left outer join such that ALL values from Case Number Table are returned, and only matching values from Link Table. The null values in this table will be the unpaired case numbers you're looking for.
posted by julthumbscrew at 12:07 PM on August 11, 2010


Sorry, by link table I meant link QUERY. Querying on queries is fun, kids!
posted by julthumbscrew at 12:08 PM on August 11, 2010


Oh - then you probably want to do the same method, but adding a cross join into the mix.

You can create this query in SQL view, or if you want to do it in design view, you'll need two queries - one to do the cross join and one to do the filter.

(To do a cross join in design view, just show the two tables with nothing joining them.)

SELECT x_join.reference, x_join.case_number
FROM
(SELECT references, case_number
FROM references, qry_case_numbers) x_join
LEFT JOIN link_table
ON x_join.reference = link_table.reference AND
x_join.case_number = link_table.case_number
WHERE link_table.reference IS NULL

posted by zixyer at 12:14 PM on August 11, 2010


zixyer, that did the job. Thanks!
posted by charred husk at 6:56 AM on August 12, 2010


« Older What bird is featured on the R...   |  Dyeing oneself blue by ingesti... Newer »
This thread is closed to new comments.