Prev: user level security alternative in 2007
Next: Filter on a date working on one PC not not another
From: sparks on 26 May 2010 13:21


I have a query that has Var1 VAR2 VAR3 VAR4 in the output

each row is based on var4 having a 6 or a 12 and they variables are
all there.
But in a std report they would have something like

bob 1 2 3 6mo
bob 2 3 4 12mo


I need to get

bob 1 2 3 6mo 2 3 4 12mo


how do you get this output?
From: Salad on 26 May 2010 14:12
sparks(a)work.com wrote:
>
> I have a query that has Var1 VAR2 VAR3 VAR4 in the output
>
> each row is based on var4 having a 6 or a 12 and they variables are
> all there.
> But in a std report they would have something like
>
> bob 1 2 3 6mo
> bob 2 3 4 12mo
>
>
> I need to get
>
> bob 1 2 3 6mo 2 3 4 12mo
>
>
> how do you get this output?

I suppose there's some method to do so. I might do a
Select F1, F2, F3, F4, SecondRow([key]) As Row2 From ...

Then create a function in a module called SecondRow()
It would pass the key (empid) to function.

The function would be something like
Public FUnction SecondRow(lngID as long) As String
Select Top 2 F1, F2, F3, F4 From ....
set rst = currentdb....
rst.movelast
if rst.recordcount = 2 then
SecondRow = F1 & "*" & F2 & "*"...
endif
End Function

When reporting the secondrow data, use Split() to retrieve F1..F4 data.
From: sparks on 26 May 2010 14:19
Thanks for the idea. At first I just made 2 queries. 6mo and 12mo then
put the data together by last name.
UH why do I have about 50% of the records showing up?
wow bob jones and jones bob are the same person.
they are?
and wilma flintstone and monthly entry are the same person.
I gave up at that point and sent the data back for them to re
enter...clean up or whatever they want to call it.
If they every fix this mess I will give it a shot LOL

thanks again...




On Wed, 26 May 2010 11:12:10 -0700, Salad <salad(a)oilandvinegar.com>
wrote:

>sparks(a)work.com wrote:
>>
>> I have a query that has Var1 VAR2 VAR3 VAR4 in the output
>>
>> each row is based on var4 having a 6 or a 12 and they variables are
>> all there.
>> But in a std report they would have something like
>>
>> bob 1 2 3 6mo
>> bob 2 3 4 12mo
>>
>>
>> I need to get
>>
>> bob 1 2 3 6mo 2 3 4 12mo
>>
>>
>> how do you get this output?
>
>I suppose there's some method to do so. I might do a
> Select F1, F2, F3, F4, SecondRow([key]) As Row2 From ...
>
>Then create a function in a module called SecondRow()
>It would pass the key (empid) to function.
>
>The function would be something like
>Public FUnction SecondRow(lngID as long) As String
> Select Top 2 F1, F2, F3, F4 From ....
> set rst = currentdb....
> rst.movelast
> if rst.recordcount = 2 then
> SecondRow = F1 & "*" & F2 & "*"...
> endif
>End Function
>
>When reporting the secondrow data, use Split() to retrieve F1..F4 data.
From: Salad on 26 May 2010 17:58
sparks(a)work.com wrote:
> Thanks for the idea. At first I just made 2 queries. 6mo and 12mo then
> put the data together by last name.
> UH why do I have about 50% of the records showing up?
> wow bob jones and jones bob are the same person.
> they are?
> and wilma flintstone and monthly entry are the same person.
> I gave up at that point and sent the data back for them to re
> enter...clean up or whatever they want to call it.
> If they every fix this mess I will give it a shot LOL

That's one reason a contact/cust/emp id is handy...to link to tables.
Or have a counter like 1 & 2. Not sure how you are able to sort on the
data. Sometimes that's not possible when data comes in from external
sources but it's something to consider in planning.

> thanks again...

No prob. With your clean-up crew luck.


> On Wed, 26 May 2010 11:12:10 -0700, Salad <salad(a)oilandvinegar.com>
> wrote:
>
>
>>sparks(a)work.com wrote:
>>
>>>I have a query that has Var1 VAR2 VAR3 VAR4 in the output
>>>
>>>each row is based on var4 having a 6 or a 12 and they variables are
>>>all there.
>>>But in a std report they would have something like
>>>
>>>bob 1 2 3 6mo
>>>bob 2 3 4 12mo
>>>
>>>
>>>I need to get
>>>
>>>bob 1 2 3 6mo 2 3 4 12mo
>>>
>>>
>>>how do you get this output?
>>
>>I suppose there's some method to do so. I might do a
>> Select F1, F2, F3, F4, SecondRow([key]) As Row2 From ...
>>
>>Then create a function in a module called SecondRow()
>>It would pass the key (empid) to function.
>>
>>The function would be something like
>>Public FUnction SecondRow(lngID as long) As String
>> Select Top 2 F1, F2, F3, F4 From ....
>> set rst = currentdb....
>> rst.movelast
>> if rst.recordcount = 2 then
>> SecondRow = F1 & "*" & F2 & "*"...
>> endif
>>End Function
>>
>>When reporting the secondrow data, use Split() to retrieve F1..F4 data.
 | 
Pages: 1
Prev: user level security alternative in 2007
Next: Filter on a date working on one PC not not another