Linking Tables w/same fields

[lisa626]

I have to link together two tables, one is a tickets table with a ticket number, date, total cost etc..., the other is an airseg table with a ticket number, depart date, depart time etc...

I need to link the two together via the ticket number, however, there may be multiple of the same ticket numbers in airseg but only one in the tickets table, how do I link together so it doesn't show me duplicates when I run the data???

 

[lynchg]

You can group by ticket number but you would need to use an aggregate function on the field(s) you want to display from the airseg table. Something like:

Select t.ticketnumber, max(a.date)
from tickets t inner join airseg a
on t.ticketnumber = a.ticketnumber
group by t.ticketnumber

 

[lisa626]

I'm sorry, need a bit further clarification, when I join the two tables, on what field do I link and what type of link do I use, THEN, where does the function above go, in a formula??

 

[lbass]

Link the tables on the ticket number. Then you could group on the ticket number and place the fields in the group header and suppress the detail section. The ticket will only appear once, with the information from the first airsegment record that is returned to the report. If you want certain specific information from the airsegment table, you should explain further.

-LB