Avatar of urjudo
Flag for United States of America

asked on 

Query syntax in Access 2016 from different tables

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:    

  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:

     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!
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon