Report to answer the question: How many unique items?

[FoxProProgrammer]

My database contains several linked tables. In a nutshell, the database contains the test results for circuit boards. There are approximately 20 part numbers (circuit boards), and each part number has many serial numbers. The flow is as follows:

1. Test the circuit board. It can pass or fail. Enter the test results in the database. Each test result is one record in a table.
2. If it fails, repair the circuit board.
3. Do 1 and 2 until the circuit board passes. Sometimes we can't fix a circuit board, in which case the last record for that serial number will have a status of "fail".

I want to generate a report that contains the number of unique serial numbers tested, number of unique serial numbers that passed, and number of unique serial numbers that failed, all sorted by part number so I can determine the quantity of each part that passed and failed, and the total number of each part that we tested. I can't simply count the number of records to determine the number of parts tested because each serial number can be tested more than once until it passes or we decide that it is beyond economic repair.

Any guidance would be greatly appreciated.

Thank you,



dz
dzaccess@yahoo.com

 

[nberryman]

Run the Report Wizard and take a look at the grouping and summary options. This will allow you to group by and give a count for the group and an overall total



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[JoeEskimo]

You may also wish to take a look at the Unique Records option in the properties area of your query

 

[FoxProProgrammer]

Thanks for your replies. I'm getting somewhere, but it still isn't working correctly.

The first problem is that the detail section of the report contains a lot of white space. I generated the report with the Wizard, and it included the serial number for each circuit board in the detail section. I don't need that much detail so I deleted it. Even after I deleted the field in the detail section and shrunk the size of the detail section by grabbing it the mouse, the white space is still there. Any ideas why?

The second problem is that I don't want to return the total number of failures. I want to count the number of boards that passed the first time, the number that passed after failing (no matter how many times it failed), and the number that failed and haven't yet passed. I think that there's enough data in my database to answer these questions. I just don't know how to extract it. Or maybe there isn't enough data to do this? I'm sure that a query will be needed, and then I can use the query results to populate the report.

The report needs to look like this:

Part Number 1000

Passed first time: 10
Passed after repair: 27
Failed (Needs repair): 3
Total Tested: 40

Part Number 1070
Passed first time: 2
Passed after repair: 5
Failed: 1
Total Tested: 8

It would also be nice to calculate the percentage that passed and failed, but I can deal with that after the rest of the report is working. That part should be a simple mathematical formula stored in a field.

Thanks again for your help.

dz
dzaccess@yahoo.com