Update one Access Table from a Query

Question

Maybe this is more complicated than it needs to be, or maybe it's simpler. Either way, it has been driving me nuts and I would really appreciate any help.

I've tried searching google and stack overflow, as well as a Safari Book on Access with no success, but maybe I just cannot clearly define what I want enough to find it.

I am creating a database in Access. Thus far I have 2 tables: Area Information Zone Information

Area Information has a table that has a "Zone" Column with a 2 letter abbreviation for which zone it belongs to. (Example: AA, GB, DT) Zone Information has a table that has "Abbreviation" as well as "Number of Areas"

What I would like (and here is where I may be completely off base...I thought I could do it in mySQL before, but I'm really quite new to Access):

I would like to be able to add new rows to Zone whenever I want, which will automatically populate with "Number of Areas" in that zone.

I think it needs to

SELECT Count([Area Information].Zone) AS CountOfZone, [Area Information].Zone
FROM [Area Information]
GROUP BY [Area Information].Zone

And from there,

HAVING ((([Area Information].Zone)=[Zone Information].Abbreviation));

Too bad this one doesn't work.

Lastly, it would need to then update the proper row in that column.

If anyone can help me clear this up, I would appreciate it. I've been trying everything I can think of, and my google-fu has gone soft on me.

Thanks!

Answer 1

One of the central concepts in relational database design is Normalization, which is a fancy word for saying "don't 'write down' the same piece of information in more than one place".

You think that you want to store the "Number of Areas" for each Zone in the [Zone Information] table, but you already have that information "written down" in the [Area Information] table. You just need a handy way to pull it out.

As you guessed, an aggregation (GROUP BY) query is the answer, and one like this...

SELECT [Area Information].Zone, Count(*) AS [Number of Areas]
FROM [Area Information]
GROUP BY [Area Information].Zone;

...will produce results like this:

Zone    Number of Areas
----    ---------------
AB      3
BC      3
NS      1
ON      2

If you save that query in Access as "AreaCountsByZone" then you can use that query just like a table in other queries you build, such as a query that shows the Area counts without actually storing the counts in the [Zone Information] table itself...

SELECT [Zone Information].Abbreviation, [Zone Information].[Zone Name], 
    AreaCountsByZone.[Number of Areas]
FROM AreaCountsByZone INNER JOIN [Zone Information] 
    ON AreaCountsByZone.Zone = [Zone Information].Abbreviation;

...which returns...

Abbreviation    Zone Name   Number of Areas
------------    ---------   ---------------
AB              Zone AB     3
BC              Zone BC     3
NS              Zone NS     1
ON              Zone ON     2

As you get more comfortable with SQL you may find it more convenient to skip the intermediate "saved query" and just do the whole thing in one shot:

SELECT [Zone Information].Abbreviation, [Zone Information].[Zone Name], 
    (SELECT Count(*) FROM [Area Information]
     WHERE [Area Information].Zone = [Zone Information].Abbreviation
    ) AS [Number of Areas]
FROM [Zone Information];

...which produces exactly the same result.