Building a query to get relevant names for FKs

L

Lisa

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

Michael H

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
 
K

KARL DEWEY

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;
 
L

Lisa

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 said:
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 said:
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!!
Click to expand...
Click to expand...
 
K

KARL DEWEY

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 said:
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 said:
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 said:
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!!
Click to expand...
Click to expand...
Click to expand...
 
L

Lisa

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 said:
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 said:
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 said:
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;


:

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!!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

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

:

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;


:

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!!
Click to expand...
Click to expand...
Click to expand...
 
L

Lisa

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 said:
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 said:
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 said:
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;


:

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!

:

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;


:

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!!
Click to expand...
Click to expand...
Click to expand...
 
L

Lisa

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 said:
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 said:
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 said:
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;




:

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;


:

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!

:

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;


:

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!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using QueryDefs to Build a Dynamic Query in Access 6
Can't get Query to work in Text Box - Please help.... 0

Top