troubleshooting Question

Query syntax in Access 2016 from different tables

Avatar of urjudo
urjudoFlag for United States of America asked on
Microsoft Access
5 Comments1 Solution27 ViewsLast Modified:
Hi Experts,
I have a query problem with address that involved with two tables.  one table is Client Address, and other table is Agency Table and the addresses in both tables are base on Client Status.  If Client status is IS the we don't enter the address in Client table, we enter the address in Agency table. The status is on another table ClientTable. Now I have a query need to pull the client address include the agency if the Status is IS, then show the Agency address from the Agency table otherwise show the client address from the Client address table.  Is anyway can do that?

ClientTable: CLStatus = IS

Client addressTable name:    
Address: ADRADDR, ADRCity, ADRST, ADRZip

  example: John Doe    1234 main st     new york   NY   10001     CLStatus: PD   recordID 001
                   Mary Joe Smith       RecordID 002
  (John Doe doesn't have any address in the Agency address table, only in the Client address table)
   

 agency address table name:
  tblAgency: AGRADDR, AGYCITY, AGYST, AGYZip

     Example: Mary Smith      PO Box 123        Madison    WI      53965      CLstatus: IS     RecordID 002
     (mary smith doesn't have any address in the Client address table, only in the Agency Address table)

How can I do in the query that show:
CL Name             CLAddress                                       CLCity              ClSt             CLZip
John Doe             1234 Main st                                    New York        NY                10001            recordID 001
Mary Smith          PO box 123  mary Doe Smith       Madison         WI                 53065            recordID 002

 in the same fields: that if ClStatus is IS then show the Agency otherwise show the ClientAddress.  

Here is my Syntax:
CLAddress: IIf([CLStatus] = "IS", [AGYADDR] & ", " & [[Address]![ADRADDR]  but is seems the CLAddress also capture extra data in for the ClStatus IS record if someone enter something on the ClientAddress table (see above) , it will show like the query above, how do I remove the mary doe smith in the field of the query?

Many thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros