Mark A Matte
markamatte at hotmail.com
Mon Nov 5 12:24:23 CST 2007
Gustav,
Thanks for pointing this out as I typically don't read my own posts. I'm not sure what the problem is...I think this is happening when I reply to the list...as a test I will send this email to you directly...then hit "back" and send it to the list...please let me know the results.
Thanks,
Mark A. Matte
From: markamatte at hotmail.comTo: accessd at databaseadvisors.comSubject: RE: [AccessD] ...gurus onlyDate: Fri, 2 Nov 2007 20:44:24 +0000
> ...now if only I could learn to swallow my pride and just ask before > spending days running in circles. ...ouch...you're gonna hate this suggestion. Guessing from your description below...you used about 3 fields in your original table to get this output. We'll use the fields:field1=Set---this is where your data1,data2 stuff wentfield2=Value---this is where your 1,2,5,4,2,3,4...wentfield3=Ltr--(letter)...where your w,x,y,z Assuming this is how your data is stored...the results you want can be achieved using a single crosstab. Good Luck, Mark A. Matte ************SQL*************TRANSFORM Sum(tblDataTest.Value) AS SumOfValueSELECT tblDataTest.Set, Max(tblDataTest.Value) AS MaxOfValue, Count(*) AS SeriesFROM tblDataTestGROUP BY tblDataTest.SetPIVOT tblDataTest.Ltr;************SQL*************> From: wdhindman at dejpolsystems.com> To: accessd at databaseadvisors.com> Date: Fri, 2 Nov 2007 16:32:10 -0400> Subject: Re: [AccessD] ...gurus only> > ...tks gustav ...I'll give this a shot ...suddenly my cup runneth over > ...ain't no place like AccessD :)> ...now if only I could learn to swallow my pride and just ask before > spending days running in circles.> > William> > ----- Original Message ----- > From: "Gustav Brock" <Gustav at cactus.dk>> To: <accessd at databaseadvisors.com>> Sent: Friday, November 02, 2007 4:16 PM> Subject: Re: [AccessD] ...gurus only> > > > Hi William> >> > 1) No problem.> >> > 2) Sorry, now I understand.> > Use this for Series:> >> > IIf([First]+[Last]=0,> > 0,> > 1+[Last]-[First]) AS> > Series> >> > 3) If you expect "" for empty fields, values must be strings. That's why I > > use Val() to convert to Integers.> >> > /gustav> >> >> >>>> wdhindman at dejpolsystems.com 02-11-2007 21:05:02 >>>> > ...thanks gustav but:> > ...I'm busted again ...there was an error in the data3 max ...sigh> > ...given:> >> > fields w x y z max series> > data1 1 1 1 2> > data2 1 5 5 3> > data3 1 4 3 4 4> > data4 2 3 3 2> > data5 1 1 1 1 1 4> > data6 1 1 1> >> > 1) where max is the largest number in w/x/y/z and> > 2) where series is the total number of fields between the first one> > containing a> > number and the last containing a number, not the total of the field> > contents.> > 3) where w/x/y/z are long integers for some stupid reason since it will> > allways be a single digit> >> > ...I ask for guru help and everyone starts exposing why I need it :(> > ...there is a desperate shortage of caffiene in my bloodstream ...that's > > my> > story and I'm sticking to it :)> >> > William> >> > ----- Original Message ----- > > From: "Gustav Brock" <Gustav at cactus.dk>> > To: <accessd at databaseadvisors.com>> > Sent: Friday, November 02, 2007 3:40 PM> > Subject: Re: [AccessD] ...gurus only> >> >> >> Hi William> >>> >> Here is one method using Choose():> >>> >> SELECT> >> ID,> >> w,> >> x,> >> y,> >> z,> >> IIf(Val("" & [w])>Val("" & [x]),> >> Val("" & [w]),> >> Val("" & [x])) AS Max12,> >> IIf(Val("" & [y])>Val("" & [z]),> >> Val("" & [y]),> >> Val("" & [z])) AS Max34,> >> IIf(Val("" & [w])>0,> >> 1,> >> IIf(Val("" & [x])>0,> >> 2,> >> IIf(Val("" & [y])>0,> >> 3,> >> IIf(Val("" & [z])>0,> >> 4,> >> 0)))) AS> >> First,> >> IIf(Val("" & [z])>0,> >> 4,> >> IIf(Val("" & [y])>0,> >> 3,> >> IIf(Val("" & [x])>0,> >> 2,> >> IIf(Val("" & [w])>0,> >> 1,> >> 0)))) AS> >> Last,> >> IIf([Max12]>[Max34],> >> [Max12],> >> [Max34]) AS> >> Max,> >> IIf([First]=[Last],> >> Val(Choose([First],[w],[x],[y],[z])),> >>> >> Val(Choose([First],[w],[x],[y],[z]))+Val(Choose([Last],[w],[x],[y],[z])))> >> AS> >> Series> >> FROM> >> tblGuru;> >>> >>> >> This will return, which differs from your example but is according to > >> your> >> specification:> >>> >> Max Series> >> 1 2> >> 5 6> >> 4 4> >> 3 5> >> 1 2> >> 1 1> >>> >> /gustav> >>> >>>>> wdhindman at dejpolsystems.com 02-11-2007 19:45:35 >>>> >> ...ok, you too, eh :)> >>> >> ...given:> >>> >> fields w x y z max series> >> data1 1 1 1 2> >> data2 1 5 5 3> >> data3 1 4 3 3 4> >> data4 2 3 3 2> >> data5 1 1 1 1 1 4> >> data6 1 1 1> >>> >> 1) where max is the largest number in w/x/y/z and> >> 2) where series is the total fields between the first one containing a> >> number and the last containing a number> >> 3) where empty fields contain "" rather than nulls> >>> >> ...two days and I've yet to come up with an answer to fill the max and> >> series fields from the data in w/x/y/z.> >>> >> ...help!> >>> >> William> >> >> >> > -- > > AccessD mailing list> > AccessD at databaseadvisors.com> > http://databaseadvisors.com/mailman/listinfo/accessd> > Website: http://www.databaseadvisors.com > > > -- > AccessD mailing list> AccessD at databaseadvisors.com> http://databaseadvisors.com/mailman/listinfo/accessd> Website: http://www.databaseadvisors.com
Peek-a-boo FREE Tricks & Treats for You! Get 'em!
_________________________________________________________________
Peek-a-boo FREE Tricks & Treats for You!
http://www.reallivemoms.com?ocid=TXT_TAGHM&loc=us