Select multiple rows into a single string for a Query

[Andydr]

Hi I need some help with a query.
I have got 2 tables VACANCY and VACANCY_LOCATIONS linked as
VACANCY.[vac:vacancy_id] to VACANCY_LOCATIONS.[vacloc:vacancy_id]
There is a one to many relationship between the tables, ie for each record in VACANCY there could be multiple rows in VACANCY_LOCATIONS. This table holds a list of Cities [vacloc:city] for each VACANCY.
I am trying to write a query that selects specific fields from VACANCY and also to select into a single output string all the cities in the VACANCY_LOCATIONS table.
The query is then going to be used for a mail merge into Word, so obviously I cant do a normal join otherwise I would end up with repetitive data relating to the VACANCY.
Any advice would be apreciated.

Andy

 

[NilsBevaart]

There is no option to have data from multiple records as a single record output. As I can see the situation, you have two options:
Make a report in Access instead of your MS Word Mail Merge. - In this option you can use grouping. With each grouping level (in your case, for each vacancy) you can sum up all records as a list (all city's)

Option 2:
In Word, make use of Merge fields {Next record if}. You can specify the vacancy information at the beginning of the document. Below, you can repeat this Next Record If statement to include city information if the vacancynumber is the same...

Good luck,


Nils Bevaart

 

[PWise]

look into crosstab querys

 

[Andydr]

Thanks. Have been been busy so havent had a good look any of these yet. But they have given some pointers. Thanks again guys.