AnsweredAssumed Answered

Taking it to a new level -- Compiling totals

Question asked by TKnTexas on Mar 3, 2012
Latest reply on Mar 5, 2012 by philmodjunk

Title

Taking it to a new level -- Compiling totals

Post

I have amazed myself with what I have accomplished (and surprised my managers).  I am not a member of IT but Accounts payable.  I started my databases to supplement some deficiencies from our accounts payable software.  I cannot change the software so I have to work around the limitations.

At the end of each day I read a text file (report) from which I parse out the report into usable data, such as invoice number, date, amount, batch number, voucher number (these last two assigned by the AP software) posting date, and vendor number. 

I summarize this into another table:  batch info-APUser, batch number, date, number of invoices keyed, total dollar amount.  This is where I got the attention of management because now I know how many items are being processed daily by each AP Processor.  Each person will have a variable number of batches per day; typically two but more if they do imports of batches from certain vendors.

I have built a table that summarizes each person per week.  I added a week number calculation field.  What I am trying to do is expand this to show how many per Monday, Tuesday etc of the week each person does.  I realized I needed to tag each batch appropriately.  To get weekly totals I had a "key" field of week.number  and person's initials.  The key would look like:  201201.th for TH's total of week 1 in 2012.  It worked find.

To expand to days of the week I changed the calculation to 201201.02.th for week number - day number - for keyer.  I created a relation alias for each day of the week and created a calculation field for each day to sum approprately. 

What I get is "index missing" and no sums.  There is a record for each keyer for each week.  I am generating keys on the summary record that should have matches in  the Batch.table for each keyer.  Manually I can copy a value from the summary record and use it to find values in the batch.table. 

Any suggestions as to what I am missing?  I am sure it is something simple.  Solving this will move my database app from a nice tool to close end of day to a major analytical total regarding staffing and productivity metrics. 

Thomas Hill

Outcomes