Getting total count (1 Viewer)

[mech55]

One of the tables in the database has these fields

st1: st2: st3: st4: Date:
pass fail fail pass 10/21/05
fail pass fail pass 10/22/05

and so on. What I need to do is extract the number of passes and fails on each of the four stations and put them into a report. User will have to enter either one date or a date range.

Right now, i'm having to create a 8 queries for to find Count of Pass and Fail on each station and make a master query. And I because it's not grouping everything together I can't figure out a way to get a date range...

Please help,

 

[FlyerMike]

I suppose it is too late to drag the database designer into a dungeon, and have a long talk?

In a perfect world, the table would have been designed as:

Station (int)
TestDate (date)
TestResult (Yes/No) as opposed to Pass/Fail

If the current design cannot be changed, you need to interrogate each of the 4 station fields individually for the count of passes and failures, and then summarize them in a query/report.

Sorry...

 

[mech55]

well, actually I designed the db and it's possible to change it. But I also designed the application in vb.net that uses it as a back-end. I can't really think of a good way to implement it the way you describe because of certain business rules. such as a station can't be passed if the previous station hasn't been passed or inspected. See what i'm saying?

 

[FlyerMike]

Sorry, I'll retract the dungeon remark...

I can't get to Station2 unless I pass Station1, correct?

How did the entry on 22-Oct-2005 happen? I failed Station1, but passed Station2?

I'd lean toward the table design where you have only one test result per record.

I'm probably not appreciating the business rules.

 

[mech55]

never mind I just realized that it's a problem to be handled by the front end and not the fault of back end Db. i'm a newb sorry. If I shout out my three tables out here could you give me some more suggestions on what to change?

 

[mech55]

oh I just posted some random stuff. just wanted to show some info. it's more like this...

st1: st2: st3: st4: Date: serial#(primarykey)
pass pass fail NA 10/21/05 s23434324
pass fail NA NA 10/22/05 s234324234

 

[mech55]

Date is when Serial number was input into the system.

I have three tables

PartNumber with fields PN(primary) (description) (type)
There are only 62 part numbers (ever)

There are many serial numbers to each part number
so second table is like so

SN(primary key) PN(foreign) Station1 Station2 Station3 Station4

and I have another table called defect log. There are many defects per serial number...But you only get a defect when one of the stations fail. so it's possible to have no defects.

table looks like this...
DFID(autonumber/primary key)--SerialNumber(foreign)--DefectType--Stations
--Date rework done..

 

[Jon K]

Instead of using 8 queries, you can sum 8 expressions in a query like this:-

SELECT
-Sum([st1:]="Pass") AS [st1 Pass], -Sum([st1:]="Fail") AS [st1 Fail],
-Sum([st2:]="Pass") AS [st2 Pass], -Sum([st2:]="Fail") AS [st2 Fail],
-Sum([st3:]="Pass") AS [st3 Pass], -Sum([st3:]="Fail") AS [st3 Fail],
-Sum([st4:]="Pass") AS [st4 Pass], -Sum([st4:]="Fail") AS [st4 Fail]
FROM [TableName]
WHERE [Date:] between [Enter start date] and [Enter end date]
.

 

[mech55]

I LOVE YOU MAN! hehe, (little dance of joy)

 

[mech55]

well, um bit of a problem now, i'm able to get all the passes and fails, but I'm running into a problem with getting them by the date...

I'm getting the error below
you tried to execute a query that does not include the specified expression date as part of an aggregate function

this is my sql...
SELECT -Sum([Serial_Number_Log]![St1_Status]="Pass") AS st1_Pass, -Sum([Serial_Number_Log]![St2_Status]="Pass") AS st2_Pass, -Sum([Serial_Number_Log]![St3_Status]="Pass") AS st3_Pass, -Sum([Serial_Number_Log]![St4_Status]="Pass") AS st4_Pass, -Sum([Serial_Number_Log]![St1_Status]="Fail") AS st1_Fail, -Sum([Serial_Number_Log]![St2_Status]="Fail") AS st2_Fail, -Sum([Serial_Number_Log]![St3_Status]="Fail") AS st3_Fail, -Sum([Serial_Number_Log]![St4_Status]="Fail") AS st4_Fail, Serial_Number_Log.Date
FROM Serial_Number_Log
WHERE (((Serial_Number_Log.Date)=[?]));

what can I do to get around this?

 

[mech55]

Ha I figured it out, instead of having a separate date field just create a criteria in one of the other fields...problem seems to be solved. THANKS...