Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Building a query to get relevant names for FKs

Discussion in 'Information Technology' started by Lisa, Jul 28, 2006.

  1. Lisa

    Lisa
    Expand Collapse
    Guest

    Hi,

    I have a question regarding building a query to get a relevant name for a
    foreign key.

    My database structure is as follows:

    Table:ConfigOptions
    ID
    Name
    Location

    Table:Dependencies
    ID
    ConfigOptionID
    DependencyID

    Table:Location
    ID
    LocationDescription

    What I need to do is build a query that returns ConfigOption Name,
    ConfigOption Location, Dependency Name, Dependency Location

    Using the following query, I can get the real name for ConfigOption Name,
    ConfigOption Location, and Dependency Name, but only the FK value for
    Dependency Location.

    SELECT StaticConfigOptions.parameterName AS Parameter,
    StaticConfigOptions.parameterLocation,
    (select StaticConfigOptions.parameterName from StaticConfigOptions where
    StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    DependencyLocationID
    FROM StaticConfigOptions INNER JOIN Dependencies ON
    StaticConfigOptions.parameterID=Dependencies.parameterID;

    How do I get the real name (located in the parameterLocation table under
    LocationDescription) to appear?

    Whatever I try to do seems to crash Access.

    Thank you!!
     
  2. Loading...

    Similar Threads Forum Date
    Sikh Some Bridge Building Sikh Sikhi Sikhism May 10, 2016
    Interfaith Building A Sikh Paradigm For Interfaith Work: Part 2 Interfaith Dialogues Apr 29, 2016
    USA Houston Sikh woman’s rights possibly violated by ban on Kirpans in IRS building Breaking News Nov 22, 2013
    India Cash crunch: Punjab to mortgage buildings Breaking News Aug 25, 2013
    India Deadly Building Collapse near Mumbai Breaking News Apr 5, 2013

  3. Michael H

    Michael H
    Expand Collapse
    Guest

    Lisa,

    Have you described the table structure correctly? I'm not sure why the
    Dependencies table would have a DependencyID field. Is that field actually
    located in the Location table?

    By the way, it is not a good idea to have a field called "Name". That is a
    reserved word in Access. A better field name would be something like
    "OptionName".

    -Michael


    "Lisa" wrote:

    > Hi,
    >
    > I have a question regarding building a query to get a relevant name for a
    > foreign key.
    >
    > My database structure is as follows:
    >
    > Table:ConfigOptions
    > ID
    > Name
    > Location
    >
    > Table:Dependencies
    > ID
    > ConfigOptionID
    > DependencyID
    >
    > Table:Location
    > ID
    > LocationDescription
    >
    > What I need to do is build a query that returns ConfigOption Name,
    > ConfigOption Location, Dependency Name, Dependency Location
    >
    > Using the following query, I can get the real name for ConfigOption Name,
    > ConfigOption Location, and Dependency Name, but only the FK value for
    > Dependency Location.
    >
    > SELECT StaticConfigOptions.parameterName AS Parameter,
    > StaticConfigOptions.parameterLocation,
    > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > DependencyLocationID
    > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > StaticConfigOptions.parameterID=Dependencies.parameterID;
    >
    > How do I get the real name (located in the parameterLocation table under
    > LocationDescription) to appear?
    >
    > Whatever I try to do seems to crash Access.
    >
    > Thank you!!
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    I hard a hard time on this one as your table and field names do not match
    those in the SQL. Also your SQL just would not fly as posted but I think I
    figured out what you wanted. Try the SQL below and add or remove display
    fields as you wish.

    SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    StaticConfigOptions.parameterLOCATION, Location.LocationDescription,
    Location_1.LocationDescription AS [Dependent Location]
    FROM (StaticConfigOptions LEFT JOIN Location ON
    StaticConfigOptions.parameterLOCATION = Location.ID) LEFT JOIN (Dependencies
    LEFT JOIN Location AS Location_1 ON Dependencies.DependencyID =
    Location_1.ID) ON StaticConfigOptions.parameterID = Dependencies.parameterID;


    "Lisa" wrote:

    > Hi,
    >
    > I have a question regarding building a query to get a relevant name for a
    > foreign key.
    >
    > My database structure is as follows:
    >
    > Table:ConfigOptions
    > ID
    > Name
    > Location
    >
    > Table:Dependencies
    > ID
    > ConfigOptionID
    > DependencyID
    >
    > Table:Location
    > ID
    > LocationDescription
    >
    > What I need to do is build a query that returns ConfigOption Name,
    > ConfigOption Location, Dependency Name, Dependency Location
    >
    > Using the following query, I can get the real name for ConfigOption Name,
    > ConfigOption Location, and Dependency Name, but only the FK value for
    > Dependency Location.
    >
    > SELECT StaticConfigOptions.parameterName AS Parameter,
    > StaticConfigOptions.parameterLocation,
    > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > DependencyLocationID
    > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > StaticConfigOptions.parameterID=Dependencies.parameterID;
    >
    > How do I get the real name (located in the parameterLocation table under
    > LocationDescription) to appear?
    >
    > Whatever I try to do seems to crash Access.
    >
    > Thank you!!
    >
     
  5. Lisa

    Lisa
    Expand Collapse
    Guest

    Hi Karl,

    It didn't quite work for what is needed. Let me try and explain it a little
    more (I'll the true table names -- I thought real table names would be
    cumbersome, but I guess the opposite was true in this case, sorry)

    Table: StaticConfigOptions
    parameterID
    parameterName
    parameterLocation
    ....

    Table: ParameterLocation
    parameterLocationID
    parameterLocationDescription
    ....

    Table: Dependencies
    dependencyID
    parameterID
    dependency

    (the dependency table is a table with a primary key, dependencyID, and two
    foreign keys, both from the parameterID field from the StaticConfigOptions
    table)

    The Dependencies table is linked to the StaticConfigOptions table and the
    StaticConfigOptions table is also linked to the ParameterLocation table.

    So far I can get a query that tells me the Parameter Name, the Location, and
    the Dependency. What I'm missing is the Dependency Location. I can get the
    numerical value, but not that elusive parameterLocationDescription.

    Thank you very much for all your help!

    "KARL DEWEY" wrote:

    > I hard a hard time on this one as your table and field names do not match
    > those in the SQL. Also your SQL just would not fly as posted but I think I
    > figured out what you wanted. Try the SQL below and add or remove display
    > fields as you wish.
    >
    > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > StaticConfigOptions.parameterLOCATION, Location.LocationDescription,
    > Location_1.LocationDescription AS [Dependent Location]
    > FROM (StaticConfigOptions LEFT JOIN Location ON
    > StaticConfigOptions.parameterLOCATION = Location.ID) LEFT JOIN (Dependencies
    > LEFT JOIN Location AS Location_1 ON Dependencies.DependencyID =
    > Location_1.ID) ON StaticConfigOptions.parameterID = Dependencies.parameterID;
    >
    >
    > "Lisa" wrote:
    >
    > > Hi,
    > >
    > > I have a question regarding building a query to get a relevant name for a
    > > foreign key.
    > >
    > > My database structure is as follows:
    > >
    > > Table:ConfigOptions
    > > ID
    > > Name
    > > Location
    > >
    > > Table:Dependencies
    > > ID
    > > ConfigOptionID
    > > DependencyID
    > >
    > > Table:Location
    > > ID
    > > LocationDescription
    > >
    > > What I need to do is build a query that returns ConfigOption Name,
    > > ConfigOption Location, Dependency Name, Dependency Location
    > >
    > > Using the following query, I can get the real name for ConfigOption Name,
    > > ConfigOption Location, and Dependency Name, but only the FK value for
    > > Dependency Location.
    > >
    > > SELECT StaticConfigOptions.parameterName AS Parameter,
    > > StaticConfigOptions.parameterLocation,
    > > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > > DependencyLocationID
    > > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > > StaticConfigOptions.parameterID=Dependencies.parameterID;
    > >
    > > How do I get the real name (located in the parameterLocation table under
    > > LocationDescription) to appear?
    > >
    > > Whatever I try to do seems to crash Access.
    > >
    > > Thank you!!
    > >
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Try this --
    SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    ParameterLocation.parameterLocationDescription,
    ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    StaticConfigOptions.parameterLOCATION =
    ParameterLocation.parameterLocationID) LEFT JOIN Dependencies ON
    StaticConfigOptions.parameterID = Dependencies.parameterID) LEFT JOIN
    ParameterLocation AS ParameterLocation_1 ON Dependencies.DependencyID =
    ParameterLocation_1.parameterLocationID;


    "Lisa" wrote:

    > Hi Karl,
    >
    > It didn't quite work for what is needed. Let me try and explain it a little
    > more (I'll the true table names -- I thought real table names would be
    > cumbersome, but I guess the opposite was true in this case, sorry)
    >
    > Table: StaticConfigOptions
    > parameterID
    > parameterName
    > parameterLocation
    > ...
    >
    > Table: ParameterLocation
    > parameterLocationID
    > parameterLocationDescription
    > ...
    >
    > Table: Dependencies
    > dependencyID
    > parameterID
    > dependency
    >
    > (the dependency table is a table with a primary key, dependencyID, and two
    > foreign keys, both from the parameterID field from the StaticConfigOptions
    > table)
    >
    > The Dependencies table is linked to the StaticConfigOptions table and the
    > StaticConfigOptions table is also linked to the ParameterLocation table.
    >
    > So far I can get a query that tells me the Parameter Name, the Location, and
    > the Dependency. What I'm missing is the Dependency Location. I can get the
    > numerical value, but not that elusive parameterLocationDescription.
    >
    > Thank you very much for all your help!
    >
    > "KARL DEWEY" wrote:
    >
    > > I hard a hard time on this one as your table and field names do not match
    > > those in the SQL. Also your SQL just would not fly as posted but I think I
    > > figured out what you wanted. Try the SQL below and add or remove display
    > > fields as you wish.
    > >
    > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > StaticConfigOptions.parameterLOCATION, Location.LocationDescription,
    > > Location_1.LocationDescription AS [Dependent Location]
    > > FROM (StaticConfigOptions LEFT JOIN Location ON
    > > StaticConfigOptions.parameterLOCATION = Location.ID) LEFT JOIN (Dependencies
    > > LEFT JOIN Location AS Location_1 ON Dependencies.DependencyID =
    > > Location_1.ID) ON StaticConfigOptions.parameterID = Dependencies.parameterID;
    > >
    > >
    > > "Lisa" wrote:
    > >
    > > > Hi,
    > > >
    > > > I have a question regarding building a query to get a relevant name for a
    > > > foreign key.
    > > >
    > > > My database structure is as follows:
    > > >
    > > > Table:ConfigOptions
    > > > ID
    > > > Name
    > > > Location
    > > >
    > > > Table:Dependencies
    > > > ID
    > > > ConfigOptionID
    > > > DependencyID
    > > >
    > > > Table:Location
    > > > ID
    > > > LocationDescription
    > > >
    > > > What I need to do is build a query that returns ConfigOption Name,
    > > > ConfigOption Location, Dependency Name, Dependency Location
    > > >
    > > > Using the following query, I can get the real name for ConfigOption Name,
    > > > ConfigOption Location, and Dependency Name, but only the FK value for
    > > > Dependency Location.
    > > >
    > > > SELECT StaticConfigOptions.parameterName AS Parameter,
    > > > StaticConfigOptions.parameterLocation,
    > > > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > > > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > > > DependencyLocationID
    > > > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > > > StaticConfigOptions.parameterID=Dependencies.parameterID;
    > > >
    > > > How do I get the real name (located in the parameterLocation table under
    > > > LocationDescription) to appear?
    > > >
    > > > Whatever I try to do seems to crash Access.
    > > >
    > > > Thank you!!
    > > >
     
  7. Lisa

    Lisa
    Expand Collapse
    Guest

    It's still not returning the dependency location properly. :-( What it does
    is returns the parameterID and parameterName from StaticConfig options
    regardless of whether it has a dependency. The dependencyLocation column
    shows up, but it isn't displaying the proper table locations (but they're
    names, not numbers, so we're closer!), nor a column that shows the actual
    dependency parameter names. I added the dependency column to your sql below,
    but the location of those dependencies are not correct.

    Thank you for your continued help on this!!

    SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    ParameterLocation.parameterLocationDescription, (select
    StaticConfigOptions.parameterName from StaticConfigOptions where
    StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    StaticConfigOptions.parameterLOCATION=ParameterLocation.parameterLocationID)
    LEFT JOIN Dependencies ON
    StaticConfigOptions.parameterID=Dependencies.parameterID) LEFT JOIN
    ParameterLocation AS ParameterLocation_1 ON
    Dependencies.DependencyID=ParameterLocation_1.parameterLocationID;




    "KARL DEWEY" wrote:

    > Try this --
    > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > ParameterLocation.parameterLocationDescription,
    > ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    > StaticConfigOptions.parameterLOCATION =
    > ParameterLocation.parameterLocationID) LEFT JOIN Dependencies ON
    > StaticConfigOptions.parameterID = Dependencies.parameterID) LEFT JOIN
    > ParameterLocation AS ParameterLocation_1 ON Dependencies.DependencyID =
    > ParameterLocation_1.parameterLocationID;
    >
    >
    > "Lisa" wrote:
    >
    > > Hi Karl,
    > >
    > > It didn't quite work for what is needed. Let me try and explain it a little
    > > more (I'll the true table names -- I thought real table names would be
    > > cumbersome, but I guess the opposite was true in this case, sorry)
    > >
    > > Table: StaticConfigOptions
    > > parameterID
    > > parameterName
    > > parameterLocation
    > > ...
    > >
    > > Table: ParameterLocation
    > > parameterLocationID
    > > parameterLocationDescription
    > > ...
    > >
    > > Table: Dependencies
    > > dependencyID
    > > parameterID
    > > dependency
    > >
    > > (the dependency table is a table with a primary key, dependencyID, and two
    > > foreign keys, both from the parameterID field from the StaticConfigOptions
    > > table)
    > >
    > > The Dependencies table is linked to the StaticConfigOptions table and the
    > > StaticConfigOptions table is also linked to the ParameterLocation table.
    > >
    > > So far I can get a query that tells me the Parameter Name, the Location, and
    > > the Dependency. What I'm missing is the Dependency Location. I can get the
    > > numerical value, but not that elusive parameterLocationDescription.
    > >
    > > Thank you very much for all your help!
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > I hard a hard time on this one as your table and field names do not match
    > > > those in the SQL. Also your SQL just would not fly as posted but I think I
    > > > figured out what you wanted. Try the SQL below and add or remove display
    > > > fields as you wish.
    > > >
    > > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > > StaticConfigOptions.parameterLOCATION, Location.LocationDescription,
    > > > Location_1.LocationDescription AS [Dependent Location]
    > > > FROM (StaticConfigOptions LEFT JOIN Location ON
    > > > StaticConfigOptions.parameterLOCATION = Location.ID) LEFT JOIN (Dependencies
    > > > LEFT JOIN Location AS Location_1 ON Dependencies.DependencyID =
    > > > Location_1.ID) ON StaticConfigOptions.parameterID = Dependencies.parameterID;
    > > >
    > > >
    > > > "Lisa" wrote:
    > > >
    > > > > Hi,
    > > > >
    > > > > I have a question regarding building a query to get a relevant name for a
    > > > > foreign key.
    > > > >
    > > > > My database structure is as follows:
    > > > >
    > > > > Table:ConfigOptions
    > > > > ID
    > > > > Name
    > > > > Location
    > > > >
    > > > > Table:Dependencies
    > > > > ID
    > > > > ConfigOptionID
    > > > > DependencyID
    > > > >
    > > > > Table:Location
    > > > > ID
    > > > > LocationDescription
    > > > >
    > > > > What I need to do is build a query that returns ConfigOption Name,
    > > > > ConfigOption Location, Dependency Name, Dependency Location
    > > > >
    > > > > Using the following query, I can get the real name for ConfigOption Name,
    > > > > ConfigOption Location, and Dependency Name, but only the FK value for
    > > > > Dependency Location.
    > > > >
    > > > > SELECT StaticConfigOptions.parameterName AS Parameter,
    > > > > StaticConfigOptions.parameterLocation,
    > > > > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > > > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > > > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > > > > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > > > > DependencyLocationID
    > > > > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > > > > StaticConfigOptions.parameterID=Dependencies.parameterID;
    > > > >
    > > > > How do I get the real name (located in the parameterLocation table under
    > > > > LocationDescription) to appear?
    > > > >
    > > > > Whatever I try to do seems to crash Access.
    > > > >
    > > > > Thank you!!
    > > > >
     
  8. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    I can not follow the changes you made in the SQL.

    The way I see your Dependencies table is like a junction table and would
    only need two field. Maybe you are showing who is dependent on what and
    including a name of the dependancy. Like Mr. Brown - Father-Child - Billy.

    Maybe post some data from each table.

    "Lisa" wrote:

    > It's still not returning the dependency location properly. :-( What it does
    > is returns the parameterID and parameterName from StaticConfig options
    > regardless of whether it has a dependency. The dependencyLocation column
    > shows up, but it isn't displaying the proper table locations (but they're
    > names, not numbers, so we're closer!), nor a column that shows the actual
    > dependency parameter names. I added the dependency column to your sql below,
    > but the location of those dependencies are not correct.
    >
    > Thank you for your continued help on this!!
    >
    > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > ParameterLocation.parameterLocationDescription, (select
    > StaticConfigOptions.parameterName from StaticConfigOptions where
    > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    > StaticConfigOptions.parameterLOCATION=ParameterLocation.parameterLocationID)
    > LEFT JOIN Dependencies ON
    > StaticConfigOptions.parameterID=Dependencies.parameterID) LEFT JOIN
    > ParameterLocation AS ParameterLocation_1 ON
    > Dependencies.DependencyID=ParameterLocation_1.parameterLocationID;
    >
    >
    >
    >
    > "KARL DEWEY" wrote:
    >
    > > Try this --
    > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > ParameterLocation.parameterLocationDescription,
    > > ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    > > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    > > StaticConfigOptions.parameterLOCATION =
    > > ParameterLocation.parameterLocationID) LEFT JOIN Dependencies ON
    > > StaticConfigOptions.parameterID = Dependencies.parameterID) LEFT JOIN
    > > ParameterLocation AS ParameterLocation_1 ON Dependencies.DependencyID =
    > > ParameterLocation_1.parameterLocationID;
    > >
    > >
    > > "Lisa" wrote:
    > >
    > > > Hi Karl,
    > > >
    > > > It didn't quite work for what is needed. Let me try and explain it a little
    > > > more (I'll the true table names -- I thought real table names would be
    > > > cumbersome, but I guess the opposite was true in this case, sorry)
    > > >
    > > > Table: StaticConfigOptions
    > > > parameterID
    > > > parameterName
    > > > parameterLocation
    > > > ...
    > > >
    > > > Table: ParameterLocation
    > > > parameterLocationID
    > > > parameterLocationDescription
    > > > ...
    > > >
    > > > Table: Dependencies
    > > > dependencyID
    > > > parameterID
    > > > dependency
    > > >
    > > > (the dependency table is a table with a primary key, dependencyID, and two
    > > > foreign keys, both from the parameterID field from the StaticConfigOptions
    > > > table)
    > > >
    > > > The Dependencies table is linked to the StaticConfigOptions table and the
    > > > StaticConfigOptions table is also linked to the ParameterLocation table.
    > > >
    > > > So far I can get a query that tells me the Parameter Name, the Location, and
    > > > the Dependency. What I'm missing is the Dependency Location. I can get the
    > > > numerical value, but not that elusive parameterLocationDescription.
    > > >
    > > > Thank you very much for all your help!
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > > I hard a hard time on this one as your table and field names do not match
    > > > > those in the SQL. Also your SQL just would not fly as posted but I think I
    > > > > figured out what you wanted. Try the SQL below and add or remove display
    > > > > fields as you wish.
    > > > >
    > > > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > > > StaticConfigOptions.parameterLOCATION, Location.LocationDescription,
    > > > > Location_1.LocationDescription AS [Dependent Location]
    > > > > FROM (StaticConfigOptions LEFT JOIN Location ON
    > > > > StaticConfigOptions.parameterLOCATION = Location.ID) LEFT JOIN (Dependencies
    > > > > LEFT JOIN Location AS Location_1 ON Dependencies.DependencyID =
    > > > > Location_1.ID) ON StaticConfigOptions.parameterID = Dependencies.parameterID;
    > > > >
    > > > >
    > > > > "Lisa" wrote:
    > > > >
    > > > > > Hi,
    > > > > >
    > > > > > I have a question regarding building a query to get a relevant name for a
    > > > > > foreign key.
    > > > > >
    > > > > > My database structure is as follows:
    > > > > >
    > > > > > Table:ConfigOptions
    > > > > > ID
    > > > > > Name
    > > > > > Location
    > > > > >
    > > > > > Table:Dependencies
    > > > > > ID
    > > > > > ConfigOptionID
    > > > > > DependencyID
    > > > > >
    > > > > > Table:Location
    > > > > > ID
    > > > > > LocationDescription
    > > > > >
    > > > > > What I need to do is build a query that returns ConfigOption Name,
    > > > > > ConfigOption Location, Dependency Name, Dependency Location
    > > > > >
    > > > > > Using the following query, I can get the real name for ConfigOption Name,
    > > > > > ConfigOption Location, and Dependency Name, but only the FK value for
    > > > > > Dependency Location.
    > > > > >
    > > > > > SELECT StaticConfigOptions.parameterName AS Parameter,
    > > > > > StaticConfigOptions.parameterLocation,
    > > > > > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > > > > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > > > > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > > > > > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > > > > > DependencyLocationID
    > > > > > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > > > > > StaticConfigOptions.parameterID=Dependencies.parameterID;
    > > > > >
    > > > > > How do I get the real name (located in the parameterLocation table under
    > > > > > LocationDescription) to appear?
    > > > > >
    > > > > > Whatever I try to do seems to crash Access.
    > > > > >
    > > > > > Thank you!!
    > > > > >
     
  9. Lisa

    Lisa
    Expand Collapse
    Guest

    Okay, great idea. Here's some sample data:

    StaticConfigOption
    parameterID parameterName parameterLocation
    1 Feet 3
    2 Socks 2
    3 Shoes 1
    4 Shoelaces 1

    Dependencies
    dependencyID parameterID Dependency
    1 2 1
    2 3 1
    3 3 4
    4 4 3

    This is saying that socks are dependent on feet, shoes are dependent on feet
    and shoelaces, and shoelaces are dependent on shoes.

    ParameterLocation
    parameterLocationID parameterLocationDescription
    1 Closet
    2 Laundry Basket
    3 Legs

    So I want a query that would return the following:

    ParameterName ParameterLocation Dependency DependencyLocation
    Socks Laundry Basket Feet Legs
    Shoes Closet Feet Legs
    Shoes Closet Shoelaces Closet
    Shoelaces Closet Shoes Closet

    Phew! I hope that makes sense. I can't use real data because it's for work,
    but that data covers the conditions that I have in my data. Thank you!!

    "KARL DEWEY" wrote:

    > I can not follow the changes you made in the SQL.
    >
    > The way I see your Dependencies table is like a junction table and would
    > only need two field. Maybe you are showing who is dependent on what and
    > including a name of the dependancy. Like Mr. Brown - Father-Child - Billy.
    >
    > Maybe post some data from each table.
    >
    > "Lisa" wrote:
    >
    > > It's still not returning the dependency location properly. :-( What it does
    > > is returns the parameterID and parameterName from StaticConfig options
    > > regardless of whether it has a dependency. The dependencyLocation column
    > > shows up, but it isn't displaying the proper table locations (but they're
    > > names, not numbers, so we're closer!), nor a column that shows the actual
    > > dependency parameter names. I added the dependency column to your sql below,
    > > but the location of those dependencies are not correct.
    > >
    > > Thank you for your continued help on this!!
    > >
    > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > ParameterLocation.parameterLocationDescription, (select
    > > StaticConfigOptions.parameterName from StaticConfigOptions where
    > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    > > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    > > StaticConfigOptions.parameterLOCATION=ParameterLocation.parameterLocationID)
    > > LEFT JOIN Dependencies ON
    > > StaticConfigOptions.parameterID=Dependencies.parameterID) LEFT JOIN
    > > ParameterLocation AS ParameterLocation_1 ON
    > > Dependencies.DependencyID=ParameterLocation_1.parameterLocationID;
    > >
    > >
    > >
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > Try this --
    > > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > > ParameterLocation.parameterLocationDescription,
    > > > ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    > > > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    > > > StaticConfigOptions.parameterLOCATION =
    > > > ParameterLocation.parameterLocationID) LEFT JOIN Dependencies ON
    > > > StaticConfigOptions.parameterID = Dependencies.parameterID) LEFT JOIN
    > > > ParameterLocation AS ParameterLocation_1 ON Dependencies.DependencyID =
    > > > ParameterLocation_1.parameterLocationID;
    > > >
    > > >
    > > > "Lisa" wrote:
    > > >
    > > > > Hi Karl,
    > > > >
    > > > > It didn't quite work for what is needed. Let me try and explain it a little
    > > > > more (I'll the true table names -- I thought real table names would be
    > > > > cumbersome, but I guess the opposite was true in this case, sorry)
    > > > >
    > > > > Table: StaticConfigOptions
    > > > > parameterID
    > > > > parameterName
    > > > > parameterLocation
    > > > > ...
    > > > >
    > > > > Table: ParameterLocation
    > > > > parameterLocationID
    > > > > parameterLocationDescription
    > > > > ...
    > > > >
    > > > > Table: Dependencies
    > > > > dependencyID
    > > > > parameterID
    > > > > dependency
    > > > >
    > > > > (the dependency table is a table with a primary key, dependencyID, and two
    > > > > foreign keys, both from the parameterID field from the StaticConfigOptions
    > > > > table)
    > > > >
    > > > > The Dependencies table is linked to the StaticConfigOptions table and the
    > > > > StaticConfigOptions table is also linked to the ParameterLocation table.
    > > > >
    > > > > So far I can get a query that tells me the Parameter Name, the Location, and
    > > > > the Dependency. What I'm missing is the Dependency Location. I can get the
    > > > > numerical value, but not that elusive parameterLocationDescription.
    > > > >
    > > > > Thank you very much for all your help!
    > > > >
    > > > > "KARL DEWEY" wrote:
    > > > >
    > > > > > I hard a hard time on this one as your table and field names do not match
    > > > > > those in the SQL. Also your SQL just would not fly as posted but I think I
    > > > > > figured out what you wanted. Try the SQL below and add or remove display
    > > > > > fields as you wish.
    > > > > >
    > > > > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > > > > StaticConfigOptions.parameterLOCATION, Location.LocationDescription,
    > > > > > Location_1.LocationDescription AS [Dependent Location]
    > > > > > FROM (StaticConfigOptions LEFT JOIN Location ON
    > > > > > StaticConfigOptions.parameterLOCATION = Location.ID) LEFT JOIN (Dependencies
    > > > > > LEFT JOIN Location AS Location_1 ON Dependencies.DependencyID =
    > > > > > Location_1.ID) ON StaticConfigOptions.parameterID = Dependencies.parameterID;
    > > > > >
    > > > > >
    > > > > > "Lisa" wrote:
    > > > > >
    > > > > > > Hi,
    > > > > > >
    > > > > > > I have a question regarding building a query to get a relevant name for a
    > > > > > > foreign key.
    > > > > > >
    > > > > > > My database structure is as follows:
    > > > > > >
    > > > > > > Table:ConfigOptions
    > > > > > > ID
    > > > > > > Name
    > > > > > > Location
    > > > > > >
    > > > > > > Table:Dependencies
    > > > > > > ID
    > > > > > > ConfigOptionID
    > > > > > > DependencyID
    > > > > > >
    > > > > > > Table:Location
    > > > > > > ID
    > > > > > > LocationDescription
    > > > > > >
    > > > > > > What I need to do is build a query that returns ConfigOption Name,
    > > > > > > ConfigOption Location, Dependency Name, Dependency Location
    > > > > > >
    > > > > > > Using the following query, I can get the real name for ConfigOption Name,
    > > > > > > ConfigOption Location, and Dependency Name, but only the FK value for
    > > > > > > Dependency Location.
    > > > > > >
    > > > > > > SELECT StaticConfigOptions.parameterName AS Parameter,
    > > > > > > StaticConfigOptions.parameterLocation,
    > > > > > > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > > > > > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > > > > > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > > > > > > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > > > > > > DependencyLocationID
    > > > > > > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > > > > > > StaticConfigOptions.parameterID=Dependencies.parameterID;
    > > > > > >
    > > > > > > How do I get the real name (located in the parameterLocation table under
    > > > > > > LocationDescription) to appear?
    > > > > > >
    > > > > > > Whatever I try to do seems to crash Access.
    > > > > > >
    > > > > > > Thank you!!
    > > > > > >
     
  10. Lisa

    Lisa
    Expand Collapse
    Guest

    I got it!

    SELECT [StaticConfigOptions].[parameterName] AS Parameter,
    [StaticConfigOptions].[parameterLocation], (select
    StaticConfigOptions.parameterName from StaticConfigOptions where
    StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    (SELECT ParameterLocation.parameterLocationDescription FROM ParameterLocation
    where ParameterLocation.parameterLocationID =
    (select [StaticConfigOptions].[parameterLocation] from
    [StaticConfigOptions] where dependencies.dependency =
    [StaticConfigOptions].[parameterID]) ) AS Dependencylocation
    FROM StaticConfigOptions INNER JOIN Dependencies ON
    [StaticConfigOptions].[parameterID]=[Dependencies].[parameterID];

    Thanks for your help on this Karl!!

    "Lisa" wrote:

    > Okay, great idea. Here's some sample data:
    >
    > StaticConfigOption
    > parameterID parameterName parameterLocation
    > 1 Feet 3
    > 2 Socks 2
    > 3 Shoes 1
    > 4 Shoelaces 1
    >
    > Dependencies
    > dependencyID parameterID Dependency
    > 1 2 1
    > 2 3 1
    > 3 3 4
    > 4 4 3
    >
    > This is saying that socks are dependent on feet, shoes are dependent on feet
    > and shoelaces, and shoelaces are dependent on shoes.
    >
    > ParameterLocation
    > parameterLocationID parameterLocationDescription
    > 1 Closet
    > 2 Laundry Basket
    > 3 Legs
    >
    > So I want a query that would return the following:
    >
    > ParameterName ParameterLocation Dependency DependencyLocation
    > Socks Laundry Basket Feet Legs
    > Shoes Closet Feet Legs
    > Shoes Closet Shoelaces Closet
    > Shoelaces Closet Shoes Closet
    >
    > Phew! I hope that makes sense. I can't use real data because it's for work,
    > but that data covers the conditions that I have in my data. Thank you!!
    >
    > "KARL DEWEY" wrote:
    >
    > > I can not follow the changes you made in the SQL.
    > >
    > > The way I see your Dependencies table is like a junction table and would
    > > only need two field. Maybe you are showing who is dependent on what and
    > > including a name of the dependancy. Like Mr. Brown - Father-Child - Billy.
    > >
    > > Maybe post some data from each table.
    > >
    > > "Lisa" wrote:
    > >
    > > > It's still not returning the dependency location properly. :-( What it does
    > > > is returns the parameterID and parameterName from StaticConfig options
    > > > regardless of whether it has a dependency. The dependencyLocation column
    > > > shows up, but it isn't displaying the proper table locations (but they're
    > > > names, not numbers, so we're closer!), nor a column that shows the actual
    > > > dependency parameter names. I added the dependency column to your sql below,
    > > > but the location of those dependencies are not correct.
    > > >
    > > > Thank you for your continued help on this!!
    > > >
    > > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > > ParameterLocation.parameterLocationDescription, (select
    > > > StaticConfigOptions.parameterName from StaticConfigOptions where
    > > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > > ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    > > > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    > > > StaticConfigOptions.parameterLOCATION=ParameterLocation.parameterLocationID)
    > > > LEFT JOIN Dependencies ON
    > > > StaticConfigOptions.parameterID=Dependencies.parameterID) LEFT JOIN
    > > > ParameterLocation AS ParameterLocation_1 ON
    > > > Dependencies.DependencyID=ParameterLocation_1.parameterLocationID;
    > > >
    > > >
    > > >
    > > >
    > > > "KARL DEWEY" wrote:
    > > >
    > > > > Try this --
    > > > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > > > ParameterLocation.parameterLocationDescription,
    > > > > ParameterLocation_1.parameterLocationDescription AS [Dependent Location]
    > > > > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
    > > > > StaticConfigOptions.parameterLOCATION =
    > > > > ParameterLocation.parameterLocationID) LEFT JOIN Dependencies ON
    > > > > StaticConfigOptions.parameterID = Dependencies.parameterID) LEFT JOIN
    > > > > ParameterLocation AS ParameterLocation_1 ON Dependencies.DependencyID =
    > > > > ParameterLocation_1.parameterLocationID;
    > > > >
    > > > >
    > > > > "Lisa" wrote:
    > > > >
    > > > > > Hi Karl,
    > > > > >
    > > > > > It didn't quite work for what is needed. Let me try and explain it a little
    > > > > > more (I'll the true table names -- I thought real table names would be
    > > > > > cumbersome, but I guess the opposite was true in this case, sorry)
    > > > > >
    > > > > > Table: StaticConfigOptions
    > > > > > parameterID
    > > > > > parameterName
    > > > > > parameterLocation
    > > > > > ...
    > > > > >
    > > > > > Table: ParameterLocation
    > > > > > parameterLocationID
    > > > > > parameterLocationDescription
    > > > > > ...
    > > > > >
    > > > > > Table: Dependencies
    > > > > > dependencyID
    > > > > > parameterID
    > > > > > dependency
    > > > > >
    > > > > > (the dependency table is a table with a primary key, dependencyID, and two
    > > > > > foreign keys, both from the parameterID field from the StaticConfigOptions
    > > > > > table)
    > > > > >
    > > > > > The Dependencies table is linked to the StaticConfigOptions table and the
    > > > > > StaticConfigOptions table is also linked to the ParameterLocation table.
    > > > > >
    > > > > > So far I can get a query that tells me the Parameter Name, the Location, and
    > > > > > the Dependency. What I'm missing is the Dependency Location. I can get the
    > > > > > numerical value, but not that elusive parameterLocationDescription.
    > > > > >
    > > > > > Thank you very much for all your help!
    > > > > >
    > > > > > "KARL DEWEY" wrote:
    > > > > >
    > > > > > > I hard a hard time on this one as your table and field names do not match
    > > > > > > those in the SQL. Also your SQL just would not fly as posted but I think I
    > > > > > > figured out what you wanted. Try the SQL below and add or remove display
    > > > > > > fields as you wish.
    > > > > > >
    > > > > > > SELECT StaticConfigOptions.parameterID, StaticConfigOptions.parameterName,
    > > > > > > StaticConfigOptions.parameterLOCATION, Location.LocationDescription,
    > > > > > > Location_1.LocationDescription AS [Dependent Location]
    > > > > > > FROM (StaticConfigOptions LEFT JOIN Location ON
    > > > > > > StaticConfigOptions.parameterLOCATION = Location.ID) LEFT JOIN (Dependencies
    > > > > > > LEFT JOIN Location AS Location_1 ON Dependencies.DependencyID =
    > > > > > > Location_1.ID) ON StaticConfigOptions.parameterID = Dependencies.parameterID;
    > > > > > >
    > > > > > >
    > > > > > > "Lisa" wrote:
    > > > > > >
    > > > > > > > Hi,
    > > > > > > >
    > > > > > > > I have a question regarding building a query to get a relevant name for a
    > > > > > > > foreign key.
    > > > > > > >
    > > > > > > > My database structure is as follows:
    > > > > > > >
    > > > > > > > Table:ConfigOptions
    > > > > > > > ID
    > > > > > > > Name
    > > > > > > > Location
    > > > > > > >
    > > > > > > > Table:Dependencies
    > > > > > > > ID
    > > > > > > > ConfigOptionID
    > > > > > > > DependencyID
    > > > > > > >
    > > > > > > > Table:Location
    > > > > > > > ID
    > > > > > > > LocationDescription
    > > > > > > >
    > > > > > > > What I need to do is build a query that returns ConfigOption Name,
    > > > > > > > ConfigOption Location, Dependency Name, Dependency Location
    > > > > > > >
    > > > > > > > Using the following query, I can get the real name for ConfigOption Name,
    > > > > > > > ConfigOption Location, and Dependency Name, but only the FK value for
    > > > > > > > Dependency Location.
    > > > > > > >
    > > > > > > > SELECT StaticConfigOptions.parameterName AS Parameter,
    > > > > > > > StaticConfigOptions.parameterLocation,
    > > > > > > > (select StaticConfigOptions.parameterName from StaticConfigOptions where
    > > > > > > > StaticConfigOptions.parameterID = Dependencies.dependency) AS Dependency,
    > > > > > > > (select StaticConfigOptions.parameterLocation from StaticConfigOptions
    > > > > > > > WHERE Dependencies.parameterID = StaticConfigOptions.parameterID) AS
    > > > > > > > DependencyLocationID
    > > > > > > > FROM StaticConfigOptions INNER JOIN Dependencies ON
    > > > > > > > StaticConfigOptions.parameterID=Dependencies.parameterID;
    > > > > > > >
    > > > > > > > How do I get the real name (located in the parameterLocation table under
    > > > > > > > LocationDescription) to appear?
    > > > > > > >
    > > > > > > > Whatever I try to do seems to crash Access.
    > > > > > > >
    > > > > > > > Thank you!!
    > > > > > > >
     

Share This Page