Need Help with Complex Query Involving Using ConcatRelated on Two Pre-Concatenated Fields

[jlowder]

Here are the relevant tables and columns:

tblAccounts:
AccountID (PK, autonumber)
Lot (FK, number)
Is_Current_Owner (Yes/No)

tblAccountsContactsmm:
ID (PK, autonumber)
Account_ID (FK, Short Text)
Contact_ID (FK, Number)

tblContacts:
Contact_ID (PK, AutoNumber)
Contact_Type (Short Text)
Last_Name (Short Text)
First_Name (Short Text)
Address_ID (FK, Number)

tblAddress:
Address_ID (PK, AutoNumber)
Street1 (Short Text)
Street 2 (Short Text)
City (Short Text)
State (Short Text)
Postal Code (Short Text)

I want a query to produce the following columns, for a specified Lot number (I'm assuming the Lot will need to be a parameter):
Account_ID (limited to records where Is_Current_Owner=Yes)
Owner1 (formed by Concatenating Last_Name & ", " & First_Name for the first linked Contact record found, as long as Contact_Type="Owner")
Owner2 (formed by Concatenating Last_Name & ", " & First_Name for the second linked Contact record found, if it exists, as long as Contact_Type="Owner")


Street1 (for Owner1)
Street2 (for Owner1)
City (for Owner1)
State (for Owner1)
Postal Code (for Owner1)

How do I do this? I'm familiar with the ConcatRelated function, but I don't know how to do that when I need to pull two columns from a table, concatenate them together, and then use the concatenated string as an input to ConcatRelated).

Thanks in advance for your help.

Jeff

[davegri]

First off, Account_ID in tblAccountsContactsmm should be Number.
Second, if a Contact can have only one address, you can eliminate tblAddress by adding those fields to tblContacts.

Next, the meaning of first contact record and second contact record is ambiguous. What determines that?

[jlowder]

First off, Account_ID in tblAccountsContactsmm should be Number.

Account_ID is an alphanumeric field, not a number.

Second, if a Contact can have only one address, you can eliminate tblAddress by adding those fields to tblContacts.

Contacts can have multiple addresses (physical and mailing address).

Next, the meaning of first contact record and second contact record is ambiguous. What determines that?

The ordering is unimportant. All that is important is being able to get both of them onto the same row in a query.

[davegri]

Account_ID is an alphanumeric field, not a number.

But you indicate that it's the foreign key in an MTM linking table. The AccountID PK in tblAccounts is an autonumber.

Contacts can have multiple addresses (physical and mailing address).

OK, good reason for separate table.

All that is important is being able to get both of them onto the same row in a query.

This is unusual. Can you explain the purpose of such a query? What if a lot contains more than 2 contacts?

[June7]

I am also confused. tblContacts has only one Address_ID field, therefore contact can have only one address. If contact can have 2 and only 2 addresses (physical and billing) then have two address foreign key fields in tblContacts: AddressPhysical, AddressBilling.

If you want to provide db for analysis and testing, follow instructions at bottom of my post.

Agree with Dave, data types for PK and FK fields are inconsistent.

[jlowder]

But you indicate that it's the foreign key in an MTM linking table. The AccountID PK in tblAccounts is an autonumber.

I see your point. That was a typo. I just checked the table design. AccountID PK in tblAccounts is Short Text.

OK, good reason for separate table.

Thx

This is unusual. Can you explain the purpose of such a query? What if a lot contains more than 2 contacts?

Yes. If the user needs to generate a notice or letter to the Lot Owners associated with an account, for Mail Merge purposes it would be easier if both Lot Owners, assuming there are 2, showed up on the same row.

[Peter M]

Do you want to convert:

Account_ID Owner Address
100 Andrea New York
100 Betty Oregon
101 Claire California


To
Account ID Owner(s) Address
100 Andrea, Betty New York
101 Claire California

[Peter M]

Do you care if there are multiple columns for the owners?

[jlowder]

I am also confused. tblContacts has only one Address_ID field, therefore contact can have only one address. If contact can have 2 and only 2 addresses (physical and billing) then have two address foreign key fields in tblContacts: AddressPhysical, AddressBilling.

I see your point. A contact can have either one or two addresses. If they have only one address, the assumption is that the physical address is identical to the mailing address. If they have two addresses, then the mailing address is different from the physical address.
There is another table, tblLots, which has the physical address of a Lot. (I know I am breaking normalization by having addresses stored outside of the Addresses table, but the Lots have identical values for City State, Country, and I wanted the Lot street addresses stored somewhere separate from the other addresses, for access control purposes. The table, tblLots, has fields for Street1 and Zip, but not the other address fields).

If you want to provide db for analysis and testing, follow instructions at bottom of my post.

Thanks. Let me think about this and get back to you.