Distinct Field and Record Query

  • Thread starter Thread starter okiebob
  • Start date Start date
O

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
 
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.
 
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
 
Are you using Access 2007 with multivalued fields? If so you need to add .value the multivalued
field name to get individual values.

okiebob said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top