How to do this?

  • Thread starter Thread starter Nich
  • Start date Start date
N

Nich

I have a query that gets median info for a user and I want
to combine the lines for each user into one memo field for
later usage...the current format:

user median detail month
JohnDoe 1 0 1,2004
JohnDoe 3 1 1,2004
JohnDoe 4 3 1,2004

So that means John had 1 0-day cycle time, 3 1-day and 4 3-
day for Jan 04...I want to combine this into this format:


user median detail month
JohnDoe 1 0, 3 1, 4 3 1,2004


Any help?
 
Nich

You need to use a cross-tab query. (Queries-> New Button-> Cross-tab Query Wizard)

Hope this helps

Jim
 
That doesn't work for this, as you can only choose first,
last, min, max etc for crosstab

I want to get all info in a field on line 1 and a field on
line 2 into the same string if line 1 and 2 have the same
username and month

JohnDoe 1 1 January
JohnDoe 2 4 January

so there is one instance of 1 and 2 instances of 4 for
JohnDoe for January, so I need a string that will be "1 1,
2 4" or even "1 1 2 4" that I'll then need to later parse
out for calculating median for a quarter

JohnDoe 1 2 February
JohnDoe 3 4 February

JohnDoe 5 6 March
JohnDoe 3 7 March

Those 3 months have to be recorded at the end of their
respective months as

JohnDoe 1 1 2 4 January
JohnDoe 1 2 3 4 February
JohnDoe 5 6 3 7 March



And then, if I want to get the whole quarter's Median for
JohnDoe, I'll have to parse those back out into

user count median month
JohnDoe 1 1 January
JohnDoe 2 4 January
JohnDoe 1 2 February
JohnDoe 3 4 February
JohnDoe 5 6 March
JohnDoe 3 7 March

or, even just as

JohnDoe 1,2,4,4,4,4,4,6,6,6,6,6,7,7,7 Q1

Those are then every instance for JohnDoe in Q1, so now I
just find the median of those, which is 6


So I can report


JohnDoe 4 January
JohnDoe 4 February
JohnDoe 6 March
JohnDoe 6 Q1



I'm looking into the concatenate function...any other
ideas of easier methods, perhaps code would be my best bet?
 
Hey, I saw that
I added a "family" 4 and then put my medians (0 1 3 2,
etc) into the tblFamMem under FirstName and month in
the "Role" column..however, if i change the famID from
number to text (so I can have user name there), I get an
error "Data type mismatch in criteria expression," but I
don't see where to change it to fix this...does the Concat
only work if i use a number field, or can i do this with
all text as well?
 
You would need to modify the sql expression in the Concatenate("sql
expression") function call. For instance, the sql expression would be
something like:
Concatenate("SELECT somefield FROM sometable WHERE [textfield]=""" &
[textfield] & """")
 
Back
Top