CODEWITHSUNDEEP
ms-access
lukso
lukso

Reputation: 589

Counting rows from related table in Access form

Example: I have three tables (Addresses, Customers and Payments). I would like to calculate a discount. But therefore I need to know how many customers are living on each address. I need to count customers for each address in my Payments form, while i'm selecting a Customer, i would like to know how many customers are living on this address. If i know it then i can calculate the discount.

Upvotes: 1

Views: 1813

Answers (1)

Fionnuala
Fionnuala

Reputation: 91336

You may be able to use DCount in the control source of a textbox, for example:

 =DCount("ID","Customers","AddressID=" & AddressID)

The above will count a field called ID in the customers table where the AddressID is the same as the AddressID on the current record of your form.

This is only an outline, you will have to make it suit your set-up.

Alternatively, you can create a query that counts customers for addresses and use DLookUp on the query.

Upvotes: 2

Related Questions