Distinct Field and Record Query

[okiebob]

Good morning, I'm trying to execute a query that is based on a field within
the same record.
Example; Field A has a value selected and with the relationships that have
been established in background tables, Field B will execute a query that
limits the list in a combo box to those values associated with Field A. Both
of these are within the same record of a table.

While I've been able to have the query execute properly if Field A only has
one value, I have not been able to get it to execute properly if Field A has
more than 1 value (effectively coming back with the values for everything in
Field A instead of those exclusive to that record).

I'm hoping some of you might be able to tell me what commands need to be
included to execute properly.

Thanks in advance

 

[Stewart Berman]

Lacking the code and SQl one can only guess. My guess is that the way you have setup the query for
field B the results is based on the current value of field A.

If this is not the reason please provide some additional information -- like the queries that
populate field A and field B.

 

[okiebob]

That is correct. The query is based on the values in Field A (Weapon
System/Commodity). Below is the sql:

SELECT DISTINCT [Comm Loc Level].Location
FROM [Comm Loc Level] INNER JOIN [Equipment Types] ON [Comm Loc
Level].[Weapon System/Commodity] = [Equipment Types].[Weapon
System/Commodity];

I have tried adding the additional WHERE statement but because I can't
figure out how to direct the query to only search for values associted with
Field A (Weapon System/Commodity) in each specific row, it adds no useful
criteria.

WHERE ((([Equipment Types].[Weapon System/Commodity]) = [Comm Loc
Level].[Weapon System/Commodity]))

Thanks again for the help

 

[Stewart Berman]

Are you using Access 2007 with multivalued fields? If so you need to add .value the multivalued
field name to get individual values.

That is correct. The query is based on the values in Field A (Weapon
System/Commodity). Below is the sql:

SELECT DISTINCT [Comm Loc Level].Location
FROM [Comm Loc Level] INNER JOIN [Equipment Types] ON [Comm Loc
Level].[Weapon System/Commodity] = [Equipment Types].[Weapon
System/Commodity];

I have tried adding the additional WHERE statement but because I can't
figure out how to direct the query to only search for values associted with
Field A (Weapon System/Commodity) in each specific row, it adds no useful
criteria.

WHERE ((([Equipment Types].[Weapon System/Commodity]) = [Comm Loc
Level].[Weapon System/Commodity]))

Thanks again for the help

Lacking the code and SQl one can only guess. My guess is that the way you have setup the query for
field B the results is based on the current value of field A.

If this is not the reason please provide some additional information -- like the queries that
populate field A and field B.