Statistics from child table (1 Viewer)

[Sergeant]

Hi Everyone,
I will figure this out, but I thought asking here might help me simplify the method...

I am building a new db to track the flow of documents for our Command section. The boss wants the following metrics to be availlable on a regular basis:
- How many documents (out of how many submitted) were returned for corrections in a given period to a given Squadron, and also how many were returned twice, and so on...
- Quantify the reasons for documents returned to submitter (I have 4 yes/no options in the child table that track the reason for return).

Here are the fields in my tables:
tblDOCS (ONE SIDE)...
TYPE_DOC
MEMBER
SQUADRON
DT_RCVD
FINAL (yes/no)
DT_FINAL

tblDOC_TRAFFIC (MANY SIDE)...
DT_IN
DT_OUT
RFC_M (Returned For Corrections, Missing documents. YES/NO)
RFC_P (the rest are other reasons...)
RFC_F
RFC_O
RTN_TO (Where document was returned/forwarded to)
RFC (Binary, 1= doc was returned for corrections)
RFC_NBR (How many types of discrepancies...up to 4)

I know I've got some field redundancy in the second table, but in my simple G.I. brain, I thought it would make it easier to quantify the metrics later.

How will I count up the metrics? So many avenues are occuring to me that my brain is beginning to hurt!

Don't be shy, I have served 19 years, so I take criticism pretty well and I am well versed in starting over and also waiting for things.

Thanks in advance,
Sarge

 

[Sergeant]

Still stuck...

Do I need to clarify further? I'm still kinda stuck on how to do this.

Thanks,
Sarge.

 

[WayneRyan]

Hi Sarge,

Track an unlimited number of documents.

tblDocs:
DocID
DocName
TypeDoc
Member
Squadron
DateReceived
Final
FinalDate

Each checkOut/CheckIn will be for one document and
may be returned for any number of reasons.

tblDocTraffic:
TrafficID
DocID
DateOut
CheckedOutTo
CheckedIn
ReturnTypeID
NumberDiscrepancies

The list of reasons a doc is returned.

tblReturnTypes:
ReturnTypeID
ReturnReason

What's out:

Who's documents are out?

Select DocName, Member
From   tblDocuments
Where  DocID In (Select DocID
                 From   tblTraffic
                 Where  CheckedIn Is Null)

Who's documents have been checked-in for Reason #3?

Select DocName, Member
From   tblDocuments
Where  DocID In (Select DocID
                 From   tblTraffic
                 Where  CheckedIn Is Not Null And
                        ReturnTypeID = 3)

Summary of all return types:

Select ReturnTypeID, Count(*)
From   tblDocTraffic
Group By ReturnTypeID

I hope that at least opens up the discussion. The metrics
are hard to define, because only you know what you want.

Wayne

 

[Sergeant]

Wayne,
I appreciate the guidance offered here. Your table layout makes more sense than mine. (It's like I know what's right, but just refuse to do it that way.)

I pressed on with developing the database over the weekend, using the y/n fields for the return reasons. I will most likely alter it to use just one field to store the return reason. I also made a loop in code to evaluate a Squadron's error rate and also the average turnaround time. That was a lot easier than I thought it would be. (I have done loop-de-loops with Excel tables, but this was my first attempt with Access.)

One thing occurs to me...it was easy to confine the four error reasons in the VBA code when I knew what they'd be limited to, but how would I tabulate by error reason when the list of reasons could grow or change?

Take Care,
Sarge.

 

[WayneRyan]

Hi Sarge,

Good to see you're progressing!

If you store the reasons for the return in individual
fields (within a record), you will forever be playing
around with either code to sift through them, or very
complicated criteria in your queries.

If you seperate them into different records, then you
can use things like Group By and Having in your
queries, which should streamline things quite a bit.

The last example showed using the Count and Group By
to provide a summary of all return types.

If you structure your table's right, then your problem
boils down to just getting the right queries. And
there are plenty of (not me) folks here who are great
with queries.

hth,
Wayne

 

[Sergeant]

You know, I hadn't thought about just using a query to derive the error rates. I was kinda fixed on looping through the records to get the tabulated data. The boss might just like to see the data in a traditional query-report setup.
One problem though, is that I only want to have one child record for each trip that the document makes through the command section. Therefore, I can see a benefit in having a separate yes/no for each possible error in one record. There can always be more than one error for each trip of the document, and I wouldn't want more than one error stored in a field.
More thoughts on this?
Thanks,
Sarge.