CODEWITHSUNDEEP
sqlexcelms-accesscountsum
Cynass
Cynass

Reputation: 55

Countif and Sumif in Microsoft Access

I'm coming to you with a problem related to my Access database.

To keep it simple, I have two tables, Products and Customers :

Products table

Customers table

Those tables come from a simple Excel file I created to keep track of sales.

In this file, the fields ItemCount and TotalBill from the Customers table are calculated fields using countif and sumif as follows :

ItemCount : =COUNTIF(Products[Customer],[@Customer])

TotalBill : =SUMIF(Products[Customer],[@Customer],Products[Price])

I've realised with despair that MS Access don't offer those functions in the calculated fields, but there must be a way to do something so basic.

The tables must be kept separated.

If someone could help me to the way I'd be most grateful

Thank you all for reading !

Upvotes: 0

Views: 1219

Answers (2)

Harun24hr
Harun24hr

Reputation: 36870

You do not mention either you just want the result or you want to update Customers table columns with calculated value. So, if you want to display all the customer form customers table even if there is no transactions on Products table then use below query

SELECT Customers.CustomerID, Customers.Customer, P.Cnt AS ItemCount, P.Sm AS TotalBill
FROM (SELECT products.customer AS PC, Count(Products.Product) AS Cnt, Sum(Products.Price) AS Sm FROM Products GROUP BY Products.Customer)  AS P 
RIGHT JOIN Customers ON P.PC = Customers.Customer;

Although MS-Access discourage to store calculated value to tables [Run queries or report as need] but you can do that if you want. So, to update Customers table with values from Products table you can try below UPDATE query.

UPDATE Customers SET Customers.ItemCount = 
DCount("Product","Products","Customer= '" & Customers.Customer & "'"), Customers.TotalBill = 
DSum("Price","Products","Customer= '" & [Customers].[Customer] & "'");

enter image description here

Upvotes: 0

GMB
GMB

Reputation: 222482

This looks like a join and aggregation:

select
    c.customerid,
    c.customer,
    count(*) itemcount,
    sum(p.price) totalbill
from products p
inner join customer c on c.customer = p.customer
group by c.customerid, c.customer

Note that you shouldn't be using the name of the customer to relate the two tables, but instead the customer id, which, presumably, is the primary key of the customers table.

Upvotes: 1

Related Questions