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.
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.
Response by poster: 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
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
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
posted by julthumbscrew at 12:08 PM on August 11, 2010
Best answer: 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.)
posted by zixyer at 12:14 PM on August 11, 2010
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
Response by poster: zixyer, that did the job. Thanks!
posted by charred husk at 6:56 AM on August 12, 2010
posted by charred husk at 6:56 AM on August 12, 2010
This thread is closed to new comments.
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