Need help to Self join a table (1 Viewer)

shery1995

Member
Local time
Today, 10:05
Joined
May 29, 2010
Messages
71
A table in my database has following fields:

ClientID, MatterID, TitleNo all three fields are compound key. Some time the same MatterID can have more than one TitleNo in which case they need to be displayed in a row on a report rather than as a two different records.

I am trying to work it out as self join but unable to get my head around it. For quick reference/understanding I am attaching the file there are three records in the table first two records have same ClientID, MatterID and two different TitleNos. as two records. Whereas the third record has same ClientID, MatterID and two different TitleNos, however, two different TitleNos have been recorded in single row. This was done intentionally for understanding purpose that what is required to achieve.. Any help suggestions highly appreciated.
 

Attachments

  • TitleDeedManager.accdb
    312 KB · Views: 183

plog

Banishment Pending
Local time
Today, 05:05
Joined
May 11, 2011
Messages
11,611
in which case they need to be displayed in a row on a report rather than as a two different records.

Can you paint a better picture of this? Using the data in the table you uploaded show me exactly how you want your report/query to look when displaying that data.

Also, can there be more than 2 related records? Is there a limit or is 10 possible?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:05
Joined
May 7, 2009
Messages
19,169
in my opinion the fields propertyAddress and PostCode should go to another Master table for the property
where the PK is the TitleNo.

the first 2 records are normal, the last is not.
you can follow same as the 2 preceeding records.

on report you can just concatenate them, see this sample.
 

Attachments

  • TitleDeedManager.zip
    31.1 KB · Views: 180

shery1995

Member
Local time
Today, 10:05
Joined
May 29, 2010
Messages
71
Can you paint a better picture of this? Using the data in the table you uploaded show me exactly how you want your report/query to look when displaying that data.

Also, can there be more than 2 related records? Is there a limit or is 10 possible?
Thank you for your reply. I have created another table which is a copy of the original table, in this table I have created additional field "TitleNo1" which is being used for second title no if exist, this seems to be a one of the way to get it resolved. However, I want to stick to original table which offers multiple title numbers entries, whereas the second table is limited to two titles numbers only. By comparing both tables I am looking to produce the out put/result as table "tblTitleDetails1" is showing, but I want to stick to the structure of tblTitleDetails. Yes, there could be more than two related records, no there is no limit.
 

Attachments

  • TitleDeedManager.accdb
    392 KB · Views: 174

shery1995

Member
Local time
Today, 10:05
Joined
May 29, 2010
Messages
71
in my opinion the fields propertyAddress and PostCode should go to another Master table for the property
where the PK is the TitleNo.

the first 2 records are normal, the last is not.
you can follow same as the 2 preceeding records.

on report you can just concatenate them, see this sample.
Thank you so much for your reply. Your sample is spot on, however is there any easiest/simple way to get the same result by creating a query?
 

Users who are viewing this thread

Top Bottom