Tough query logic/problem... for the experts!!!! 1

[MikeDNova]

Here's the deal, I have a query that is built off two tables.

There is a left outer join from table1 to table2, and table2 has multiple returns for each record in table1.


table1 table2

key --------------> key
key
key

Now this means that one record from table1 will match up with X records from table1 and form X many rows in the query.

There is one column from table2 that I would return the value for only the first occurence of that key combination. And then for each subsequent record of that key combination return "0"

I realize this might be a little difficult to understand since I'm having a tough time explaining it. Please ask any questions you need to help.

Thanks a lot in advance!

-Mike

 

[swampBoogie]

That's easier to fix in the application. Retrieve the data ordered by key and keep track of when the value changes


<ASP code>
set rs = connect.execute(&quot;select * from t1 inner join t2 on t1.key = t2.key order by t1.key&quot
key = &quot;&quot;
while not rs.eof
if key <> &quot;&quot; and key <> rs(&quot;key&quot then
response.write &quot;New key value&quot; & rs(&quot;key&quot & &quot;<br>&quot;
key = rs(&quot;key&quot
end if
'write rest of data
rs.movenext
wend
</ASP code>

 

[MikeDNova]

Yeah i knew there would be a way of doing it with code, but i was trying to avoid that. I might have to anyways.

Thanks
Mike

 

[Louiegee]

Hey Mike
Maybe this will do it; you can call this function from within a query:

Global keepdata as long ' this line in declaration section

Public Function testdup(indata as long) As long
If indata <> keepdata Then
keepdata = indata
testdup = indata
Else
testdup = 0
End If
End Function

Save this in a module and then call this function from within a query; feed this function your field you want &quot;fixed up&quot;.
I've tested this in a query and it works. It does funny things on-screen but the query prints correctly and it should feed a report ok (as long as the report doesn't do any retreats, then you may start to get funny stuff).
Also if you run this twice and the last key number from the last run matches the first key number from the current run, you'll erroneously start out with a zero. (Oh well)

LouieGee

 

[MikeDNova]

thanks for the help guys!

 

[danvlas]

Create the query to return the normal values and include the ID of table 2 in it.
Create a new query to return only the first occurence and include the ID of table 2.

Left join the two queries in a new one and create a calculated field: Nz(occurenceField, 0)

It should work without functions...

Good luck

Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html