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:
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!
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!
Microsoft Access
Last Comment
urjudo