Create Summary Query

[wdyl121]

Hi All,

I'm new to this forum and am looking for help! Hopefully I will be able to get some answers from the members here!

I need help creating a query to show this summary table.

PIC	Open Part	Open Supplier
Wendy	6	5
Amy	9	6
Samantha	5	3

The database looks something like this...
As long as the Status is not "Done", then it is considered as an Open Part.

PIC	Supplier	Status
Wendy	ABC
Amy	ABC
Amy	Littelfuse
Amy	Littelfuse
Wendy	ABC
Wendy	Maxim	Pending PM
Samantha	BOURNS
Wendy	NXP
Wendy	Analog	Done
Wendy	TYCO
Wendy	Keysight
Amy	Littelfuse	Done
Amy	Nichicon
Samantha	BOURNS
Samantha	BOURNS	Done
Samantha	BOURNS
Samantha	ABC
Samantha	NXP
Amy	Panasonic
Amy	Vishay	Pending PM
Amy	Vishay
Amy	MURATA
Amy	MURATA

Thanks in advance! It's probably really simple but I have zero knowledge on Access or SQL...and my boss is driving me up the wall chasing for this data! >.<

[CJ_London]

you would use a group by query - in the query design ribbon click the epsilon (totals) option.

For Open part, group by PIC and count supplier. I'm not clear how 'Open Supplier' is calculated so the exclude 'Done' status you would either untick the status 'show' and set a criteria of <>"Done". Or you would instead of count supplier have sum a calculated field OpenSupplier:iif(status=1,0,1)