Pass through query

[manolakshman]

I have two queries A,B. Query B is a subset of Query A. Now I need a syntax
to get Query A- Query B i.e., Query A without data of Query B
Can anyone help me on this.

I think its a pass through query if am not wrong

 

[John Spencer]

No that is not a pass-through query. It is an unmatched query -using the
query A and Query B as the "tables". The unmatched query wizard should be
able to build that for you if records can be matched on one field - Select New
for a query and select the Find unmatched query Wizard option.

Assuming that you have a primary key field (PK) in both queries that can be
used for a matchup. The SQL statement for the desired result would look like

SELECT A.*
FROM [Query A] as A LEFT JOIN [Query B] as B
ON A.PK = B.PK
WHERE B.PK is Null

If there are multiple fields involved in matching records between the two,
post back for an explanation on how to set up the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[manolakshman]

Thanks for the response. As you said I have multiple records in the both the
queries.

No that is not a pass-through query. It is an unmatched query -using the
query A and Query B as the "tables". The unmatched query wizard should be
able to build that for you if records can be matched on one field - Select New
for a query and select the Find unmatched query Wizard option.

Assuming that you have a primary key field (PK) in both queries that can be
used for a matchup. The SQL statement for the desired result would look like

SELECT A.*
FROM [Query A] as A LEFT JOIN [Query B] as B
ON A.PK = B.PK
WHERE B.PK is Null

If there are multiple fields involved in matching records between the two,
post back for an explanation on how to set up the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have two queries A,B. Query B is a subset of Query A. Now I need a syntax
to get Query A- Query B i.e., Query A without data of Query B
Can anyone help me on this.

I think its a pass through query if am not wrong

 

[John Spencer]

I said nothing about multiple records. IF you must match on multiple FIELDS
then you can't use the query wizard. It only finds records with no match
based on one field in table a matching one field in Table B.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks for the response. As you said I have multiple records in the both the
queries.

No that is not a pass-through query. It is an unmatched query -using the
query A and Query B as the "tables". The unmatched query wizard should be
able to build that for you if records can be matched on one field - Select New
for a query and select the Find unmatched query Wizard option.

Assuming that you have a primary key field (PK) in both queries that can be
used for a matchup. The SQL statement for the desired result would look like

SELECT A.*
FROM [Query A] as A LEFT JOIN [Query B] as B
ON A.PK = B.PK
WHERE B.PK is Null

If there are multiple fields involved in matching records between the two,
post back for an explanation on how to set up the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have two queries A,B. Query B is a subset of Query A. Now I need a syntax
to get Query A- Query B i.e., Query A without data of Query B
Can anyone help me on this.

I think its a pass through query if am not wrong

 

[manolakshman]

I think am not able to convey properly my prob.
I have 2 queries A & B. Query B is a sub set of quey A, neither of the
queries have primary fields. In such a case how do u get Query A - Query B.

I said nothing about multiple records. IF you must match on multiple FIELDS
then you can't use the query wizard. It only finds records with no match
based on one field in table a matching one field in Table B.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks for the response. As you said I have multiple records in the both the
queries.

No that is not a pass-through query. It is an unmatched query -using the
query A and Query B as the "tables". The unmatched query wizard should be
able to build that for you if records can be matched on one field - Select New
for a query and select the Find unmatched query Wizard option.

Assuming that you have a primary key field (PK) in both queries that can be
used for a matchup. The SQL statement for the desired result would look like

SELECT A.*
FROM [Query A] as A LEFT JOIN [Query B] as B
ON A.PK = B.PK
WHERE B.PK is Null

If there are multiple fields involved in matching records between the two,
post back for an explanation on how to set up the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

manolakshman wrote:
I have two queries A,B. Query B is a subset of Query A. Now I need a syntax
to get Query A- Query B i.e., Query A without data of Query B
Can anyone help me on this.

I think its a pass through query if am not wrong

 

[John Spencer]

HOW do you know that a record in Query A matches a record in query B? Once we
know that then we can do the opposite and return all the records that are in
Query A that are not in Query B.

Simple example
Query A has 10 fields
FirstName
LastName
DateOfBirth
City
State
ZIP
LastVisit

QueryB has the same fields

To get the records in A that are not in B based on FirstName, LastName, and
DateOfBirth, you would build a query like the following

SELECT A.*
FROM [Query A] as A LEFT JOIN [Query B as B]
ON A.FirstName = B.FirstName
AND A.LastName = B.LastName
AND A.DateOfBirth = B.DateOfBirth
WHERE B.LastName is Null

If you can't match up the records between the databases based on 10 or fewer
fields then you have a problem that may not be solvable in Access without the
use of some VBA. But you do have to have a way of matching a record in table
A to one (or more) records in Table B - otherwise ALL the records in query A
have no match in query B.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I think am not able to convey properly my prob.
I have 2 queries A & B. Query B is a sub set of quey A, neither of the
queries have primary fields. In such a case how do u get Query A - Query B.

I said nothing about multiple records. IF you must match on multiple FIELDS
then you can't use the query wizard. It only finds records with no match
based on one field in table a matching one field in Table B.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks for the response. As you said I have multiple records in the both the
queries.

:

No that is not a pass-through query. It is an unmatched query -using the
query A and Query B as the "tables". The unmatched query wizard should be
able to build that for you if records can be matched on one field - Select New
for a query and select the Find unmatched query Wizard option.

Assuming that you have a primary key field (PK) in both queries that can be
used for a matchup. The SQL statement for the desired result would look like

SELECT A.*
FROM [Query A] as A LEFT JOIN [Query B] as B
ON A.PK = B.PK
WHERE B.PK is Null

If there are multiple fields involved in matching records between the two,
post back for an explanation on how to set up the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

manolakshman wrote:
I have two queries A,B. Query B is a subset of Query A. Now I need a syntax
to get Query A- Query B i.e., Query A without data of Query B
Can anyone help me on this.

I think its a pass through query if am not wrong