excel - Reporting Row and Column Names Connected to a Value (Microsoft Access) -


i'm out of depth on one, don't use databases much. hope suffices i've tried pay forward helping people indesign , photoshop on other websites!

i can use access or excel follows.

i have data looks like:

 year  president   vice 1980  reagan      bush sr. 1984  reagan      bush sr. 1988  bush sr.    quayle 1992  clinton     gore 1996  clinton     gore 2000  bush jr.    cheney 2004  bush jr.    cheney 2008  obama       biden 2012  obama       biden

i want report looks like:

 biden: vice 2008, 2012 bush jr.: president 2000, 2004 bush sr.: president 1988; vice 1980, 1984 cheney: vice 2000, 2004 clinton: president 1992, 1996 gore: vice 1992, 1996 obama: president 2008, 2012 quayle: vice 1988 reagan: president 1980, 1984

i'm having trouble figuring out how identify common name may appear anywhere on table, , how grab row , column labels report.

this simplified version of real data, doesn't have politicians. there ten column labels relevant, not two. "bush sr." gives example of 1 person holding 2 different offices.

there not cases same name appears in 2 different columns in same row, i'd prefer not rule out possibility, unless it's dramatically more complex allow that.

thanks!

the first thing need convert data "few rows, many columns" "few columns, many rows" via union query. (i saved test data in table called [n_column_table].)

select [year], "president" office, [president] person [n_column_table] union select [year], "vice" office, [vice] person [n_column_table]

if save query "3_column_data" can use table in other queries, reports, etc.. (you have add ~8 more union all constructs when build query real data.)

so our data looks this

year    office      person 1980    president   reagan 1984    president   reagan 1988    president   bush sr. 1992    president   clinton 1996    president   clinton 2000    president   bush jr. 2004    president   bush jr. 2008    president   obama 2012    president   obama 1980    vice        bush sr. 1984    vice        bush sr. 1988    vice        quayle 1992    vice        gore 1996    vice        gore 2000    vice        cheney 2004    vice        cheney 2008    vice        biden 2012    vice        biden

now, "gluing together" offices , years, we'll need use little vba function that. create module in access, , paste in following code

public function listterms(person string) string dim cdb dao.database dim rstoffice dao.recordset, rstyear dao.recordset dim result string, yearstring string  const yearseparator = ", " const officeseparator = "; "  set cdb = currentdb result = ""  set rstoffice = cdb.openrecordset( _         "select distinct office " & _         "from 3_column_data " & _         "where person=""" & replace(person, """", """""", 1, -1, vbbinarycompare) & """ " & _         "order 1") while not rstoffice.eof     yearstring = ""     set rstyear = cdb.openrecordset( _             "select distinct [year] " & _             "from 3_column_data " & _             "where person=""" & replace(person, """", """""", 1, -1, vbbinarycompare) & """ " & _                 "and office=""" & replace(rstoffice!office, """", """""", 1, -1, vbbinarycompare) & """ " & _             "order 1")     while not rstyear.eof         if len(yearstring) > 0             yearstring = yearstring & yearseparator         end if         yearstring = yearstring & rstyear!year         rstyear.movenext     loop     rstyear.close     set rstyear = nothing     if len(result) > 0         result = result & officeseparator     end if     result = result & rstoffice!office & " " & yearstring     rstoffice.movenext loop rstoffice.close set rstoffice = nothing set cdb = nothing listterms = result end function

now can use function in query list each person , terms in office

select personlist.[person], listterms(personlist.[person]) terms (select distinct person 3_column_data) personlist

which returns

person      terms biden       vice 2008, 2012 bush jr.    president 2000, 2004 bush sr.    president 1988; vice 1980, 1984 cheney      vice 2000, 2004 clinton     president 1992, 1996 gore        vice 1992, 1996 obama       president 2008, 2012 quayle      vice 1988 reagan      president 1980, 1984

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