The younger brother is access a novice, there is a problem, many queries is beside the point.
In this to you for advice, ask for help, thanks!
There are two tables in access database, tablea and tableb respectively.
Below is the tablea form (figure 1)


Below is tableb form (figure 2)


I hope to get the result: (figure 3)


Set up the query, SQL statements are as follows:
SELECT tablea. Customers, tablea area, the count (annual) as number of transactions, the sum (transaction) as the total trade amount
The from the tablea left join tableb on tablea. Customer=tableb.
Where features="large"
Group by tablea. Customers, tablea region;

But get the result: (figure 4)


Ask you for help, how can I do to get the results in figure 3.
Request advice, thanks!

CodePudding user response:

SELECT a. *, transaction number, b. b. accumulative total transaction amount
The FROM tablea a
LEFT the JOIN (
SELECT clients, the count (annual) as number of transactions, the sum (transaction) as the total trade amount
The FROM tableb
Where features="large"
Group by customer) on a. b=b. customers