returning multiple records on a single line

[Guest]

Good Afternoon,

I'm trying to write a query that performs a SQL join and returns all the
records from a linked table on a single line. Ultimately, I want an ASCII
file with the information but have considered writing a queries, reports,
HTML exports, etc. as intermediate steps to reach my end goal.

For example the join links two tables based on the ID fields in each of the
two tables. Some records in the first table will have several (up to 5 or 6
at most) records linked to it. I want to return those records from the linked
table on the same line as the fields selected from the first table instead of
one row for each match.

Here's the SQL statement I'm using. It does return the correct results, just
not on the same row.

select distinct {bunch of field names}
from {table1, table2}
where {table1.id = table2.id}
order by table1.id

Table 2 may return multiple results and those are what I want placed on a
single line.

How can I do this?

Thanks,
Scott

Office 2007
Windows XP

 

[Guest]

Scott,

check out this function at the Access Web

(http://www.mvps.org/access/modules/mdl0008.htm)

 

[Guest]

thanks. Does that get placed in a module? How and where do I palce that in my
database?

Thanks,
Scott

 

[Dale Fye]

You would place the function in a module, and would call it as a field in
your query.

Dale