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!
ASKER
@Dale,
Thanks for the suggestion. I'm so sorry, I realized after I submitted the question that I did not provide enough information at the first time, that's why I kept edit my question and end up I got it works. You were absolutely right, it's hard for your to know what I want to achieve in my query without detail explanation. Thank you so much to remind me and I will try my best to do a better job for that. As always I appreciated all your helped, I have learn a lot from you and other experts for answered my previous problems. Thank you!
also, how do I mark as my solution as I don't see anything that I can click on it.
Thanks again & have a great day
Thanks for the suggestion. I'm so sorry, I realized after I submitted the question that I did not provide enough information at the first time, that's why I kept edit my question and end up I got it works. You were absolutely right, it's hard for your to know what I want to achieve in my query without detail explanation. Thank you so much to remind me and I will try my best to do a better job for that. As always I appreciated all your helped, I have learn a lot from you and other experts for answered my previous problems. Thank you!
also, how do I mark as my solution as I don't see anything that I can click on it.
Thanks again & have a great day
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Dale,
That's what I did.
Thanks,
That's what I did.
Thanks,
ASKER
@Dale,
Thanks for your helped as always!
Thanks for your helped as always!
ASKER
CLAddress: IIf([CLStatus] = "IS", [AGYADDR],[Address]![ADRADDR])