[thelist] multiple options

[Paul Backhouse]

Im currently building a Firm Finder application, which allows users to
select criteria from dropdown boxes (location of firm, size of firm,
category etc). I will then use what they have selected to query a table and
return the ID numbers of firms which match their search criteria. That part
is easy, the part im struggling with is the back-end, where all the data for
each firm is inputted. After selecting a firm, you can then check as many
boxes as you like to describe the firm's specialist area, location and
position. Then submit the form.

The results from the form look like this:

CompanyID: 3
AreaID: 7, 17, 19, 31, 32, 45
FirmSizeID: 1
LocationID: 1, 7, 9, 13, 15
PositionID: 1, 2
TypeID: 2

I have a table called tblLookup which the above values will be inserted into
and users on the front-end can search. It is designed like this:

LookupID | CompanyID | AreaID | FirmSizeID | LocationID | PositionID |
TypeID
----------------------------------------------------------------------------
Autonumber | Number | Number etc........


Now I need to insert a record with CompanyID 3 for each AreaID *and*
FirmSizeID 1 *and* add each LocationID and each PositionID on the same row.
(Does that make sense?!?!)

So that I can do:

SELECT CompanyID FROM tblLookup WHERE (AreaID = 7) AND (FirmSizeID = 1) AND
(LocationID = 15) AND (PositionID = 2) AND (TypeID = 2)

I know how to use an array to insert a record for each AreaID, but Im not
sure about how to insert a record for each AreaID which also contains a
FirmSizeID, LocationID etc...

Can anyone slap me in the face and help me see the light, im lost.... can
this be done???

ps: MS Access

Cheers

Paul