How to display a ParentID with the corresponding SubID

[Nicholas Davenport]

Hi I hope someone is able to help me, I'm reasonably new to access so please excuse me if I don't explain myself correctly.

I have a data table called ConfirmationTab le where the Main field is called ParentID. The ParentID is the signature ID number for multiple records that are titled SubID. Within my datatable I have fields where there is 1 ParentID number with 5 or 6 SubID records. My table Looks like below.

ParentID*****Su bID*****Custome r
12345********12 346*****Jeff K
12345********12 347*****Jeff K
12345********12 348*****Jeff K
12345********12 349*****Jeff K
12345********12 350*****Jeff K

I am trying to create a form so that I can see the one ParentID and then have a subquery with all the corresponding SubID's in a list below the ParentID. I have tried linking the records together using the subquery function from within my form but when I go to display the records, I can see the ParentID but the SubID records will only appear individually to the ParentID or The ParentID will group all SubID's together but only show the first SubID number in the viewing list.

I tried to do a DISTINCTROW expression but couldn't get this to work. Below is how I would like my Form to display. Any Help would be gratefully appreciated. So thanks in advanced.

ParentID*****Su bID******Custom er
12345
*************12 346*****Jeff K
*************12 347*****Jeff K
*************12 348*****Jeff K
*************12 349*****Jeff K
*************12 350*****Jeff K

Apologies if I haven't explained myself correctly and if this has been posted on the wrong section of the site

[malcolmk]

Is this a textbook case of all customer details need to be stored in one table, parentid, custname,addres s, etc, etc
second table has parent id and subid plus data associated with subid? Link the parentid fields?
Or have I misunderstood?

[Nicholas Davenport]

Hi malcolm, thanks for the quick response

all the data is contained within 1 table called "confirmati on table" already.

It's getting the data to filter on my form as shown in the above explanation. Where there's one ParentId that can link with the corresponding SubId records where all the SubId's have the same ParentId, but on the form I would only like to see the ParentId number once for the multiple record SubId's that are attached to the ParenId......

Thanks in advanced....hop e I haven't confused you too much

[ADezii]

If you are interested, you can easily achieve the desired results using a Text Box and Unbound List Box.

[malcolmk]

ADezii, an interesting idea, tried it tying listbox to query which gets criteria from textbox and requery on change. Works great, did not think of doing it that way, seems to go against the grain to repeat customer data though. Still I learnt something from it and I'm sure it will get used by me in the future in some guise or other. :)

[ADezii]

seems to go against the grain to repeat customer data though

Could not agree with you more.

[Nicholas Davenport]

Hi guy's

Thanks for the idea. However I tried tying the list box to my query and it shows all the records from my query where as I just need to see the records with the same ParentID. I set the list box to tie into the query using the ParentId but still shows all the records from my query......I could send you a copy of my database to give either of you a better understanding of what I’m trying to achieve. Once again thanks for your help, its truly grateful as i'm getting to boiling point!

Nick

[ADezii]

If you could send a Copy of the DB, in Access 2003 Format, that would be ideal.

[Nicholas Davenport]

yeah where would you like me to send it?? can i Upload it on to here??

[ADezii]

Does it contain any kind of Personal Data, or Data that you do not wish to make available to everyone on this Formum?

[Nicholas Davenport]

No not really as i'll remove the address data from the tables. All the other data doesn't really matter! Unless you would like to send it to you directly??

[ADezii]

Just to play it safe, I'm now sending you my E-Mail Address in a Private Message, you can send it there. I won't get to it until tomorrow, since it is Thanksgiving where I am, and I'm about to gorge myself on Turkey & Stuffing!

[Nicholas Davenport]

Thanks for that, I'll get it zapped over to you shortly. Again thanks for your time and I hope you enjoy your thanksgiving. I don't really do Turkey myself even at Christmas.....I ’m more of a rib eye steak man myself, but none the less enjoy your feast