Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 17th, 2005, 02:23 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default Enumerate Master - Detail Query Recs

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?

 
Old May 17th, 2005, 07:23 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old May 17th, 2005, 07:50 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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! :)

 
Old May 17th, 2005, 08:08 AM
Friend of Wrox
 
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old May 17th, 2005, 08:34 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old May 17th, 2005, 09:05 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 100
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
master-detail hhpatek ADO.NET 0 April 3rd, 2008 02:53 PM
Master Detail Records Amsterdaz ASP.NET 2.0 Basics 4 November 10th, 2007 10:58 AM
Master Detail prasanta2expert Access VBA 1 October 1st, 2007 06:37 AM
Master/Detail page yteferi ASP.NET 2.0 Basics 1 April 5th, 2006 04:06 PM
master/detail beeyule Dreamweaver (all versions) 1 January 18th, 2005 02:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.