Our company currently uses Microsoft Access for ALL Work Order entry and shipping ticket generation purposes. I have 2 separate databases for this purpose. The 1st database is our Production Database, which is used for entering and shipping all large volume jobs. There are heat treat “recipes” that are unique to each part number. The 2nd database is our Tool & Die Database, which is used for entering and shipping all small, 1 time jobs.

The front office uses Peachtree Accounting Complete to enter all pertinent data and generate an Invoice. The problem is that Peachtree will not import All data that is needed on the Invoice. The data must be entered into Peachtree invoicing.

I would like to create an “Accounts Receivable” Access database to generate Invoices, track aged receivables, generate statements by customer and enter payment activity. The goal would be to eliminate duplicate data entering.

At times, a customer will make a partial payment on an Invoice, so I would need to accommodate for partial payments.

Does anyone have any suggestions as to what tables I would need to accomplish “Accounts Receivable”? I do not have any experience with accounting.

Thanks,


D. Bartelt

__________________
D. Bartelt

I did not mention that I plan to link the following tables to the "Accounts Receivable" database.

"Customer" tbl which is a Master Customer table and is linked to both the Production & Tool & Die databases.

"Shipping Ticket" tbls from both databases.

These tables have all of the data that will be needed on the Invoices.

Do I just make a "Invoice" tbl for invoice numbering, invoice date, dollar amounts invoiced, payments received, payment dates and check or transaction numbers?

All suggestions will be appreciated!!

D. Bartelt

Sounds like you are wanting to Integrate this with some existing access apps.

Without seeing the existing system and tables, it will be very difficult to give you very specific answers.

Will you still export data to Peachtree?

Will this need to handle relieving stock from inventory?

What about returns, credit memos?

Do you have to handle sales tax?

I would think that you will need at least some kind of invoice master record table.


Boyd
Access Based Accounting/Business Solutions developer.

Boyd,

Thanks for your reply. Yes, I am trying to integrate the “Accounts Receivable” database with 2 other databases to a degree. I am going to link the “tblShippingTickets” tables from them to the “Accounts Receivable” database. The shipping ticket tables are unique to each of their perspective databases. They are in separate databases that are not linked in any way. I will pull information from them when an invoice is to be generated. The information will be as follows: ShippingTicketNumber, ShippingTicketDate, Description, CustomerName, PartNumber, PONumber, LotNumber, WorkOrderNumber, Quantity and Weight.

No. It will not be exporting to Peachtree. My goal is to replace Peachtree with my new database.

No. It will not need to handle relieving stock from inventory. The other 2 databases handle that end of it.

I will need a Credit Memo table.

Returns are not an issue. If needed, we rework parts at no charge.

No. It does not need to handle sales tax.

It will need to handle unit price for each part number along with the cost multiplier for that line item. Finally, a total price for a given shipper ticket.

It will also need to generate statements and aged receivables reports.

Each Invoice number will need to be unique for tracking purposes. However, an Invoice may have information from 5 different Shipping Tickets on it.
I believe I will need to make the following tables to accomplish what I desire.

Customer table: for customer information. Ie.: Name, Address, City, State and Zip.

Invoice table: to generate an invoice number and hold selected data on the invoice.

Order table: to hold the information for each Shipping Ticket on the invoice.

Payments table: to hold the information for each payment a customer makes. Sometimes, the customer does not pay the entire invoice amount. They make partial payments.

Credit memo table: to hold credit memo information.

Thanks.




D. Bartelt