Taking it to a new level -- Compiling totals
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.