pulling data from different tables depending on criteria

[Basil via AccessMonster.com]

I am creating an address list for parents (billto) of children (patient). If
there is an address for the parent, I want to include that address in the
list, otherwise if there is no address for the parent, then I need to include
the child's address. (i.e. If billto.address1 is NOT NULL, then pull address
fields from billto table. Else If billto.address1 is Null, then pull address
fields from patient table.)

Is this possible to code in a single query? Not sure how to code this.

 

[Allen Browne]

The optimal solution will depend on how your tables fit together.

What is the relationship between your Parent and Child tables? Is this a
one-to-many (one parent can have many children), or a many-to-many resolved
through a ParentChild table?

If one-to-many, you can probably create a query that contains the Parent and
Child tables. Double-click the line joining the 2 tables in query design,
and choose option 2 or 3 (i.e. use an outer join in case the parent record
has not been entered.) Now that you have both fields in the query, you can
enter an expression like this:
Address: Nz(billto.address1, patient.address1)

If many-to-many, there could be multiple parents' addresses to choose from,
but it would be possible with a DLookup() or a subquery. For help with a
DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

For an introduction to subqueries, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[John Spencer]

You should be able to do this with the NZ function and a join of the two
tables.

SELECT Distinct NZ(Billto.Address1,Patient.Address) as MailingAddress
FROM BillTo INNER JOIN Patient
ON BILLTo.ParentID = Patient.ParentID

If the Patient has multiple addresses and the BillTo has no address, you
could end up with multiple records with differing addresses.

 

[Basil via AccessMonster.com]

thanks, I'll try the Nz function.

The optimal solution will depend on how your tables fit together.

What is the relationship between your Parent and Child tables? Is this a
one-to-many (one parent can have many children), or a many-to-many resolved
through a ParentChild table?

If one-to-many, you can probably create a query that contains the Parent and
Child tables. Double-click the line joining the 2 tables in query design,
and choose option 2 or 3 (i.e. use an outer join in case the parent record
has not been entered.) Now that you have both fields in the query, you can
enter an expression like this:
Address: Nz(billto.address1, patient.address1)

If many-to-many, there could be multiple parents' addresses to choose from,
but it would be possible with a DLookup() or a subquery. For help with a
DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

For an introduction to subqueries, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I am creating an address list for parents (billto) of children (patient).
If

[quoted text clipped - 8 lines]

Is this possible to code in a single query? Not sure how to code this.

 

[Basil via AccessMonster.com]

Here is an excerpt of my logic:

SELECT DISTINCT BillTo.BILLTONUM, BillTo.NAME, NZ(BillTo.STREET1,Pt.STREET1)
AS Street1, NZ(BillTo.STREET2,Pt.STREET2) AS Street2, NZ(BillTo.CITY,Pt.CITY)
AS City, NZ(BillTo.STATE,Pt.STATE) AS State, NZ(BillTo.ZIP,Pt.ZIP) AS Zip
FROM …

This logic works, however, some billto records have only some of the address
fields populated. Resulting in a mixed address of some fields from billto
and some from pt (i.e. pt.street1, pt.street2, with billto.city, billto.state,
and billto.zip which is not a valid address). How to I ensure all the
address fields come from either one or the other table based on whether or
not street1 is populated in billto?

You should be able to do this with the NZ function and a join of the two
tables.

SELECT Distinct NZ(Billto.Address1,Patient.Address) as MailingAddress
FROM BillTo INNER JOIN Patient
ON BILLTo.ParentID = Patient.ParentID

If the Patient has multiple addresses and the BillTo has no address, you
could end up with multiple records with differing addresses.

I am creating an address list for parents (billto) of children (patient).
If

[quoted text clipped - 8 lines]

Is this possible to code in a single query? Not sure how to code this.

 

[Allen Browne]

To test if all the values are null, concatenate them together and see if the
result is null.

You can do the concatenation for each of your address fields, but it would
be more efficient to do it in one go like this:

SELECT BillTo.BILLTONUM, BillTo.NAME,
IIf(IsNull(BillTo.STREET1 & BillTo.STREET2 & BillTo.CITY & BillTo.STATE &
BillTo.ZIP),
Pt.Street1 + Chr(13) + Chr(10) &
Pt.Street2 + Chr(13) + Chr(10) &
Pt.CITY + Chr(13) + Chr(10) &
Pt.State + Chr(13) + Chr(10) & Pt.ZIP,
BillTo.Street1 + Chr(13) + Chr(10) &
BillTo.Street2 + Chr(13) + Chr(10) &
BillTo.CITY + Chr(13) + Chr(10) &
BillTo.State + Chr(13) + Chr(10) & BillTo.ZIP) AS Address
FROM ...

Note the different concatenation operators:
"A" & Null = "A"
"A" + Null = Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is an excerpt of my logic:

SELECT DISTINCT BillTo.BILLTONUM, BillTo.NAME,
NZ(BillTo.STREET1,Pt.STREET1)
AS Street1, NZ(BillTo.STREET2,Pt.STREET2) AS Street2,
NZ(BillTo.CITY,Pt.CITY)
AS City, NZ(BillTo.STATE,Pt.STATE) AS State, NZ(BillTo.ZIP,Pt.ZIP) AS Zip
FROM .

This logic works, however, some billto records have only some of the
address
fields populated. Resulting in a mixed address of some fields from billto
and some from pt (i.e. pt.street1, pt.street2, with billto.city,
billto.state,
and billto.zip which is not a valid address). How to I ensure all the
address fields come from either one or the other table based on whether or
not street1 is populated in billto?

You should be able to do this with the NZ function and a join of the two
tables.

SELECT Distinct NZ(Billto.Address1,Patient.Address) as MailingAddress
FROM BillTo INNER JOIN Patient
ON BILLTo.ParentID = Patient.ParentID

If the Patient has multiple addresses and the BillTo has no address, you
could end up with multiple records with differing addresses.

I am creating an address list for parents (billto) of children (patient).
If

[quoted text clipped - 8 lines]

Is this possible to code in a single query? Not sure how to code this.