Accounts, how to create monthly statements etc. 1

[oliverdempsey]

Hi there,
I am developing an accounts package based on the Northwind template and I want to add a facility for printing statements at the end of each month.

I am wondering what is the conventional approach to this type of project.
Receipts,
create a new table for receipts?
Credit notes,
Create a table for credit notes or put them in as negative entries in the order details table?
Statements,
How would I create a query to list all transactions for each customer from two or three tables ('order details', 'credit notes' and 'receipts') or is this possible?

Have a look at

http://www.barrowvale.com/accounts

to see screenshots of what I have done so far. You will notice at No. 5 where I tried to create a query with the orders and receipts tables, but with no success.

Have a look and see if you can solve my problem

Thanks in advance
Oliver Dempsey

 

[ghubbell]

Hi Oliver,
This is a wonderful way to convey your problem! First I'll say don't believe the query as you see it. Have a "Report Wizard" build you a report based off of the query and see what your results are there. You will probably find that the results are good, as the report will be sorted and grouped in a more logical fashion compared to the query. Your Order Subtotal Join is not a "firm" join (my word) and in truth, you can probably obtain an order subtotal by means of a calculation or summation within the query, another query, or on the report its self. Try a few tests either way and see if a report is telling you the truth... Gord
ghubbell@total.net

 

[oliverdempsey]

Hi Gord
I tried building a report with the report wizard but it just gave me the same results in the report as I had seen in the query. Maybe I did something wrong.
If you would like to see how I constructed the query and the report feel free to download

http://www.barrowvale.com/BVAccounts.zip

it's 1.45 MB and will take 9 mins to download at 44Kbs
The query and report are both named invoicesandreceipts.
Anybody is welcome to download it and learn from it.

Maybe you can shine some more light on it for me

Regards
Oliver

 

[ghubbell]

Oliver, you will have to show me how to do that. I'd love to do that with some samples!

I really got in there and I found some things that are causing grief.
First is the orders subtotal table. It has to go I'm afraid. It is really not required: calculate-able in any query or directly on the report.

This would apply as well to the credit notes.

I'm guessing you would like to keep track of the customers debits/credits versus their orders. Without getting in to a complete AR/AP/GL system, you might be wiser to monitor each order and its payments. This would become a single table off of the Customers and might contain the orderID, a payment date, an amount field and maybe a field for a note.

This "accounting" table could handle both debits and credits and with a little code could push any overpayments towards any outstandings. This is a very simplified method as sophisticated accounting packages will move payment records from many different tables (batch posting) in order to "make the books balance" and close fiscal periods.

Your choice to base your work off of The Northwinds sample is, well, politely... so-so. If you think it is all that you might need, then continue. Personally I only use it to demonstrate code to others as I know most everyone has it.

You're using Access 97 (8.0) which offered many more "wizard built" database options than 2000 does. I might suggest to take a shot at one or two that might address your needs a little closer. You may be very surprised what can be automatically produced for you in a blink of an eye.

Now, before doing anything (besides sketching out a plan of wants and don't wants), hang around for a while and see what some others might have to say. There's lots of bright folks here who love the challenge! I'll stay tuned too. Gord
ghubbell@total.net