Direction in designing needed 1

[stillwillyboy]

I am trying to create an inventory tracking/invoicing/accounts receivable program. I have my tables created and relationships set. I have tested what I have so far and for the most part, it looks like it’s working.

What I am needing is some direction. Basically, “What do I do now?” I have some questions below:

After all orders for the week have been entered, how do I go about processing this information and create the invoices? The Orders and Orders Detail are in two separate tables. Each Order and related Detail may have more than one line of entry. This could happen if an order has both a part item and shipping charges.

I want the system to assign the invoice number, but if an order could have more than one line of entry, each line should be on the same invoice. I don’t think Autonumber would work here. It seems that all of the info would need to sorted and then processed, then have an invoice number assigned to it.

It seems to me that after the orders have been processed that they should be moved to their own Sales History table for lookup, reports, etc.

Should I set up a separate Accounts Receivable table for the application of payments, debits and credits to outstanding invoices. Or could I just use the Sales History table for the AR also?

Any input on the above would be greatly appreciated.

TIA,

Bill

 

[lespaul]

Bill,

As a current programmer and a former accountant, my first suggestion will be to purchase an accounting package.

Peachtree and Quickbooks have spent YEARS developing what you are asking for, why reinvent the wheel.

Secondly, unless you have a firm understanding of accounting principles, I would pass on this project. I have a very firm understanding of accounting principles and I have always suggested that a client use a packaged accounting system.

As far as your question, where do I go from here? Here are a few things to think about before you start working on the invoice generation:

Is this going to tie in to the General Ledger at some point? If not, then what's the point really?

What about Accounts Payable and Payroll?

How are you going to make adjustments to balances?

How are you going to keep the customer ledgers?

What about purchase journals and receipt journals, etc.?

I have used both Peachtree & Quickbooks, and while I prefer Peachtree, it's focus is towards accountants; Quickbooks is ok for people who don't understand accounting as well, but it's difficult for accountants to change or fix things as they would like.



Leslie

 

[stillwillyboy]

Leslie,

Thank you for the excellent input! Let me touch on the issues that you raised.

The company that I work at is a non-profit sheltered workshop. Basically, we do manufacturing and assembly. We have five different branches for billing. I have the basics on the accounting issues, plus my boss is the CFO, so getting the info I need is easily attainable.

We need a system without a lot of bells and whistles, though we do have one major requirement for the invoicing. We need to have a separate invoice for the same part in a different branch as well as a separate invoice for the same part in the same branch BUT with the a different purchase order number. I looked at Peachtree and Quickbooks and did not see that these would do this, though they might.

As to your questions:
"Is this going to tie in to the General Ledger at some point? If not, then what's the point really?" Yes, the plan is to send the weekly sales numbers per branch to GL. Monthly figures would also be sent.

"What about Accounts Payable and Payroll?" These are different packages. I will need to compare units assembled (payroll) to units billed (invoicing), but this can be done by exporting info from each into Excel. I've already set up the procedure in Excel to handle this.

"How are you going to make adjustments to balances?" Good question. That's where I need the direction re:Access. I know the accounting aspect, it's the Access design and programming I need help on.

"How are you going to keep the customer ledgers?" Another good Q. Same answer as above.

"What about purchase journals and receipt journals, etc.?" The purchase journals are not an issue. Since we receive the product to assemble, we do not purchase much of our own materials at this time. The receipt journals? This would be part of the AR. Would every customer need their own table?

In Quickbooks, it appeared to me that as you enter an order, the invoice number is already assigned. This would not work for us since we might have some of the same order shipped out on different days and different shipping tickets and it would not be feasible for me to go looking for past entries to put another order onto an invoice. I need to be able to enter all the orders and at the time of creating the invoices, have the system sort the info and assign invoice numbers.

As before, any input is appreciated.

Thanks. Bill

 

[lespaul]

We need a system without a lot of bells and whistles, though we do have one major requirement for the invoicing. We need to have a separate invoice for the same part in a different branch as well as a separate invoice for the same part in the same branch BUT with the a different purchase order number. I looked at Peachtree and Quickbooks and did not see that these would do this, though they might.

Could you explain this a little more and I can try to see if it's something Peachtree can already do.

Here's a link to Peachtree's new Manufacturing package that looks promising:

http://www.peachtree.com/mfgindustry/

Leslie

 

[stillwillyboy]

Les,

An example of the Invoice and P.O. situation would be:

Part # 1234 is assembled in three branches 1, 2, and 3 for this week. All three branches for this part for this week have the same P.O. number, let’s say, 9876. I will need three invoices. One for each branch.

Now, let’s say that this same part is once again assembled in all three branches, but this week, there are two different orders for this part and each order has a different P.O. (The same part CAN and sometimes does have different P.O.’s in the same week. Some are ordered early in the week, some later in the week.) Let’s say one P.O. is 4444 and the other is 5555. I will now need six invoices. One for each branch for P.O. # 4444 (total of three invoices) and one for each branch with P.O. 5555 (also a total of three invoices), giving a total of six invoices.

Also as I mentioned, I enter shipping tickets throughout the week and it’s not practical for me to try to find any previous entries so I can add to the order. I need to be able to enter the ticket information as the tickets come in and then have the program sort and process all the info, including assigning the invoice number.

While I’m here, I have an Access Q. I have the two tables “Orders” and “OrdersDetail”. I have made OrderID the PK in both tables and it is also an AutoNumber. When I try to link the two tables by the OrderID field, set the Ref. Integ, Cascade both Updates and Deletes, I get the message “Invalid field definition ‘OrderID’ in definition of Index or Relationship”. In both tables “OrderID” is indexed with Yes (No Dups). Thoughts? Suggestions?

Thanks,

Bill

 

[lespaul]

OrderId can be an autonumber in tblOrders.

In tblOrderDetails, OrderID needs to be a long integer.

Then in tblOrderDetails, you need OrderDetailID (which can be an autonumber too) but the COMBINATION of OrderID and OrderDetailID is the Primary Key of tblOrderDetails.

As far as your invoicing goes, so you issue PO#1234. Branch1 does some building, then it goes to branch2 for more processing and then to branch3? Or is it that PO#1234 is for 1000 things, Branch1 does 250, branch2 does 250 and branch3 does 500, you now need to invoice individually?

Leslie

 

[stillwillyboy]

Leslie,

Thanks for the info on the OrderID problem. I'll give it a try ASAP.

As to the P.O. issue: "Or is it that PO#1234 is for 1000 things, Branch1 does 250, branch2 does 250 and branch3 does 500, you now need to invoice individually?"

Yes. The units are invoiced per whatever branch they are completed in. Each branch needs to receive the revenue for the work that they do and the customer requires the invoices separated by P.O. for their bookkeeping.

Thanks.

 

[lespaul]

Then you can set up cost centers in peachtree that will allow you to do this.



Leslie