Query for same field in main and subform

[Natalie]

I have no idea if this is possible:

I have a main table containing the following fields:
User ID(primary)
Company
Address
Status
Oil and Grease 1(Yes/No)

I also have a subform based on a query that combines the above table and a
tenant table that contains the following fields:
Tenant ID (primary)
Tenant Name
Suite #
Oil and grease 2 (Yes/No)
User ID (above table)
Company (above table)
Address (above table)
Status (above table)


I want to create a new form that pulls all the Oil and grease that are
checked Yes from both tables and spit out the address. If the Oil and Grease
1 is checked Yes then the User ID and address should display also, if Oil and
Grease 2 is checked yes, I want it to spit out the address along with the
suite #. Basically, I need to get all the all the checked Oil and Grease
boxes onto one form so someone can search for a facility that is part of Oil
and Grease.

Thank you for your help,
Natalie

 

[Michel Walsh]

That is not a complete solution, more just a start, ... simply because I am
not sure I understand the whole thing.

It seems you need a full outer join, but Jet does not support them directly,
Many solutions exist, one of which is to make two queries. The first one is
about to get all the possible values for the join. Here I assume that [User
ID] is the field to be used to detect matches, so, my first query will be:

SELECT [User ID] AS userID FROM mainTable
UNION
SELECT [User ID] FROM subTable


save it as qAllUserID, as example (or maybe you already have such a table,
listing all user id, no dup.) .


Next, to make the full outer join:

SELECT mainTable.[User ID] , mainTable.[Oil and Grease 1] AS oag1,
subTable.[User ID], subTable.[Oil and Grease 2] AS oag2
FROM (qAllUserID LEFT JOIN mainTable ON qAllUserID=mainTable.[User ID])
LEFT JOIN subTable ON qAllUserID=sub.[User ID]



which can now list yes, no AND null under the fields oag1 and oag2. If any
one is now NULL, it is because there is no match, for the table the value
would have come from, but also are the other fields (for the same table),
so, as example, you can add, in the SELECT clause:


Nz( mainTable.Address, subTable.Address)


which will return the address from the mainTable unless the main table was
without match, then, the above expression return subTable.Address. Or you
can also use:

SWITCH( oag1, mainTable.Address, oag2, subTable.Address, true, null )

which returns the address from mainTable is oag1 is true (checked) and
matched, if not, address from subTable if oag2 is true (checked) and
matched, else, returns null.

As you could see, that is the point where I am not sure I understood your
problem, but I hope it can help you to progress in your solution.



Vanderghast, Access MVP

 

[Natalie]

Thank you,
This was a big help. A simple Union query has been working so far, I am
still exploring the full outer join.

--
Natalie

That is not a complete solution, more just a start, ... simply because I am
not sure I understand the whole thing.

It seems you need a full outer join, but Jet does not support them directly,
Many solutions exist, one of which is to make two queries. The first one is
about to get all the possible values for the join. Here I assume that [User
ID] is the field to be used to detect matches, so, my first query will be:

SELECT [User ID] AS userID FROM mainTable
UNION
SELECT [User ID] FROM subTable


save it as qAllUserID, as example (or maybe you already have such a table,
listing all user id, no dup.) .


Next, to make the full outer join:

SELECT mainTable.[User ID] , mainTable.[Oil and Grease 1] AS oag1,
subTable.[User ID], subTable.[Oil and Grease 2] AS oag2
FROM (qAllUserID LEFT JOIN mainTable ON qAllUserID=mainTable.[User ID])
LEFT JOIN subTable ON qAllUserID=sub.[User ID]



which can now list yes, no AND null under the fields oag1 and oag2. If any
one is now NULL, it is because there is no match, for the table the value
would have come from, but also are the other fields (for the same table),
so, as example, you can add, in the SELECT clause:


Nz( mainTable.Address, subTable.Address)


which will return the address from the mainTable unless the main table was
without match, then, the above expression return subTable.Address. Or you
can also use:

SWITCH( oag1, mainTable.Address, oag2, subTable.Address, true, null )

which returns the address from mainTable is oag1 is true (checked) and
matched, if not, address from subTable if oag2 is true (checked) and
matched, else, returns null.

As you could see, that is the point where I am not sure I understood your
problem, but I hope it can help you to progress in your solution.



Vanderghast, Access MVP

I have no idea if this is possible:

I have a main table containing the following fields:
User ID(primary)
Company
Address
Status
Oil and Grease 1(Yes/No)

I also have a subform based on a query that combines the above table and a
tenant table that contains the following fields:
Tenant ID (primary)
Tenant Name
Suite #
Oil and grease 2 (Yes/No)
User ID (above table)
Company (above table)
Address (above table)
Status (above table)


I want to create a new form that pulls all the Oil and grease that are
checked Yes from both tables and spit out the address. If the Oil and
Grease
1 is checked Yes then the User ID and address should display also, if Oil
and
Grease 2 is checked yes, I want it to spit out the address along with the
suite #. Basically, I need to get all the all the checked Oil and Grease
boxes onto one form so someone can search for a facility that is part of
Oil
and Grease.

Thank you for your help,
Natalie