Hi all, I have an application written in VB6, using DAO to access an MDB database (Access type). I need to generate a report and seem to be stuck. I hope some one can help or point me in the right direction.

The database is an inventory of objects. The main table holds the objects most basic data. In this case it is dealing with cars. There is a second table that for each car hold certain attributes.

The attributes are defined in a table called ObjAttributes and consists of an autonumber field called "Id" and a text description. Here is some sample data:

1 - Green
2 - Silver
3 - Red
4 - 4 wheel drive
5 - Power windows
6 - GPS
7 - 2 door
8 - 4 door
9 - Manual transmission

The main table consists of an automumber Id field, make and model of the car. The table is called InvItems. Very simple. :-) For example

1 - Chevy Malibu
2 - Ford Focus

The other table, called ObjDetails is a cross reference table that contains the InvItems.Id value and the ObjAttributes.Id value. The user can define 0 or multiple attributes for a given car. For example, taking the Ford Focus as an example, Id = 2, so the ObjDetails table can contain the following

1 - 2, 2
2 - 2, 6
3 - 2, 8

This describes the Malibu as being Silver (2), having a GPS (6) and 4 doors (8).

The query that I need should be able to find all cars given a list of common attributes. For example the query could request a list of cars that have GPS, are 4 door and are red. Or all cars that have power windows, are 4 wheel drive and have a manual transmission. Or just cars that are red and are 4 wheel drive.

I am having problems putting together a query that will work. I have something like this:

select * from ObjDetails where ObjAttributesId in (2,6,8)

In this case ObjAttributesId is the column name where the fk to ObjAttributes.Id is kept. This work for scenarios when I am looking for cars that have any of the listed attributes, but I also need one that gets all car that have all listed attributes. Thank you for you help and time. Best, Sgarv