Update one Access Table from a Query -


maybe more complicated needs be, or maybe it's simpler. either way, has been driving me nuts , appreciate help.

i've tried searching google , stack overflow, safari book on access no success, maybe cannot define want enough find it.

i creating database in access. far have 2 tables: area information zone information

area information has table has "zone" column 2 letter abbreviation zone belongs to. (example: aa, gb, dt) zone information has table has "abbreviation" "number of areas"

what (and here may off base...i thought in mysql before, i'm quite new access):

i able add new rows zone whenever want, automatically populate "number of areas" in zone.

i think needs 

select count([area information].zone) countofzone, [area information].zone [area information] group [area information].zone

and there, 

having ((([area information].zone)=[zone information].abbreviation));

too bad 1 doesn't work.

lastly, need update proper row in column.

if can me clear up, appreciate it. i've been trying can think of, , google-fu has gone soft on me.

thanks!

one of central concepts in relational database design normalization, fancy word saying "don't 'write down' same piece of information in more 1 place".

you think want store "number of areas" each zone in [zone information] table, have information "written down" in [area information] table. need handy way pull out.

as guessed, aggregation (group by) query answer, , 1 this...

select [area information].zone, count(*) [number of areas] [area information] group [area information].zone;

...will produce results this:

zone    number of areas ----    --------------- ab      3 bc      3 ns      1 on      2

if save query in access "areacountsbyzone" can use query table in other queries build, such query shows area counts without storing counts in [zone information] table itself...

select [zone information].abbreviation, [zone information].[zone name],      areacountsbyzone.[number of areas] 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 more comfortable sql may find more convenient skip intermediate "saved query" , whole thing in 1 shot:

select [zone information].abbreviation, [zone information].[zone name],      (select count(*) [area information]      [area information].zone = [zone information].abbreviation     ) [number of areas] [zone information];

...which produces same result.


Comments

Post a Comment

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

i force app's home screen portrait , other views landscape. apple rejects app if this? if not , how force first screen launch in portrait mode? , force other screens launch in landscape. this not duplicate question search every possible answer in website. i force app's home screen portrait , other views landscape. apple rejects app if this? no apple not reject app this. have lot of apps doesn't support orientation. forcing on 1 screen won't cause app reject. if not , how force first screen launch in portrait mode? , force other screens launch in landscape. can on write : -(nsuinteger)supportedinterfaceorientations; function.
Read more

java Extracting Zip file -

i'm looking way extract zip file. far have tried java.util.zip , org.apache.commons.compress, both gave corrupted output. basically, input zip file contain 1 single .doc file. java.util.zip: output corrupted. org.apache.commons.compress: output blank file, 2 mb size. so far commercial software winrar work perfectly. there java library make use of this? this method using java.util library: public void extractzipnative(file filezip) { zipinputstream zis; stringbuilder sb; try { zis = new zipinputstream(new fileinputstream(filezip)); zipentry ze = zis.getnextentry(); byte[] buffer = new byte[(int) ze.getsize()]; fileoutputstream fos = new fileoutputstream(this.tempfolderpath+ze.getname()); int len; while ((len=zis.read(buffer))>0) { fos.write(buffer); } fos.flush(); fos.close(); } catch (filenotfoundexception e) { // todo auto-generated catch block ...
Read more

C# WinForm - loading screen -

i ask how make loading screen (just picture or something) appears while program being loaded, , disappears when program has finished loading. in fancier versions, have seen process bar (%) displayed. how can have that, , how calculate % show on it? i know there form_load() event, not see form_loaded() event, or % property / attribute anywhere. all need create 1 form splash screen , show before main start showing landing page , close splash once landing page loaded. class splashform { //delegate cross thread call close private delegate void closedelegate(); //the type of form displayed splash screen. private static splashform splashform; static public void showsplashscreen() { // make sure launched once. if (splashform != null) return; thread thread = new thread(new threadstart(splashform.showform)); thread.isbackground = true; thread.setapartmentstate(apartmentstate.sta); thread....
Read more