Table Setup advice for invoice processing...

[Delta729]

Hi all,



I'm very new to Access. The company I work for is currently using Office 2002 and will be switching to 2010 early this next year. My situation is this... The department I work for processes outside contractors work invoices for payments by my company. We receive and validate invoices, send them to an approver, get them back with either an approver or a rejection, and then process the approved invoices in our SAP system for payment, which is then processed for payment by Accounts payables.

We need to track the processing time for all invoices received and the time it takes to process them from the date we received from the contractor to the date they are processed for payment.

We currently have a database that nothing more than a spreadsheet. It contains the following info in one table.
1. Contractor Name
2. Vendor Number (which is unique to Contractor),
3. PO Number (several different per contractor, one or two per Region - depending on type of PO.)
4. PO Item No (only 4, 10 to 40)
5. Invoice No (always unique - We process about 2500 total invoices per month)
6. Invoice Amount
7. Date our department received
8. Type of Work (3 digit code)
9. Date we sent to approver
10. Approvers Name
11. Date Returned from approver
12. Rejection (yes/no) option
13. Comments section
14. Processing Number
15. Processing/Rejection Date

The information is overwhelming how it is currently set up. I would like to completely revamp this monster before we switch to the new Office 2010. I would greatly appreciate any input on how best to setup tables to avoid all the duplicated information and possibly how best setup up primary keys.

When we first receive the invoices, we input 1 to 10 above. The remaining fields are completed after we process the invoices for payment. The datebase and how it is currently setup is "extremely" repetitive, nothing more than you couldn't do with an extremely large Excel file.

My thoughts is that there should be a contractor table which has ...
1. Contractor Name
2. Vendor Number (which is unique to Contractor)

A table for...
3. PO Number (several different per contractor, one or two per Region - depending on the type of PO.)
4. PO Item No (only 4, 10 to 40)
8. Type of Work (3 digit code)

Then a table for approvers and the region they are in (which is currently not tracked).

Then a table for the invoices with...
5. Invoice No (always unique - We process about 2500 total invoices per month)
6. Invoice Amount
7. Date our department received
11. Date Returned from approver
12. Rejection (yes/no) option
13. Comments section
14. Processing Number
15. Processing/Rejection Date

I think this would avoid un-nessesary duplicate info. I would like to have a form to basically click (sekect) on the contractor name, the Regions would appear and then their corresponding PO's, this would then open a form to enter the invoice #, $, receive and sent date. The other info to be filled in when the invoices are returned to us on another form. I'm not sure if this would do the trick and would appreciate any feedback

I truely hope I made this understandable as I've now re-written this about 5 times and tried to include what I'm trying to accomplish as precisely as I could. Awaiting any and all feedback.

[NTC]

Seems like you are on the right track. Sometimes the right answer depends on the knowing how the business works; but in general any fixed, repeated info (like contractor name/number - can be streamlined into their own tables and then are 'look up' in the main data table.

Splitting a 15 field table into 2 tables of 7 or 8 fields doesn't unto itself buy one a whole lot - after all the records must be linked anyways. Afterall you can selectively display fields in the form/report design.........However it can be extremely useful to split a table if there is high probability that users of differing departments will 'step' on each other in terms of working in the exact same record at the exact same time. In many work situations this phenomena is very very rare - but it could be a point to consider.

Hope this helps a little.