Help with designing a query!

[Guest]

Hi,

I have a database with some tables and I have to create a report after
extracting some information. I'll appreciate some help with the query for the
report.

The tables are:

1. tblColor
fields: ColorID (pk), ColorName, some other fields

Each ColorID belongs to either ColorGroupA or ColorGroupB:

2. tblColorGroupA
fields: ColorGroupA_ID (pk), ColorID (fk), some other fields

3. tblColorGroupB
fields: ColorGroupB_ID (pk), ColorID (fk), some other fields

4. tblPaperColor
fields: PaperID (pk), ColorID (pk)

So, tblPaperColor looks like this
(first column is PaperID, second column is ColorID):
1, 1
1, 2
1, 3
2, 1
2, 2
2, 4
2, 5
3, 3
3, 7

Just for illustration, assume that ColorIDs 1 and 3 are in tblColorGroupA
and ColorIDs 2,4,5 and 7 are in tblColorGroupB.

What I need to know is to get the number of ColorIDs that belong to each
ColorGroup for each PaperID.

So, I need the folowing information:
PaperID, ColorGroupA, ColorGroupB
1, 2, 1
2, 1, 3
3, 1, 1

I'm not sure how to construct this query and will appreciate any help.

Thanks.

-amit

 

[Guest]

Combine tblColorGroupA and tblColorGroupB by adding a Group field.
You can not have two PKs in a table. You can have a compound PK consisting
of two fields.

Create your query with tables - PaperID and tblColorGroup joined on ColorID.
In query design view drag down the following fields --
PaperID
Group
ColorName
ColorName

Change the query to a totals query by clicking on the icon that is the Greek
letter Epsilon. The design grid will change. Change the TOTALS field for
the second ColorName from Group By to Count.

 

[Guest]

Combine tblColorGroupA and tblColorGroupB by adding a Group field.

You can not have two PKs in a table. You can have a compound PK consisting
of two fields.

Yes, I know. It is a compound PK, and that's what I was trying to get across
- could have done it differently

Create your query with tables - PaperID and tblColorGroup joined on ColorID.
In query design view drag down the following fields --
PaperID
Group
ColorName
ColorName

Change the query to a totals query by clicking on the icon that is the Greek
letter Epsilon. The design grid will change. Change the TOTALS field for
the second ColorName from Group By to Count.

Worked great! I didn't include the second ColorName though, and it works fine.

Thanks for your response Karl. Appreciate it.

Cheers,

-amit