getting single row from many to many relationship

[aqif]

Hi

I have two table which look like this

TblPrimaryDiagnosis
-------------------
PriDiagID (primary diagnosis ID)
CancerID (type of cancer)
..plus other fields


TblPtDiagBasis
---------------
PriDiagID (primary diagnosis ID)
DiagBasisID (basis of diagnosis for primary diagnosis)
OtherComments


The idea behind this table structure is to store many Baisis of Diagnosis for one primary diagnosis. Now I need to export the data and the other site is only interested in the 1st Diagnosis that comes first in the list and dont want many rows for one patient. How can I do that. I cannot set the criteria as maybe tht basis of diagnosis is not present but other are. Can i try dlokup n stuff?>

Any suggestions?

Cheers!
Aqif

 

[The_Doc_Man]

One thought that comes to mind:

Do a query on your TblPtDiagBasis using the query grid. Find and click the Sigma button in the (Iconic) Tool Bar. This converts the query to a totals query. But that name is misleading.

Now bring in your PriDiagID field. Do a GroupBy on that.

Next, bring in your DiagBasisID field. Do a First on that.

Now you can save this query. It gives you the first basis ID for the rest of this operation.

Do another query on your TblPrimaryDiagnosis table. In the query grid, add the previous query. You'll get an automatic JOIN across the PriDiagID fields if you had a relationship between the two original tables. Now extract whatever you need for your report from that JOIN query.

 

[aqif]

Thanxx for response

Thanxx Doc_Man.....I actually figured out eventually as i di smthng like this

Select PriDuagId,(Select Top 1 DiagBasisId from TblPtDiagBasis Where TblPtDiagBasis.PriDiagId=TblPrimaryDiagnosis.PriDiagId),CancerID from TblPrimaryDiagnosis;

Got some other directions for me?

Cheers!
Aqif