Access Data Movement between Query and Table (1 Viewer)

ZoeMaKayla

Registered User.
Local time
Today, 15:02
Joined
Dec 14, 2017
Messages
12
Hello All,
Please I have a table tblSalesInvoice and a table tblPayment to take the payment from the Sales Invoice. But I couldnt collect the total sum of the order on the Sales Invoice. I have tried creating a query that does the sum but also couldnt move the sum of the order to the payment table yet even using a form with unbound textbox. Can someone help please:confused:
 

plog

Banishment Pending
Local time
Today, 09:02
Joined
May 11, 2011
Messages
11,611
Data shouldn't be moved in a database. It sounds like you are trying to store a calculated value--which is also a no-no.

Instead, when you need the total, you simply use the query. Do not store a total....calculate a total and use it from where you calculate it.
 

ZoeMaKayla

Registered User.
Local time
Today, 15:02
Joined
Dec 14, 2017
Messages
12
@Plog, thanks for your response. But I still need to store the data in a field in tblPayment table and Payment made can be used and another field in the table calculate the balance which is store in the field to be call back whenever the information is refer to
 

Mark_

Longboard on the internet
Local time
Today, 07:02
Joined
Sep 12, 2017
Messages
2,111
@Plog, thanks for your response. But I still need to store the data in a field in tblPayment table and Payment made can be used and another field in the table calculate the balance which is store in the field to be call back whenever the information is refer to

From your description, you need to calculate the total of unpaid sales.

Payments are normally applied directly to sales (going oldest to newest) so you can properly track what sale is OLDEST that still has an open balance.

Depending on business model you will either directly apply payments to sales OR use a junction file to link one payment to multiple sales / multiple payments to one sale (depending on if they are making payments or paying for multiple sales at once).
 

ZoeMaKayla

Registered User.
Local time
Today, 15:02
Joined
Dec 14, 2017
Messages
12
@Mark Thanks, I have tried the link and also, a subdatasheet to link the query and the tblPayments but i have to manually typed the total into the tblPayment field for the Total Amount. I dont want to repeat this I want it automated into the field directly from either the query or the tblInvoice. And I have used the sum(expression) function im the module but its not working
 

Mark_

Longboard on the internet
Local time
Today, 07:02
Joined
Sep 12, 2017
Messages
2,111
@ZoeMaKayla,

Not sure on your reply, but it sounds like you want to DEFAULT the Amount in your payment to be equal to the amount owed for the sale. Is this true?

Question for business rules though;
1) Can one payment apply to more than one sale?
2) Can you accept a partial payment (such that one sale would have more than one payment)?
 

ZoeMaKayla

Registered User.
Local time
Today, 15:02
Joined
Dec 14, 2017
Messages
12
@Mark Thanks. Your Question for business rules 1 & 2 is exactly what I need. Can you please help.
 

ZoeMaKayla

Registered User.
Local time
Today, 15:02
Joined
Dec 14, 2017
Messages
12
@Mark, I haven't had from you yet. Please kindly reply and waiting for your help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:02
Joined
May 7, 2009
Messages
19,169
suppossing you have this invoice table (say tblSalesInvoice)
with fields like

Invoice Number
Invoice Date
Customer Number
Invoice Amount

and your Payment table (say tblPayment)
with fields like

Payment Date
Customer Number
Payment Amount

and your Customer Table (say tblCustomers)
with fields like

Customer Number
Customer Name
Invoice Total
Payment Total
Outstanding Balance

Your Update query should look something like this:

UPDATE tblCustomer SET
tblCustomer.[Total Invoice] = DSum("[Invoice Amount]","tblSalesInvoice","[Customer Number] = " & tblCustomer.ID),
tblCustomer.[Total Payment] = DSum("[Payment Amount]","tblPayment","[Customer Number] = " & tblCustomer.ID),
tblCustomer.[Outstanding Balance] = DSum("[Invoice Amount]","tblSalesInvoice","[Customer Number] = " & tblCustomer.ID)-DSum("[Payment Amount]","tblPayment","[Customer Number] = " & tblCustomer.ID);
 

Attachments

  • aaSales.zip
    19.8 KB · Views: 92

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:02
Joined
Jul 9, 2003
Messages
16,244
@Mark, I haven't had from you yet. Please kindly reply and waiting for your help
I think you are more likely to get a reply if you answer Marks questions. Speaking for myself, I find it difficult to continue participating in a thread if my questions are ignored.

