Combining fields from multiple records

[Guest]

I have a rental property database. There are 2 tables that I am concerned
with here:

PROPERTY
PID
Street
City
State
ZIP
....

TENANT
PID
FirstName
MI
LastName
.....

The tables are linked via PID and there can be up to 4 Tenant records per
Property record. I would like to create a query that would contain the
tenant names and the address information in one record so that it can be used
as the data source for Word mail merge documents. The records should look
like:

Tenant 1
Tenant2
Tenant3
Tenant4
Street
City
State
ZIP

Is this possible? If not, is there a better solution to this?

 

[Guest]

hi,
yes it is possible. it's your basic two table select query.
you didn't say the names of your tables so i am using tb1
and tb2.
SELECT tb2.tenant, tb1.street, tb1.city, tb1.state,
tb1.zip FROM tb1 INNER JOIN tb2 ON tb1.pid = tb2.pid

change tb1 and tb2 to your table names, copy and paste
the sql into a query in sql view. run the query. if you
like it. save it for future use.

 

[Guest]

This solution does not yield the results I need. This produces a record for
each record in the tenant table, where I need a record for each record in the
property that contains all the tenant information related to that property.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

I used the concatenate function and it produced the correct query results.
However, when I tried to use the query as the data source for a mail merge,
it was not available as a possible data source. It is acting the same way as
a parameter query acts with mail merge. I then changed the query to a make
table type to see if I could use the created table as the data souce.
However, the table was created with the proper number of records, but the
concatenation was gone (only the 1st name appeared in the name field).

 

[Duane Hookom]

Are you sure only the first name appeared? Did you expand the row width to
see if more information was available? How about clicking in the field and
the pressing [Chift]+[F2]?

 

[Guest]

Completely my error. Once I expanded the row height the data was there.
Tested with the Word doc successfully. Thanks so much for your help and
patience.

Are you sure only the first name appeared? Did you expand the row width to
see if more information was available? How about clicking in the field and
the pressing [Chift]+[F2]?

--
Duane Hookom
MS Access MVP
--

I used the concatenate function and it produced the correct query results.
However, when I tried to use the query as the data source for a mail
merge,
it was not available as a possible data source. It is acting the same way
as
a parameter query acts with mail merge. I then changed the query to a make
table type to see if I could use the created table as the data souce.
However, the table was created with the proper number of records, but the
concatenation was gone (only the 1st name appeared in the name field).