how to deduct order price from prepaid credit value?

[Kwizard]

Hi guys, I'm new to access...Very new.

I've been creating a cash register, that has the following tables:

Customer

Credit



Order_main

Order_sub

Products.

I've gotten so far that I can set up and order, consisting of several products, choosing the customer from a dropdown list and display the subtotal of the order.

But I can't, for the love of god, figure out how to deduct the subtotal from the credit value and get it updated.

if someone could help me with a step-by-step guide, I'd really appreciate it.

but I need it to be very low-tech explanations...like I said: I'm very new to access

[rzw0wr]

I assume you have a form.
In a text box record source =sum([credit] -[subtotal])

If you are still having problems with this upload you database.
Dale

[Kwizard]

Exellent, ty.
ok, now I've gotten the cost field to deduct from the credit value, but how do I get it to update with the new credit?

[rzw0wr]

Well I don't know.
I had this problem when I wrote a database that involved debt and payments.
I made a query that added all the debt the person had accrued and subtracted all the payment they had made.
Subtracted total payments from the total debt.
2 tables. Debt and Credit.


Dale

[Kwizard]

ok, I have the Debt and Credit tables, and the Query, now I need to figure out how to add the total salesprice from the order to the debt table.
the order form consists of an "order" table and "order_sub" table (that connects to the "Goods" table) and the total price value is a calculated field in the "order_sub" table.

[orange]

You may get some ideas from this free data model that introduces "Transaction". Somewhere in your setup I think you match Orders to Payments, and you can determine a Balance. Debt and Credit seem to be further abstractions of Orders and Payments.
I'm not an accountant and the best advice I can offer is :

Identify the entities and relationships as best you can (can be done with a piece of paper and pencil)
Set up some test data,(and your expected outcomes) and test against your evolving data model. Whenever you have some issue/anomaly figure out if it is the test data, the model, or something else. Rationalize every issue and adjust until the model satisfies the test data.... Then build your database.

The free model at http://www.databaseanswers.org/data_...king/index.htm There are other models at
http://www.databaseanswers.org/data_models/index.htm that may help , but I couldn't find one quickly that dealt with Invoices and Payments and Credit.

Good luck.

Note: There is a thread that is dealing with AdvancedPayment that you may wish to read --it may have similar concepts to your issue and may offer some new ideas.
see https://www.accessforums.net/databas...ign-35189.html