Sent from my SM-G925F using Tapatalk
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Jan 23, 2006
Messages
15,362
@ZoeMaKayla,

I agree with Tony --answer the questions that have been asked. Answers to these will help you and readers.
For an overview of a Customer, Sales, Invoice and Payment context, see this free generic data model from Barry Williams' site.
 

ZoeMaKayla

Registered User.
Local time
Today, 15:02
Joined
Dec 14, 2017
Messages
12
@arnelgp
Thank you for a wonderful contribution but its not fitting into the database. Can you please send your email to me so as to push to your email the database i am redesigning

@Uncle Gizmo,
I have answered Mark's question already.
the rule 1 & 2 is exactly what i wanted to answer in the designing.
Thanks


@jdraw
Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:02
Joined
Jan 14, 2017
Messages
18,186
PMFBI but it really doesn't appear that you've answered Mark's questions.
You've just stated that the questions are what you need...

The questions need yes or no answers to inform future replies.
 

ZoeMaKayla

Registered User.
Local time
Today, 15:02
Joined
Dec 14, 2017
Messages
12
Hi all,
My answer to mark's questions

I have the following tables alongside other tables tblSalesInvoice and tblPayment with relational field as the customers Name and Invoice Number,
I want to do the following
) one payment apply to more than one sale?
2) Accept a partial payment (such that one sale would have more than one payment)?
 

isladogs

MVP / VIP
Local time
Today, 14:02
Joined
Jan 14, 2017
Messages
18,186
So removing the question marks, that's YES to both questions...
 

Mark_

Longboard on the internet
Local time
Today, 07:02
Joined
Sep 12, 2017
Messages
2,111
@ZoeMaKayla

I am guessing you have the following then;
1) A file that contains PAYMENT information (amount, date, customer)
2) A file that contains SALE information (amount, date, customer)
---SALE may either be a single record OR an invoice/line item parent/child.

You will want to add one file; a junction file.
T_ApplyPayment
-ApplyPaymentID...Autonumber...Primary Key
-SaleID...Numeric...Foreign Key
-PaymentID...Numeric...Foreign Key
-Dt_Applied...DATE...Date payment is effective (User editable, SHOULD be date of payment, but management may allow credit as if before)
-Amt_Applied...Currency... How much of payment to apply.

Now here comes the fun part.

When you are looking at sales, you won't store the amount paid. You will total all matching records in T_ApplyPayment. Once you have applied payments equal to the sales amount you would update the record with a PAID date. When you are looking at payments you will do the same until the total amount in the payments is applied, then you will update it APPLIED date.

What this will let you do;
1) Show sales that are not fully paid off (No PAID date) and the amount open on that sale (SALE amount, less the total of all T_ApplyPayment for that SALE).
2) Show all payments that have not been applied, or applied in full (No APPLIED date) and the current balance (PAYMENT amount, less the total of all T_ApplyPament fro that PAYMENT)

This covers the following cases;
-Sale is made but partial payment received
-Sale is made with full payment
-Payment received that covers more than one sale
-Payment received that exceeds all open sales

For myself, I would have a form with customer information, and a sub-form with open payments. Open payments are simply ones that do not have a APPLIED date. In the subform I would show the amount of the payment, have a calculated field with the amount applied, and a calculated field with the amount remaining.

From the sub-form I would have a button that calls another form to apply payments. The called form would be based off of the SALES table.

When you apply a payment, this creates the T_ApplyPayment record. You will want to make sure (as you will know ahead of time) that the amount applied does not exceed EITHER the amount open on the sale or the amount unapplied on the payment. For most users the date applied is when they apply the payment OR the date the payment was received (depending on your business rules). Managers would be able to enter an earlier date; some times they will want to consider the payment when the customer told them they paid rather than when the payment cleared.

Please let me know if this description will work for your business model. I didn't have a chance to make a sample over the weekend, been busy planning for holiday travel.
 

Mark_

Longboard on the internet
Local time
Today, 07:02
Joined
Sep 12, 2017
Messages
2,111
@ZoeMaKayla

One other question, do you need to allow for third party payments?
 

Users who are viewing this thread

Top Bottom