UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Help Access, Access 2016    
 
   
basehumax
post Feb 11 2019, 11:12 PM
Post#1



Posts: 44
Joined: 17-June 18



Hi All,

I would like someone to help please. i am looking database journal like example wanted to give customer a credit and debit and make a credit limited so when i give 100 it records and when they pay back like 200 system calculates and shows that customer paid extra 100 also it checks the credit limit i set for that customer please guys help..even if you help me the calculation table and queries the rest i can do it..


Thank you in advance.
Go to the top of the page
 
theDBguy
post Feb 11 2019, 11:20 PM
Post#2


Access Wiki and Forums Moderator
Posts: 74,985
Joined: 19-June 07
From: SunnySandyEggo


Hi. When creating a relational database application, everything starts out with the table structure. Can you please show us your table structure, so we can tell what you're working with? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
basehumax
post Feb 12 2019, 02:19 AM
Post#3



Posts: 44
Joined: 17-June 18



Hi
i have not make table yet but i will build table and upload

Thanks
Go to the top of the page
 
basehumax
post Feb 12 2019, 04:19 PM
Post#4



Posts: 44
Joined: 17-June 18



hi
i was unable to upload the file as it refuses the upload but here is what i need please help thanks


Date Name Particulars DR CR Balance Credit Limit


This post has been edited by basehumax: Feb 12 2019, 04:24 PM
Go to the top of the page
 
theDBguy
post Feb 12 2019, 04:22 PM
Post#5


Access Wiki and Forums Moderator
Posts: 74,985
Joined: 19-June 07
From: SunnySandyEggo


Hi. I don't see any file. Please try again...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Feb 12 2019, 04:29 PM
Post#6



Posts: 2,999
Joined: 27-February 09



If you're using WINRAR to zip your files, you're probably saving as a .RAR file and this site won't accept RAR files. Save it as a .ZIP file instead.
Go to the top of the page
 
basehumax
post Feb 12 2019, 04:34 PM
Post#7



Posts: 44
Joined: 17-June 18



Thanks here is the file to start
Attached File(s)
Attached File  Credit__Debit_Ledger_2.zip ( 78.16K )Number of downloads: 3
Attached File  Credit__Debit_Ledger_2.zip ( 78.16K )Number of downloads: 2
 
Go to the top of the page
 
basehumax
post Feb 12 2019, 04:49 PM
Post#8



Posts: 44
Joined: 17-June 18



Hi

that is only example you can make some changes if you want no problems.


Thanks for your help i really appreciated
Go to the top of the page
 
MadPiet
post Feb 12 2019, 05:08 PM
Post#9



Posts: 2,999
Joined: 27-February 09



I looked at your tables and I'm not sure I totally understand your design. it sounds like you should have something like Account(s) and then related child Debits and Credits. Then the balance would be a running total of Credits minus Debits. Right? (Well, I'm not an accountant, clearly!) If that's the case, you could have the Debits and Credits tables related back to the Accounts table (on AccountID).

Once you have that, if you UNION the debits and the credits (one of them multiplied by -1), you can create a report with a running total.

Sorry, but you kind of jumped in to the deep end on your first day at the pool. so to speak.
Go to the top of the page
 
basehumax
post Feb 12 2019, 05:11 PM
Post#10



Posts: 44
Joined: 17-June 18



anything you can help just calculations debit credit that mines the credit limit that will do please help
Go to the top of the page
 
MadPiet
post Feb 12 2019, 05:20 PM
Post#11



Posts: 2,999
Joined: 27-February 09



Okay. I'm going to restructure your database. Do you need just a query for this or can I do it in a report?
Go to the top of the page
 
basehumax
post Feb 12 2019, 05:22 PM
Post#12



Posts: 44
Joined: 17-June 18



Thank you query and including report i will appreciated if you can

Thanks
This post has been edited by basehumax: Feb 12 2019, 05:24 PM
Go to the top of the page
 
MadPiet
post Feb 12 2019, 07:32 PM
Post#13



Posts: 2,999
Joined: 27-February 09



Okay, not going to lie. This thing is really ugly. But it works.

Here's what's going on...
I made two different tables - one for Charges and one for Payments. (Normally the two tables would have several fields in common, but not all of them).
The UNION query lets me join them to create one virtual table, and if I sort them by Account and then by Transaction Date, I can then use a running total to get the current balance.

Here's the query:
SELECT Payments.PaymentID AS TransactionID, Payments.PaymentDate AS TransactionDate, Payments.AccountNo, Payments.Amount
FROM Payments
UNION ALL SELECT Charges.ChargeID, Charges.ChargeDate, Charges.AccountNo, -1*[Amount] AS ChargeAmt
FROM Charges;

In a nutshell, it says "stack these two tables on top of one another, but multiply the Amount by -1 if it's a Charge" (Then I can just add all that stuff up in the Report).
The report is basically doing a Running Sum over the Group (AccountNo). Other than that, no magic. Hope this helps!


Attached File(s)
Attached File  Credit__Debit_Ledger_2.zip ( 58.73K )Number of downloads: 3
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd April 2019 - 05:03 AM