Good evening everyone. I am planning on learning how to more effectively use Access by working on some tools to make my life easier at work and could use some direction/examples/learning materials to help make this a little less painful.
I currently work as a low level accountant / bookkeeper in a hotel environment where all of my data is stored in excel files (large amounts) and then submitted to an offsite accountant to be entered into proper software. I want to try and make some database applications so I can quickly access old data as currently I have to search through several hundred excel files if someone is asking a question on older data.
The first project would be an invoice management module , I currently have plans for a Vendor database which includes info about the vendors based on a csv file I had my superiors send me. I plan on making a form to input invoices into the database but here are a few of my road blocks currently.
1) I'm not sure how to lay the data out in a database to take into consideration that each invoice can have between 1 - 6 Ledger codes attached Current Excel Format
GL Code Vendor Vendor ID InvoiceNum Invoice Date Amt
Example of a multi code invoice is
11-1234 Bob's Shop BOB1 INV1234 1/1/2014 100.00
11-4321 Bob's Shop BOB1 INV1234 1/1/2014 200.00
I thought about using the same format in the database however it seems kind of redundant to do it that way. Also I want to add in a few features such as Date Entered, and Batch Number so that I can enter them on the fly and then eventually compile a batch from all invoices without a batch attached to them . I have no clue how to accomplish this with the form interfaces I assume it would probably need 2 tables 1 for the main invoice data such as VendorID , Invoice Number, Invoice Date, Total Invoice amount then some sort of subform that automatically attaches VendorID and Invoice Number to each GL code. Other areas I could use some direction on is I want to make the data as compatible with the formats they use since our GL codes are stored in their system (from what I can tell from current templates) as 1111223333 and between various reports we either use 22-3333 1111-22-3333 etc so I am wondering how I would take that into account so that when referencing data if I say search or enter 22-3333 it'll search for 1111223333 where 1111 is a constant number in my case 2501 . Any help is very much appreciated, I've been racking my brain on this for 2+ years and still haven't left square one eventually I'd like to implement some sort of Purchase Order system on top of this database to save me hours of tracking down people to find out if they've purchased stuff.
I currently work as a low level accountant / bookkeeper in a hotel environment where all of my data is stored in excel files (large amounts) and then submitted to an offsite accountant to be entered into proper software. I want to try and make some database applications so I can quickly access old data as currently I have to search through several hundred excel files if someone is asking a question on older data.
The first project would be an invoice management module , I currently have plans for a Vendor database which includes info about the vendors based on a csv file I had my superiors send me. I plan on making a form to input invoices into the database but here are a few of my road blocks currently.
1) I'm not sure how to lay the data out in a database to take into consideration that each invoice can have between 1 - 6 Ledger codes attached Current Excel Format
GL Code Vendor Vendor ID InvoiceNum Invoice Date Amt
Example of a multi code invoice is
11-1234 Bob's Shop BOB1 INV1234 1/1/2014 100.00
11-4321 Bob's Shop BOB1 INV1234 1/1/2014 200.00
I thought about using the same format in the database however it seems kind of redundant to do it that way. Also I want to add in a few features such as Date Entered, and Batch Number so that I can enter them on the fly and then eventually compile a batch from all invoices without a batch attached to them . I have no clue how to accomplish this with the form interfaces I assume it would probably need 2 tables 1 for the main invoice data such as VendorID , Invoice Number, Invoice Date, Total Invoice amount then some sort of subform that automatically attaches VendorID and Invoice Number to each GL code. Other areas I could use some direction on is I want to make the data as compatible with the formats they use since our GL codes are stored in their system (from what I can tell from current templates) as 1111223333 and between various reports we either use 22-3333 1111-22-3333 etc so I am wondering how I would take that into account so that when referencing data if I say search or enter 22-3333 it'll search for 1111223333 where 1111 is a constant number in my case 2501 . Any help is very much appreciated, I've been racking my brain on this for 2+ years and still haven't left square one eventually I'd like to implement some sort of Purchase Order system on top of this database to save me hours of tracking down people to find out if they've purchased stuff.
