Grouping and count question 1

[FoxProProgrammer]

Hi!

I want to create a report based on the following Query:

SELECT tbl_LRU_Repair.LRU_Repair, tbl_LRU_parts_replaced.PartID, tbl_LRU.LRU_Name, tbl_SRU.Part_Name
FROM tbl_SRU INNER JOIN (tbl_LRU INNER JOIN (tbl_LRU_Repair INNER JOIN tbl_LRU_parts_replaced ON tbl_LRU_Repair.JobID = tbl_LRU_parts_replaced.JobID) ON tbl_LRU.LRUID = tbl_LRU_Repair.LRU_Repair) ON tbl_SRU.SRUID = tbl_LRU_parts_replaced.PartID
WHERE (((tbl_LRU_Repair.LRU_Repair)=[forms]![frm_report]![cbo_LRU].[value]));

If the Query returns the following results:

2 6 XYZ-211 Video Board
2 9 XYZ-211 Modem Board
2 18 XYZ-211 Keyboard
2 9 XYZ-211 Modem Board

I want to display the following in the Report

Computer Model Part Replaced Quantity Replaced
XYZ-211
Modem Board 2
Video Board 1
Keyboard 1

The report outputs everything except the quantities replaced. If I don't include the DISTINCT clause in the Query, there are duplicates in the report. If the DISTINCT clause is in the Query, the total number of records for each part isn't returned, and I can't do the count. How do I go about this?

Thank you!

dz

 

[dhookom]

Create you query like:

SELECT tbl_LRU.LRU_Name, tbl_SRU.Part_Name, Count(*) As NumOf
FROM tbl_SRU INNER JOIN (tbl_LRU INNER JOIN (tbl_LRU_Repair INNER JOIN tbl_LRU_parts_replaced ON tbl_LRU_Repair.JobID = tbl_LRU_parts_replaced.JobID) ON tbl_LRU.LRUID = tbl_LRU_Repair.LRU_Repair) ON tbl_SRU.SRUID = tbl_LRU_parts_replaced.PartID
WHERE tbl_LRU_Repair.LRU_Repair=[forms]![frm_report]![cbo_LRU].[value]
GROUP BY tbl_LRU.LRU_Name, tbl_SRU.Part_Name;

You can hide duplicates or create a group header for LRU_Name.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[FoxProProgrammer]

Thank you so much, Duane. It works perfectly!

Best regards,


dz