CODEWITHSUNDEEP
sqlms-access
Gandalfrandalf
Gandalfrandalf

Reputation: 257

Nesting Queries in Access SQL

I have recently started using SQL and am stuck when applying it to Access. My previous experience (limited) has been with PostgreSQL, and I understand that SQL in Microsoft Access requires you to nest queries into sub-queries, which I am not familiar with.

I believe the code in SQL (not for access would look something like this...)

select weeks, sum(sweets_eaten), count(distinct (sweet))
from table
group by weeks;

This would then give me a table where I would have the unique weeks, the sum of sweets eaten per week and the number of sweets per week.

Ideally, what the query would then do is also tell me the average sweets eaten per week by dividing the total sweets eaten per week by the number of sweets.

Does anyone know how to write a query so this will work in Microsoft Access?

Thanks!

Edited code, this is what I am entering

select f15, sum(f16), count(*)
from (select f15, sum(f16) as sum_sweets_eaten
from table1
group by f15, f16
) as t
group by f15;

For the average, would it be possible to do this in addition to the sum.

Upvotes: 0

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

The query you have written will not work in MS Access, because it does not support count(distinct).

You can pre-aggregate to get the result you want:

select weeks, sum(sum_sweets_eaten), count(*)
from (select weeks, sum(sweets_eaten) as sum_sweets_eaten
      from table
      group by weeks, sweet
     ) as t
group by weeks;

To get the average, use avg() rather than sum().

Upvotes: 2

Related Questions