dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] Table has two different fields, related to the same table

  1. #1

    Thread Starter
    Hyperactive Member jazFunk's Avatar
    Join Date
    Dec 2008
    Location
    Flint, MI
    Posts
    397

    Resolved [RESOLVED] Table has two different fields, related to the same table

    This is an MS Access database

    I know, the title is vague as I'm really struggling with how to word this question.. Here goes..

    I have a table with two fields, pointing to different records, in the same related table. I need to pull this data together to build a report. I've created some sub-queries, but, I'm certain there's an easier/simpler way to achieve this.

    Any help is appreciated.
    Things I've found useful:
    DateTime.ToString() Patterns | Retrieving and Saving Data in Databases | ADO.NET Data Containers: An Explanation

    Quote of the day from jmcilhinney:
    'Talking about Option Strict and Option Explicit in the same sentence is pointless unless it is to say that they should both be On.'

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,199

    Re: Table has two different fields, related to the same table

    I'm not certain I understand what you mean, but assuming it is something like one field for "Manager" and one for "Subordinate", and they both have references to a "People" table... you can join using aliases, eg:
    Code:
    SELECT MainTable.*, Manager.Name, Subordinate.Name, ...
    
    FROM (MainTable
    INNER JOIN People as Manager ON Manager.ID = MainTable.ManagerID)
    INNER JOIN People as Subordinate ON Subordinate.ID = MainTable.SubordinateID
    You can then refer to them independently using the aliases (as I did in the Select clause), and cannot refer to them using the "real" table name (People in this example).

  3. #3

    Thread Starter
    Hyperactive Member jazFunk's Avatar
    Join Date
    Dec 2008
    Location
    Flint, MI
    Posts
    397

    Re: Table has two different fields, related to the same table

    Yes, that is seemingly a perfect analogy. Here's a screenshot of each table, the highlighted fields from the table on the left both correspond to a record in the table on the right. I'll give your suggestion a try.

    Name:  TwoFieldsOneTable.jpg
Views: 34
Size:  30.3 KB
    Things I've found useful:
    DateTime.ToString() Patterns | Retrieving and Saving Data in Databases | ADO.NET Data Containers: An Explanation

    Quote of the day from jmcilhinney:
    'Talking about Option Strict and Option Explicit in the same sentence is pointless unless it is to say that they should both be On.'

  4. #4

    Thread Starter
    Hyperactive Member jazFunk's Avatar
    Join Date
    Dec 2008
    Location
    Flint, MI
    Posts
    397

    Re: Table has two different fields, related to the same table

    So, if I'm applying my object names to your SQL, tell me if this looks correct..?

    SELECT tblStationStatus.*, Legend.lot, Legend.drum, Covered.lot, Covered.drum, ...

    FROM (tblStationStatus
    INNER JOIN tblSheetingDetails as Legend ON Legend.ID = tblStationStatus.LotLegend)
    INNER JOIN tblSheetingDetails as Covered ON Covered.ID = tblStationStatus.LotCovered
    Things I've found useful:
    DateTime.ToString() Patterns | Retrieving and Saving Data in Databases | ADO.NET Data Containers: An Explanation

    Quote of the day from jmcilhinney:
    'Talking about Option Strict and Option Explicit in the same sentence is pointless unless it is to say that they should both be On.'

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,199

    Re: Table has two different fields, related to the same table

    That looks right

    Does it return what you'd hoped?

  6. #6

    Thread Starter
    Hyperactive Member jazFunk's Avatar
    Join Date
    Dec 2008
    Location
    Flint, MI
    Posts
    397

    Re: Table has two different fields, related to the same table

    Indeed, it did. Outstanding and simple. I actually get it. Haha. Thank you very much!
    Things I've found useful:
    DateTime.ToString() Patterns | Retrieving and Saving Data in Databases | ADO.NET Data Containers: An Explanation

    Quote of the day from jmcilhinney:
    'Talking about Option Strict and Option Explicit in the same sentence is pointless unless it is to say that they should both be On.'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width