Query Criteria fomula (1 Viewer)

BoltV

New member
Local time
Today, 13:05
Joined
Oct 19, 2021
Messages
8
Hi,
I am planning to make the Query with the following fields (in illustration below).

I would like to sum the total Purchase Amount (F3) who has Paid (F4) per Customer Name (F2).

For example:
Bob T has paid $200.00 (Purchase Number 2 in F1) and $100.00 (Purchase Number 9 in F1). Query result will show Bob T with Sum of Paid Amount of $300.00 (it excludes the unpaid transaction of Purchase Number 5 & 6).


Thanks in Advance.

Purchase Number (F1)Customer Name (F2)Purchase Amount (F3)Paid? (if not paid leave empty) (F4)
1Rex D$100.00
2Bob T$200.00Paid
3May X$300.00
4Abram C$400.00Paid
5Bob T$100.00
6Bob T$230.00
7Abram C$300.00
9Bob T$100.00Paid
 

LanaR

Member
Local time
Today, 15:05
Joined
May 20, 2021
Messages
87
Welcome to the forum

If you create a Totals Query, you can group on Customer name and Sum the Paid amounts where Paid is "True". The SQL will look something like;

Code:
SELECT Table1.CustName, Sum(Table1.Amount) AS SumOfAmount, Table1.Paid
FROM Table1
GROUP BY Table1.CustName, Table1.Paid
HAVING (((Table1.CustName)="Bob T") AND ((Table1.Paid)=True));

Changing Table1 for your Table name. I have also assumed that the Paid filed is a yes/no field

I'm going to assume that the above is only illustrative and that you have a Normalised table structure.
 
Last edited:

BoltV

New member
Local time
Today, 13:05
Joined
Oct 19, 2021
Messages
8
Hi LanaR,

Thanks for such a prompt reply. Appreciate it.

Yes, the above is for illustrative purpose. The Paid field is actually VoucherNumber. So what I am looking for is IF there is VoucherNumber, I would like to SUM only the amount with VoucherNumber. Yes, I have normalised table structure. Where do I put the SQL in the Query? My apologies, I am fairly new to this.

Much blessings.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:05
Joined
May 7, 2009
Messages
16,393
put a real table fieldname, it's hard to keep on guessing.
 

BoltV

New member
Local time
Today, 13:05
Joined
Oct 19, 2021
Messages
8
Here's the real table:
So i wanted the query to
Sum CustomerName and Purchase Amount that has VoucherNumber.

Example:
Bob T $300
Abram C. $800


Purchase Number (F1)Customer Name (F2)Purchase Amount
(F3)
Voucher Number (F4)
1Rex D$100.00
2Bob T$200.00V111
3May X$300.00
4Abram C$400.00V222
5Bob T$100.00
6Bob T$230.00
7Abram C$300.00
8Bob T$100.00V333
9Abram C$400.00V444
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:05
Joined
Sep 21, 2011
Messages
10,531
Group by CustomerName, Sum() PurchaseAmount Where VoucherNumber is not Null ( or check it's length)
Surround your fieldnames with [] due to having spaces in them😔
 

BoltV

New member
Local time
Today, 13:05
Joined
Oct 19, 2021
Messages
8
Thanks Gasman, Do I put that in the CustomerName criteria?
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:05
Joined
Sep 21, 2011
Messages
10,531
No, the criteria is that the voucher not have any data.?
Use the qbe gui, I always start with that.
 

BoltV

New member
Local time
Today, 13:05
Joined
Oct 19, 2021
Messages
8
The criteria is the voucher should have data.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:05
Joined
May 7, 2009
Messages
16,393
see query1.
 

Attachments

  • voucherPayment.accdb
    432 KB · Views: 265

LanaR

Member
Local time
Today, 15:05
Joined
May 20, 2021
Messages
87
Where do I put the SQL in the Query? My apologies, I am fairly new to

When you go to the query builder you can view your query in design mode (the default when you first start building a query). At the top left of your ribbon you will see a button called View a small drop down arrow, from which you can select Datasheet View (which will show you your results) Design View and SQL View into which you can paste of type SQL
 

BoltV

New member
Local time
Today, 13:05
Joined
Oct 19, 2021
Messages
8
I'll post screenshot of what I meant. I would like to sum up the records that has VoucherNumber group by VendorName. Ignore or excludes the ones without VoucherName. In this case, the query should only show this result with VoucherNumber
Bob T with total of 1100.00,
Kay C with total of 400.00
Rex M with total of 700.00

Access Photos3.png
Access Photos2.png
Access Photos.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:05
Joined
May 7, 2009
Messages
16,393
you upload a sample db. that for me is a Big font.
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:05
Joined
Sep 21, 2011
Messages
10,531
I do not believe you need half of all that, but I will leave you in arnel's capable hands.
 

Users who are viewing this thread

Top Bottom