# Query Criteria fomula (1 Viewer)

#### BoltV

##### New member
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).

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
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
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!
put a real table fieldname, it's hard to keep on guessing.

#### BoltV

##### New member
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) 1 Rex D \$100.00 2 Bob T \$200.00 V111 3 May X \$300.00 4 Abram C \$400.00 V222 5 Bob T \$100.00 6 Bob T \$230.00 7 Abram C \$300.00 8 Bob T \$100.00 V333 9 Abram C \$400.00 V444

#### Gasman

##### Enthusiastic Amateur
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
Thanks Gasman, Do I put that in the CustomerName criteria?

#### Gasman

##### Enthusiastic Amateur
No, the criteria is that the voucher not have any data.?

#### BoltV

##### New member
The criteria is the voucher should have data.

see query1.

#### Attachments

• voucherPayment.accdb
432 KB · Views: 265

#### Gasman

##### Enthusiastic Amateur
The criteria is the voucher should have data.
Ok, so change the criteria
I see I actually said initially 'Not Null or length > 0)

#### LanaR

##### Member
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
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

#### arnelgp

##### ..forever waiting... waiting for jellybean!
you upload a sample db. that for me is a Big font.

#### Gasman

##### Enthusiastic Amateur
I do not believe you need half of all that, but I will leave you in arnel's capable hands.

Replies
2
Views
297
Replies
48
Views
1,699
Replies
6
Views
529
Replies
1
Views
592
Replies
2
Views
1,089