Re: Combining fields from multiple records

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: otisg1 (otisg1_at_discussions.microsoft.com)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 05:57:02 -0800

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" wrote:

> There is a generic concatenate function with sample usage at
> http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "otisg1" <otisg1@discussions.microsoft.com> wrote in message
> news:CE34D2DD-7DA1-44C1-AFC4-C5007E983715@microsoft.com...
> > 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.
> >
> > "anonymous@discussions.microsoft.com" wrote:
> >
> >> 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.
> >>
> >>
> >> >-----Original Message-----
> >> >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?
> >> >.
> >> >
> >>
>
>
>

Relevant Pages

  • Re: User Data to Create a Custom Query
    ... On Jul 26, 10:54 pm, Duane Hookom ... allows users to select a "data source" query, fields, sorting, filtering, ... subset of data which would then be fed to a graph. ... I have query A, B and C ...
    (microsoft.public.access.queries)
  • RE: Concatenate function from a Query, not a table
    ... I will hard code the date and then reference the query to see if that is the ... "Duane Hookom" wrote: ... is to provide the user with vehicle allocation information per vehicle, ... Each time the concatenate function is called, ...
    (microsoft.public.access.queries)
  • RE: Concatenate function from a Query, not a table
    ... is to provide the user with vehicle allocation information per vehicle, ... For this particular query, to summarise all the drop points ... "Duane Hookom" wrote: ... Each time the concatenate function is called, ...
    (microsoft.public.access.queries)
  • RE: Concatenate function from a Query, not a table
    ... I will hard code the date and then reference the query to see if that is the ... "Duane Hookom" wrote: ... is to provide the user with vehicle allocation information per vehicle, ... Each time the concatenate function is called, ...
    (microsoft.public.access.queries)
  • Re: Automate opening a merge with Mailmerge Recipients open
    ... To disconnect the data source, change the type of document from a mailmerge document to a normal Word document and then change it back to the desired type of mail merge main document. ... As far as the "field mapping" is concerned, if by that it can be implied that you are not using the actual names of the fields from the data source, then it would be far better if you did use the data source field names directly as then no mapping would be required. ... datasource linked to the query, only now the query has been changed. ...
    (microsoft.public.word.mailmerge.fields)