Returning multiple values within a single recordset

[Tomas Eklund]

Using: Classic ASP, MS Access, Jet SQL

I have a users table, an occupation table and a resolver for the n-n
relationship between the two first.

CREATE TABLE User (
ID INT PRIMARY KEY,
Name TEXT(50),
City TEXT(20)
)

CREATE TABLE Occupation (
ID INT PRIMARY KEY,
Occupation TEXT(20)
)

CREATE TABLE UserOccupation (
UserID INT,
OccupationID INT
)

Now I need to list all the users, simple enough, but I would also like to
get all the occupations (let's say they are within the range of 1-5) for
each user. Is this possible within a single query? I understand that it
may not be possible to return an array with SQL, but perhaps there is a
way to concatenate the occupations into a comma separated string or
something.

Regards
Tomas Eklund

 

[Jerry Whittle]

Create a query that gathers up all the needed data in the coventional one
User and one Occupation per row. Use this query as the record source for a
Crosstab Query. It might take a few tries to figure it out even with the
Crosstab Wizard, but I bet it gives you something similar to what you want.