Field Specific Criteria

[TomJamieson]

I have a query drawing information from two tables. The first table has information about a product, and the second table holds all the faults found per product. So any one product can have anything from 1-14 faults. I have created a report from the query, but the Fault item that I put on it only ever shows the first fault for that product. Is there some way of fixing this at report level, or on the query?

The tables are connected through the ID field, but the primary key for the faults table is another autonumber. Ex:

Primary Key___ID___Fault
___1__________ 1____fault 1
___2__________ 1____fault 2

That's two faults for the same product. Maybe I could create new fields in the query like Fault1: [Fault] with criteria set to primary key = 1? And one for each fault? But when I do it, it sets that criteria to the entire query, so the results end up blank because the "primary key" field doesn't exist on the product table.

I guess what I'm asking is can you set a criteria for just one field of your query to follow? Or can I resolve this issue in the report design?

Many thanks,
Tom

 

[TomJamieson]

I just tried creating a new field for every fault like this: fault1: IIf([Primary Key]=1,[Fault]).

It didn't work. Because the query results in mutliple records, with all fields exactly the same except record 1 has data in the fault1 field and no other fault fields, record 2 has data in the fault2 field and no other fault fields, etc. And when you pull the field from the field list on the report, it only shows data for the first record, so fields fault2 to fault14 are blank.