search fields in query combinations

[Guest]

Please can you help

I have set up a table with 3 columns

1st : part no
2nd : attribute name
3rd : attribute


every part number has 2 attribute names (size & colour)
and each name has a number of attribute (size has s,m,l,xl & colour has
red,yellow,orange...)

eg.

Part No Name Attribute

l007 Colour orange
l007 Colour yellow
l007 Colour white
l007 Colour red
l007 Size large
l007 Size medium
l007 Size small



What i want to do is on another table to create records of every combination
or attribute available with the part number

eg.


l007 -yellow - small
l007 - yellow - medium
l007 - yellow - large
l007 - red - small
l007 - red - medium
l007 - red - large

............ and so on


Is this possible to be done automatically and if it is how is it done, if
you need any other information on what i am trying to do please mail me

thanks
IMMY

 

[John W. Vinson/MVP]

Please can you help

I have set up a table with 3 columns

1st : part no
2nd : attribute name
3rd : attribute

A "Cartesian Join" self-join query will do the job here. You'll need to
create a query by adding the table to the query grid TWICE (Access will
alias the second instance by appending a 1 to its name). The SQL would be
something like

SELECT mytable.[Part No], mytable.[Attribute] AS [Colour],
mytable1.[Attribute] AS [Size]
FROM mytable INNER JOIN mytable AS mytable1
ON mytable1.[Part No] = mytable.[Part No]
WHERE mytable.[Attribute Name] = "Colour"
AND mytable1.[Attribute Name] = "Size";

 

[Guest]

Im sorry im very new o this would it be possible to guide me through how to
do this

Immyz

Please can you help

I have set up a table with 3 columns

1st : part no
2nd : attribute name
3rd : attribute

A "Cartesian Join" self-join query will do the job here. You'll need to
create a query by adding the table to the query grid TWICE (Access will
alias the second instance by appending a 1 to its name). The SQL would be
something like

SELECT mytable.[Part No], mytable.[Attribute] AS [Colour],
mytable1.[Attribute] AS [Size]
FROM mytable INNER JOIN mytable AS mytable1
ON mytable1.[Part No] = mytable.[Part No]
WHERE mytable.[Attribute Name] = "Colour"
AND mytable1.[Attribute Name] = "Size";

 

[John Vinson]

Im sorry im very new o this would it be possible to guide me through how to
do this

Not easily, since I'm not certain what you've changed between your
database and your question. If your table fields are in fact named as
you posted them ([part no], [attribute name], and [attribute]), then
you can simply copy and paste the SQL string

SELECT mytable.[Part No], mytable.[Attribute] AS [Colour],
mytable1.[Attribute] AS [Size]
FROM mytable INNER JOIN mytable AS mytable1
ON mytable1.[Part No] = mytable.[Part No]
WHERE mytable.[Attribute Name] = "Colour"
AND mytable1.[Attribute Name] = "Size";

into the SQL view of a new Query; create a new query, don't select any
tables, select SQL from the View menu item, and paste this in place of
the word SELECT; that Access puts in automatically.

Edit all occurances of mytable to the actual name of your table (which
of course I do not know).

Alternatively, create a new query by adding your table to the query
design window, twice. Select the Part No field from the first
instance, and the other two fields from both instances. Drag the Part
No field from the first instance to the Part No field on the second.
Put a criterion "Colour" under the first [Attribute Name] field, and
"Size" under the second.

John W. Vinson[MVP]