Invoicing database design

[the bp Guy]

I am redesigning an existing database and need to put some invoicing
capabilities in it need to carry forward some payments an credits to the next
invoice. the existing database was a simple one table database to store info
for the amount of credit cards that we need to reimburse our customers. Now
we are setting up the invoice for gas purchased less credit cards to be
reimbursed. Not always equal need to carry credit to next invoice. I have
made a form to add credit to next invoice but seems to be alot of manual work
and could run into problems if the the credit doesn't get entered

Thanks for your help

 

[Fred]

I noticed that nobody answered. I think that this is due to several issues
and complexities. If I may mention and address a few and then provide an
idea to hopefully get you pointed in the right direction.

- Semantics / definitions problem. In most business circles invoicing is a
one time event, THE billing for goods or services provided. Anything else
(e.g. reflecting payments, a repeated listing of still open items (but NO
first billings for items) would be a different item which is a "statement."


- Your description was hard to understand. This is understandable, give
the complexity of what you're trying to do. While the specific were not
rigorously understandable, I did get the general idea.

- What you are describing is a complex application, not just a question to
be answered. For example, it is fundamentally different and more
complicated than the Northwind example.

- If you want something that is quick or easy to get to the point where it
is ready to use, you might have to buy/use enterprise software rather than
developing it on Access.

As an overview to do it in Access, you'll need to set up a table which is
an "accounts receivable" ("A/R") ledger for each customer. One field will be
the customer ID number. Each transaction involving invoicing or receipt of
payment from that customer, or other items affecting (such as credits)
affecting what they owe will be a record in this table. It's up to you
(based on your particular needs) whether the individual line items from your
invoice each become a record, or whether the whole invoice becomes a
one-record summary.

You need to set it up so that invoicing an order generates ("posts") the
appropriate entry in your "accounts receivable" table. Statements would be
reports generated from the A/R table.

Again, this is just an overview, what you're describing will require a lot
of work and details.

 

[the bp Guy]

Thank you for the reply. I think invoicing might have been the wrong term for
what I want to accomplish with this app. It might be more of a tracking or a
double check for our accounting software. the original database was designed
to keep track of daily credit cards that were taken by our customers and
needed to be reimbursed to them via eft ever 3 to 4 days. which worked fine
all we had to do was reimburse there bank acct.

Now a customer wants to subtract the amount of credit card reimbursement
from the cost of fuel that is purchased from us.

So what I did was created a table [customer] [InvoiceDate] [invoice#]
[InvoiceAmt] to enter data what the customer owes. This works fine. I setup a
query to get info for the tblinvoice and tblcreditcard and then created a
report to show the invoice and list creditcards due and did calculations to
show invoice amt and credit cards owed. the problem is the two don't match
example invoice 25,000 credit cards owed 28,000 which would produce a credit
of 3,000 which would have to be applied to next invoice. So I put another
field in tblinvoice table [creditinvoice]and made the adjustment to the query
and report settings. what I am afraid of is not getting that credit applied
to the next report. this does work pretty well just afraid of human error.

I hope this makes a little more sense than my first post .

Thanks for your help

 

[Fred]

Your second post provided additional insight, but did not provide the
"structure of data" type info that would be needed to solidly spec a
solution.

So I'm taking a guess here.

If I may be direct in a crucial area, your second posts seems to continue
the confusion between invoicing and statements.

If this is a double check on another system, then the invoicing ACT might be
occurring in that system. In that case it gets simpler. Then just set up
the described A/R ledger. Then you should be able to do any or all of that
in reports. For example, if your group by the customer ID, and run a
subtotal of all of their items, that subtotal will be the net amount owed.
You could put a page break with the grouping in which cashe you'll print one
page (+) per customer.

Maybe / hopefully this helps a little

Sincerley,

Fred

 

[the bp Guy]

Your second post provided additional insight, but did not provide the
"structure of data" type info that would be needed to solidly spec a
solution.

So I'm taking a guess here.

If I may be direct in a crucial area, your second posts seems to continue
the confusion between invoicing and statements.

If this is a double check on another system, then the invoicing ACT might be
occurring in that system. In that case it gets simpler. Then just set up
the described A/R ledger. Then you should be able to do any or all of that
in reports. For example, if your group by the customer ID, and run a
subtotal of all of their items, that subtotal will be the net amount owed.
You could put a page break with the grouping in which cashe you'll print one
page (+) per customer.

Maybe / hopefully this helps a little

Sincerley,

Fred

 

[the bp Guy]

My post didn't go through so will try again. I have kinda got hung up on
invoice statement is a better choice of words the a/r ledger would be a great
solution if I would be able to get data from the two tables that I now get
the info for what I am doing. The query that I use now is
[tblinvoice](Invoicedate,customer,invoice#,Invoiceamount,creditamount)
and [tblcreditcard](customer,creditcarddate,Gross,fee,net)
the problem I run into in this query that the invoicedates and credit card
dates are not equal. If I could make a table from this query to get a/r
ledger I could see where I could list this in a statement. The relationships
of the two tables seems to be a problem as it is now.

Hopefully I am not adding more confusion to this .

 

[Fred]

Hello,

Again, I'm just going on my best guess of what's happening.

I guess that you are putting the applicable payment/credit into the same
record as the invoice. And i'm assuming that you have NO DB linkage from
the credit card table to the invoices table. If this assumption is wrong,
that changes some things.

My A/R idea would take a fundamental restructuring of your tables. Here's
the broad brush (via the Fred LOW TECH armchair method) if you are
interested:

1. Save a backup copy or two of your database & everything
2. Add a "description" field to your table.
2.1 Undo the PK status of your PK field
3. Add a "closed" field, and load a "Y" in it for all invoices which are
exactly zeroed out
4. Copy your original Table1 to Table2
5. In Table1 wipe out all records (if any) that don't have an invoice
amount
6. In Table 1 Rename the InvoiceAmount field to "Amount", and delete the
"CreditAmount " field
7. In Table 2 wipe out all fields that don't have a CreditAmount
8. In table 2 If "CreditAmount" doesn't already have the opposite
mathematical sign as "InvoiceAmount", mathematically invert it.
9. In table 2 change the "CreditAmount" field name to "Amount"
10. Append table 2 into table 1
11. Add an autonumber field "EntryNumber" and make it the PK.

For your creti card charge details, you'll either "squeeze" them into the
fields in this table (description etc.) or put them into a seperate linked
"CreditChargeDetails" table.

Well, that might make a mess out of things, but I think it will give you a
solid foundation for doing everything that you describe.

Sincerely,

Fred

My post didn't go through so will try again. I have kinda got hung up on
invoice statement is a better choice of words the a/r ledger would be a great
solution if I would be able to get data from the two tables that I now get
the info for what I am doing. The query that I use now is
[tblinvoice](Invoicedate,customer,invoice#,Invoiceamount,creditamount)
and [tblcreditcard](customer,creditcarddate,Gross,fee,net)
the problem I run into in this query that the invoicedates and credit card
dates are not equal. If I could make a table from this query to get a/r
ledger I could see where I could list this in a statement. The relationships
of the two tables seems to be a problem as it is now.

Hopefully I am not adding more confusion to this .

Your second post provided additional insight, but did not provide the
"structure of data" type info that would be needed to solidly spec a
solution.

So I'm taking a guess here.

If I may be direct in a crucial area, your second posts seems to continue
the confusion between invoicing and statements.

If this is a double check on another system, then the invoicing ACT might be
occurring in that system. In that case it gets simpler. Then just set up
the described A/R ledger. Then you should be able to do any or all of that
in reports. For example, if your group by the customer ID, and run a
subtotal of all of their items, that subtotal will be the net amount owed.
You could put a page break with the grouping in which cashe you'll print one
page (+) per customer.

Maybe / hopefully this helps a little

Sincerley,

Fred