Building a query to get relevant names for FKs

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

Tableependencies
ID
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9538-building-query-get-relevant-names-fks.html
ConfigOptionID
DependencyID

Table:Location
ID
LocationDescription

What I need to do is build a query that returns ConfigOption Name,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
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.param eterID;

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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
>
> Tableependencies
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> 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.param eterID;
>
> How do I get the real name (located in the parameterLocation table under
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> LocationDescription) to appear?
>
> Whatever I try to do seems to crash Access.
>
> Thank you!!
>

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;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538


"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
>
> Tableependencies
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
>
> 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.param eterID;
>
> 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!!
>

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
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
> >
> > Tableependencies
> > 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.param eterID;
> >
> > How do I get the real name (located in the parameterLocation table under
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > LocationDescription) to appear?
> >
> > Whatever I try to do seems to crash Access.
> >
> > Thank you!!
> >

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
> > >
> > > Tableependencies
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > > ID
> > > ConfigOptionID
> > > DependencyID
> > >
> > > Table:Location
> > > ID
> > > LocationDescription
> > >
> > > What I need to do is build a query that returns ConfigOption Name,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > > 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.param eterID;
> > >
> > > 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!!
> > >

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=ParameterLoc ation.parameterLocationID)
LEFT JOIN Dependencies ON
StaticConfigOptions.parameterID=Dependencies.param eterID) LEFT JOIN
ParameterLocation AS ParameterLocation_1 ON
Dependencies.DependencyID=ParameterLocation_1.para meterLocationID;




"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 =
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> 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
> > > >
> > > > Tableependencies
> > > > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > > > 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.param eterID;
> > > >
> > > > 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!!
> > > >

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:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> 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=ParameterLoc ation.parameterLocationID)
> LEFT JOIN Dependencies ON
> StaticConfigOptions.parameterID=Dependencies.param eterID) LEFT JOIN
> ParameterLocation AS ParameterLocation_1 ON
> Dependencies.DependencyID=ParameterLocation_1.para meterLocationID;
>
>
>
>
> "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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > > 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
> > > > >
> > > > > Tableependencies
> > > > > 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.param eterID;
> > > > >
> > > > > 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!!
> > > > >

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]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > FROM ((StaticConfigOptions LEFT JOIN ParameterLocation ON
> > StaticConfigOptions.parameterLOCATION=ParameterLoc ation.parameterLocationID)
> > LEFT JOIN Dependencies ON
> > StaticConfigOptions.parameterID=Dependencies.param eterID) LEFT JOIN
> > ParameterLocation AS ParameterLocation_1 ON
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > Dependencies.DependencyID=ParameterLocation_1.para meterLocationID;
> >
> >
> >
> >
> > "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
> > > > > >
> > > > > > Tableependencies
> > > > > > 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.param eterID;
> > > > > >
> > > > > > 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!!
> > > > > >

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
>
> 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=ParameterLoc ation.parameterLocationID)
> > > LEFT JOIN Dependencies ON
> > > StaticConfigOptions.parameterID=Dependencies.param eterID) LEFT JOIN
> > > ParameterLocation AS ParameterLocation_1 ON
> > > Dependencies.DependencyID=ParameterLocation_1.para meterLocationID;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9538
> > >
> > >
> > >
> > >
> > > "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
> > > > > > >
> > > > > > > Tableependencies
> > > > > > > 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.param eterID;
> > > > > > >
> > > > > > > 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!!
> > > > > > >