Query Table with multiple values on a single field

[Harsha]

Hi all,

I have a database with 4 tables Company, Samples, Components and
ComponentDetails.

The Samples and Components are in M-N relationship with ComponentDetails
Table being the bridge entity.

Each sample has many components in them and want to know if it is possible
to write a parameter query which will take a list of components values as
input and find the sample having all these ( not one of the components in the
list) components?

I hope I am being clear.

Thanks in advance

 

[Lord Kelvan]

..........

so to calarify you want

select sample a
where component = component a
and component b
and component c
and component d

but the number of components that coudl be in that list could be any
number of components

 

[Lord Kelvan]

you cannot do that you can do something like

InStr("," & [Enter components with comma between them] & ",","," &
[components] & ",")>"0"

thanks duane for that command

but you have to type in each component eg componenta,componentb

you cannot however have a combo box showing you the list then you
selecting the ones you want

what you have to do for that is create a form with a combo box and
then have a button to add the values you select to a list box or
something. You then need to use a loop to check the values in that
list box create a filter or query to seach for thoes records

ask on the .forms or .formscoding groups they can help you with that

regards
Kelvan

 

[louisjohnphillips]

you cannot do that you can do something like

InStr("," & [Enter components with comma between them] & ",","," &
[components] & ",")>"0"

thanks duane for that command

but you have to type in each component eg componenta,componentb

you cannot however have a combo box showing you the list then you
selecting the ones you want

what you have to do for that is create a form with a combo box and
then have a button to add the values you select to a list box or
something.  You then need to use a loop to check the values in that
list box create a filter or query to seach for thoes records

ask on the .forms or .formscoding groups they can help you with that

regards
Kelvan

Is the point here to find the Item that is made of all the available
Components?


SELECT A.Item, A.PartsUsed, B.PartsAvailable
from
(
select Item, count(*) as PartsUsed
from ComponentDetails
group by Item
) as A
INNER JOIN
(
SELECT count(*) as PartsAvailable
from Components
) as B
ON A.PartsUsed = B.PartsAvailable