Require Balance/ Outstanding Ledger Report in my db

[Shyamdharam2]

I have two table in my db,

1. Opening table
type
id - autonumber
date - date
Ownerid- number
Nameid - number
debit - number
credit - number
Description - text



2. Bank table
type
id - autonumber
bank - text
date - date
ownerid - number
debit - number
credit - number
nameid- number
narration - text

two table is there,
i want query, that give balance/outstanding of nameid


let Suppose - nameid - 50
in
opening table - 500 /- credit

then , bank table - 600 /- debit
after 500/- credit

then i open query, it's shall show = nameid -50 ( 500 - 600 + 500) = 400/- balance

Basic Calculation: opening table -
Nameid : (sumof credit - sumof debit) + bank table (sumof credit - sumof debit) = balance amount

 

[CJ_London]

How does this question differ from your existing thread here?

Make a query of Balance/Outstanding Amount Display only

i Have two table one is Bills table another is Payment table, I want a query that show only Payment pending amount onlye

www.access-programmers.co.uk

 

[Shyamdharam2]

it Slightly Different Because it's totalling of Nameid
and its has opening table + bank table (2) debit & credit column

 

[plog]

Your table structure is incorrect.

1. Tables with the same structure. You're two tables are virtually identical. Why is that? Why can't you just use one table for this data?

2. Credits and debits in separate fields. If you want to aggregate the data (e.g. add it up). You don't store positive transactions in one field and negatives transactions in another. You put them in the same field. That way you can just SUM that field for the total.

You need to do both 1 & 2 above and the issue you are posting about is trivially solved with just a SUM()

 

[Shyamdharam2]

Your table structure is incorrect.

1. Tables with the same structure. You're two tables are virtually identical. Why is that? Why can't you just use one table for this data?

2. Credits and debits in separate fields. If you want to aggregate the data (e.g. add it up). You don't store positive transactions in one field and negatives transactions in another. You put them in the same field. That way you can just SUM that field for the total.

You need to do both 1 & 2 above and the issue you are posting about is trivially solved with just a SUM()

Send me Your opinion and data structure to give my desires result

 

[plog]

I did, you quoted it.

 

[Shyamdharam2]

I did, you quoted it.

yes

 

[Gasman]

Send me Your opinion and data structure to give my desires result

No Please?

 

[MajP]

Send me Your opinion and data structure to give my desires result

You may have it already. What are you using Type for? If it is the values (Opening, Debit, Credit) then you have everthing you need in one table.
If that field is used for something else then add a field
TransactionType (Debit, Credit, Opening)

Also whatever "type" is used for rename it. The word "Type" is a reserved word in VBA and can cause problems

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

 

[CJ_London]

You need to clarify what type of accounting app you are developing.

Is it cash accounting type or accrual accounting type? Is it required to generate balance sheets, profit and loss reports?

Or is it a ledger type - only dealing with one aspect of accounts such as sales ledger or purchase ledger (which by definition would be part of accrual accounting)

What type of business is it for? Retail? Manufacturing? Banking? Estate agents?

How do you do business? Cash only?cheques? Online payments/reciepts? Mixture?

Is it required to integrate with other systems?

Until we know all that, we cannot provide any suggestions to you with any degree of confidence. We will be in a ‘gotcha’ loop. We suggest something and you come back with ‘ah yes but it also needs to do..,,’

 

[Shyamdharam2]

I Want Basic Query for Now....
I want only Ledger Balance of my Record Which I am Maintaing....


I Want A Query to Show me A particular Ledger Balance Still Date Report
I Will Input , Let Suppose Mr. A
In Opening Table I will Be Input X Amt Let Say 1000
In Bank Table Let one Day 50 Credit
another day 100 Credit
Then 200 Debit
At the End I want the Query show show Mr. A Ledger as Balance : 1000 + 50 + 100 -200 = 950

For That
Take Common MR. A
From Opening Table Credit Amt + Bank Table Credit - Bank Table Debit = Balance

And So Next Customer Mr B So On....

 

[mike60smart]

I Want Basic Query for Now....
I want only Ledger Balance of my Record Which I am Maintaing....


I Want A Query to Show me A particular Ledger Balance Still Date Report
I Will Input , Let Suppose Mr. A
In Opening Table I will Be Input X Amt Let Say 1000
In Bank Table Let one Day 50 Credit
another day 100 Credit
Then 200 Debit
At the End I want the Query show show Mr. A Ledger as Balance : 1000 + 50 + 100 -200 = 950

For That
Take Common MR. A
From Opening Table Credit Amt + Bank Table Credit - Bank Table Debit = Balance

And So Next Customer Mr B So On....

Hi
You need to create tables as shown in the Relationship Diagram attached.
This would then allow you to create a Main Form as shown in the attached screenshot based on tblCustomers,
with a Subform based on tblCustomersAccounts.
then another subform based on tblAccounts

 

[The_Doc_Man]

@Shyamdharam2

We will offer help and advice, but please be aware that YOU are the subject-matter expert here. We cannot do your work for you. We can only advise you in methodology. We can supply examples and links, but only you know the specifics of what you need. What you don't realize is that your problem will have little quirks, idiosyncrasies, special rules... something to make it unique... something that we can never really know using this method of communication. Be prepared to have to do things mostly on your own with our help when possible.

 

[Shyamdharam2]

Hi
You need to create tables as shown in the Relationship Diagram attached.
This would then allow you to create a Main Form as shown in the attached screenshot based on tblCustomers,
with a Subform based on tblCustomersAccounts.
then another subform based on tblAccounts

Do you have Anything sample data for understanding, the structures

 

[mike60smart]

Do you have Anything sample data for understanding, the structures

Hi
I have given you the Relationship Diagram so you can now create the tables based on this Diagram.
Once you have the tables created you then need to create Forms based on the Diagram.