 | |
trying to put 2 lines into 1
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
trying to put 2 lines into 1 -
05-26-2010
, 01:21 PM
#2
| |||
| |||
|
|
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? |
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.
#3
| |||
| |||
|
|
sparks (AT) work (DOT) 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. |
#4
| |||
| |||
|
|
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 |
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.
Quote:
|
thanks again... |
Quote:
|
On Wed, 26 May 2010 11:12:10 -0700, Salad <salad (AT) oilandvinegar (DOT) com wrote: sparks (AT) work (DOT) 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. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.