Hi,

I'm fairly new to Access - taught myself the basics as I need to set-
up a database fairly quickly for circuit boards that have been
inspected by Quality Assurance.

They need to record the type of circuit board and quantity inspected.
Each circuit would have a number associated with it for the amount of
parts used on that circuit. They would also input different fault
types in about 30 fields. The result would be a pass percentage based
on quantity of boards+quantity of parts with the sum of the 30 fault
fields.

The part I need help with is how do I work out this percentage? I know
how to add records but how do I add 30 fields and include the other
two quantities in this calculation?


I have most of the database in place but I'm now about to give-up!
cryhard.gif
Any help would be appreciated & may prevent my nervous breakdown!

Thanks in advance,
Mandu

On Thu, 15 Sep 2011 11:26:52 -0700 (PDT), Mandu 666
<mandu666 (AT) googlemail (DOT) com> wrote:

The reason this is so difficult for you is that you have the wrong
database design. The 30 fields represent a "repeating group" and that
is a violation of database normalization rules. Spin these fields off
in their own table:
tblFaults
CircuitID long int PK FK required
FaultTypeID long int PK FK required
Qty int required

You also have:
tblFaultTypes
FaultTypeID autonumber required PK
FaultTypeDescription text50 required

You also go to the Relationships diagram and draw the relations
between the three tables and enforce them.

Now the query is much simpler; for example the sum of faults for each
circuit is:
select CircuitID, Sum(Qty) as SumOfFaults
from tblFaults
group by CircuitID

-Tom.
Microsoft Access MVP

On Thu, 15 Sep 2011 11:26:52 -0700 (PDT), Mandu 666
If, for some reason, you can't, or don't want to, do that, you can use a
union query to get the data into the necessary form:

select circuitid, "fault1" as fault_type, faultfield1 as faults from
circuit_tests
union all
select circuitid, "fault2", faultfield2 from circuit_tests
....
union all
select circuitid, "fault30", faultfield30 from circuit_tests

Save the query as qryFaults, then use it as the source of the grouping query
Tom suggested