How to display multiply records in query?

[awu]

All:
I use two tables to generate a query.

The fields in table 1:
stylenumber
productline
One productline can have many stylenumbers in this table.

The field in table 2:
stylenumber
componentnumber
status
One stylenumber can have many components. Each component has one of 4
status.

My query wants to display component counts for each stylenumber under
single productline. For example, this query can be

productline stylenumber CountOfComponent Status
p1 s1 32 1
p1 s1 8 2
p1 s1 2 3
p1 s1 5 4
p1 s2 7 1
p1 s2 6 3

In my form, I want to disply each stylenumber with all the count for
each status.

stylenumber s1 32 8 2 5
I tried many ways, using DLookup, DCount, etc, no success. Really need
help

Thank you much
awu

[Randy Harris]

"awu" <aw***@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

All:
I use two tables to generate a query.

The fields in table 1:
stylenumber
productline
One productline can have many stylenumbers in this table.

The field in table 2:
stylenumber
componentnumber
status
One stylenumber can have many components. Each component has one of 4
status.

My query wants to display component counts for each stylenumber under
single productline. For example, this query can be

productline stylenumber CountOfComponent Status
p1 s1 32 1
p1 s1 8 2
p1 s1 2 3
p1 s1 5 4
p1 s2 7 1
p1 s2 6 3

In my form, I want to disply each stylenumber with all the count for
each status.

stylenumber s1 32 8 2 5
I tried many ways, using DLookup, DCount, etc, no success. Really need
help

Thank you much
awu

If you can have any number of stylenumbers for a given productline, I don't
see any reasonable way to output the data that way. Your example shows 4,
but what if some have 8... or 18?

Perhaps a simple aggregate query might do what you need?

Select
t1.productline,
t2.stylenumber,
t2.status,
count(t2.componentnumber) as countofcomponent
From
table1 t1, table2 t2
Where
t1.stylenumber = t2.stylenumber
Group By
t1.productline,
t2.stylenumber,
t1.status

This would give you - a row with a count of components for each combination
of productline, stylenumber and status.