Listing and Counting

joe.sims

Registered User.
Local time
Today, 17:23
Joined
Dec 9, 2004
Messages
13
Hello,

I am in need of some major help. I have built a database to track what the quality of a particular part is and count it. Let me start over....

Business Rules:

1. OEN/PN is on multiple items.
2. Each screen item has a specific quality rating.
3. The rating system is based on the numbers 0-8 (0 = New, 8 = Failed).

I want to be able to generate a report that lists the OEM/PN and the number of those OEM/PN that have a rating. Refer to the table below:

OEM/PN 0 1 2 3

Part Number 5 15 7

Please help and let me know if you need more info

Joe
 
Hello Joe, If I may be allowed to go slightly off topic here, Can you elaborate on your rating schema. I see where you say you use something like 0 = New, 8 = Failed. Could you elaborate on that. This could be viewed as a statistical process control issue and those types of issues interest me...

Ken
 
The rating system is 0 - 8 and it is broken down as follows:

0 = New
1 = Scratch
2 = Spot
3 = Major Spot
4 = Scratch plus spot

and so on.
 
To start with, it appears that your data is not normalized. At this point in time, would you be open to changing the design of your database? If you already have a lot of info in the table and a major change is out of the question, I'm sure we can come with something...

kh
 
I believe it is normalized. Could you please tell me why you think it is not? Maybe this is the issue.
 
Sorry, I may well be jumping to the wrong conclusion....

1. You have a part numbers.
2. As parts come through, you record their quality rating
3. Your table looks something like:

PartNumber, pk
rating_0
rating_1
rating_2
rating_3
etc...

4. When a part comes through it gets a yes in the appropriate fld
5. Now you need to add up the number of 'yes's for each rating for each PartNumber?

Is this basically the way it works?
kh
 
It is no problem. I want to be sure that it is right or close to it. I know it is working the way we need it to and if any revisions are needed i will correct them in a few weeks.

There are a total of 7 tables. tblOEM/PN and tblResults are the ones that I am concerned with.

tblOEM/PN looks like this...

OEMID.........OEM/PN
1 07k5589
2 07k5569
3 07k5592

tblResults looks like this...

ResultID........Result_Name
0 New
1 Scratch

In a report i would like to see this...

OEM/PN........0.........1.........2..........3

07k5589........2........3..........8........10

This shows the partnumber and the associated quantities for all Results.

Thanks,

Joe
 
And how do you link tblOEM/PN to tblResults? You should have a foreign key in tblResults that references a record in tblOEM/PN.

btw: The slash in 'tblOEM/PN' is bad juju... :)

kh
 
I need to bug out. I'll try to get back later today...

kh
 
Thanks for the help, I will be here till 5 CST and until we figure this out i am just going to export the data into Excel.
 
Ok. Here's the drill:

1. Make a new query based on the result table.
2. Make it a totals query.
3. Limit the ID's to to '0's
4. Group on the fk and count the fk's
5. Save the query something like ttlCount_0
6. Repeat 1 - 5 except do the limit and name to '1's, '2's, etc

7. Do a new query and bring down the oem table and all eight of the new queries.
8. Link 1 to many from the OEM table to all eight of the new tables
9. Bring down the OEM fld from the OEMTable and the count fld from each othe new queries.

I think this will work...
kh
 
This sounds good but I need some clarification on the step 2 (Make it a totals query). How would I do that.

My results table has only two columns, ResultID and ResultName. The ResultID is the PK. I have created the queries based on the the desired result.

Please explain futher

Thanks
 
Can you post the a db with the tables and some sample data?

kh
 
Do a new db and import the two tables. Then delete all but a couple hundred rows in each table. Zip it and use the attach file feature in a new post.

kh
 

Users who are viewing this thread

Back
Top Bottom