Hi Gurus,

I have a master-detail setup something like below

Master Detail
ID(PK) ID(PK) FK (Master)
-------- -------------
1 1 1
2 2 1
3 3 2
4 4 3
            5 3
            6 4
            7 4

I want a query that displays the data like this

ID
--------
1 - 1
1 - 2
2 - 1
3 - 1
3 - 2
3 - 3
4 - 1
4 - 2

The first digit is the Masters PK, the second digit is the Position/enumeration of the detail recordset. So if master has three detail records then the list should be (PK) - 1, (PK) - 2, etc. I am thinking along the lines of dlookup inside the query, or perhaps a sub query?!?

Any Ideas anyone?

hi,

Look at this Query based on pubs in MS-SQL SERVER, this might solve the issue

select * from
(
select ( Select count(au_Id) from titleauthor a where a.au_Id = b. au_Id and a.title_ID <= b.title_ID) enum , au_Id, title_ID from titleauthor b group by au_Id ,title_ID

) t order by au_Id


Prashant

Prashant,

Thanks for the response. I cannot get this to work. I think that the enum keyword is available in SQL but not Access. I can't find anything in there anyway.

thanks though! :)

hi,

Here enum is not a keyword(It may be a keyword in access thats why is is not working), It was an alias, change it to any name you wish.

Prashant.

Prashant,

After playing with the query it just seems to provide a count of items for each master record. Here is the Statement I Used -

SELECT * from
  (SELECT
      (SELECT Count(A.Item_Id) FROM
                   tbl_Items A WHERE
                   A.Item_Referral_ID = B.Referral_ID )
   AS Numbers , Referral_ID
   FROM tbl_Referrals B group by B.Referral_Id )

What I want is to enumerate the details records for each referral. Have I done something wrong in the statement?

Jon

Prashant,

Ignore my last post. I approached it the wrong way. I have got it working after noticing that the table is the same for both alias's DOH!

Here is what works

SELECT * from (
  SELECT (
    SELECT count(Item_Id) FROM tbl_Items A
         WHERE A.Item_Referral_Id = b.Item_Referral_Id AND a.Item_ID <= b.Item_ID
              ) AS RefItem , Item_Referral_Id, Item_ID
    FROM tbl_Items B GROUP BY Item_Referral_ID ,Item_ID ) order by Item_Referral_ID

Thanks Prashant for your help. :D

Jon