Automating a series of repeated queries

[irwin]

This is probably a simple question to answer, but I'm only a dabbler
in Access so I'm not sure what to do.

I have a database of around 200 items. In one field are listed one or
more similar attributes (can be up to 50 or more) for each item. If I
want to know which items have a particular attribute I have a simple
manual query inputting the attribute that does that. I would like to
get a list/table of all attributes with the associated items. I do not
want to do repeated manual queries. I assume there is some way of
automating the process to input a list of attributes, do the query on
each in turn and give a combined output.

Ideas, please?

Thanks.

 

[Michel Walsh]

SELECT DISTINCT item
FROM itemsAttributes
WHERE attribute = [Which attribute you are looking for: ]


will return all items having the said attribute. I assume a table
itemsAttributes with two fields, item and attribute.


SELECT DISTINCT attribute
FROM itemsAttributes
WHERE item IN (
SELECT DISTINCT item
FROM itemsAttributes
WHERE attribute = [Which attribute you are looking
for: ]
)


will return all attributes associate with at least ONE of these items.



Vanderghast, Access MVP

 

[irwin]

SELECT DISTINCT item
FROM itemsAttributes
WHERE attribute = [Which attribute you are looking for: ]

Thanks for this, but not sure how far it helps.

How is [Which attribute you are looking for: ] input? If I try this I
get an Enter Parameter Value box.

How can all the attributes be input sequentially and automatically?

irwin

 

[Michel Walsh]

A query cannot 'loop' through time, all by itself. Indeed, as it is, this
query asks for one parameter. If you want to run the query for another
parameter, you run the query a second time.

If the value of the parameter is already available in a control of a form
and if the query is the row source (property) of a list box, as example,
then you can use

FORMS!formNameHere!ControlNameHere

instead of [Which attribute you are looking for: ]


To fill the said list box with records but for another value for the
parameter, you will have to change the value in the control the query used
as reference AND to re-assign the row source of the listbox, but that
implies some use of VBA.

Otherwise, if you are with another scenario, sorry, but 'automatically and
sequentially' is too fuzzy for me to understand what are your intention.



Vanderghast, Access MVP

SELECT DISTINCT item
FROM itemsAttributes
WHERE attribute = [Which attribute you are looking for: ]

Thanks for this, but not sure how far it helps.

How is [Which attribute you are looking for: ] input? If I try this I
get an Enter Parameter Value box.

How can all the attributes be input sequentially and automatically?

irwin

 

[irwin]

A query cannot 'loop' through time, all by itself. Indeed, as it is, this
query asks for one parameter. If you want to run the query for another
parameter, you run the query a second time.

If the value of the parameter is already available in a control of a form
and if the query is the row source (property) of a list box, as example,
then you can use

FORMS!formNameHere!ControlNameHere

instead of [Which attribute you are looking for: ]

To fill the said list box with records but for another value for the
parameter, you will have to change the value in the control the query used
as reference AND to re-assign the row source of the listbox, but that
implies some use of VBA.

Otherwise, if you are with another scenario, sorry, but 'automatically and
sequentially' is too fuzzy for me to understand what are your intention.

Vanderghast, Access MVP

OK

I have a table with a field of items X1 to X50
Associated with each item in another field are one or more attributes
from a list A1 to A100, eg

| X1 | A1, A6, A17, A67 |
| X2 | A56, A87 |
| X3 | A6, A45, A89 | etc


I want to know which items are associated with each attribute A1 to
A100. I can do a simple query, but don't want to have to input each
attribute individually. Can I do a query to give me a list something
like this:

A1: X11, X26, X34, X41, X46
A2: X23, X43, X44
A3: X1, X12, X22, X41
etc

Thanks.

irwin

 

[Michel Walsh]

SELECT a.item AS x, b.item AS y
FROM myTable As a INNER JOIN myTable as b
ON a.attribute = b.attribute AND a.item <> b.item
GROUP BY a.item, b.item



Should produce a vertical list (rather than a tableau). To produce a
tableau, save the query, say under the name of qu1, and either use a
crosstab:


TRANSFORM iif( MAX(y) IS NULL , " ", "x" )
SELECT x
FROM qu1
GROUP BY x
PIVOT y


either concatenate to make a list (note that in both cases, there is a
limitation: the crosstab is limited to 255 fields, the concatenation is
limited to 255 characters).


To produce a concatenated list, someone can use a query assisted by a temp
table which will hold the result. Make the temp table:

SELECT x, iif(false, "", null) AS concat FROM qu1 INTO tempTable


(creates a table with two columns, the first one having all the possible
'groups', the second column, a varChar(255) column filled with nulls)

then


UPDATE tempTable INNER JOIN qu1 ON tempTable.x = qu1.x
SET concat = (concat + ", ") & CStr(qu1.y)


should produce the desired result in table tempTable.


Hoping it may help,
Vanderghast, Access MVP

A query cannot 'loop' through time, all by itself. Indeed, as it is,
this
query asks for one parameter. If you want to run the query for another
parameter, you run the query a second time.

If the value of the parameter is already available in a control of a form
and if the query is the row source (property) of a list box, as example,
then you can use

FORMS!formNameHere!ControlNameHere

instead of [Which attribute you are looking for: ]

To fill the said list box with records but for another value for the
parameter, you will have to change the value in the control the query
used
as reference AND to re-assign the row source of the listbox, but that
implies some use of VBA.

Otherwise, if you are with another scenario, sorry, but 'automatically
and
sequentially' is too fuzzy for me to understand what are your intention.

Vanderghast, Access MVP

OK

I have a table with a field of items X1 to X50
Associated with each item in another field are one or more attributes
from a list A1 to A100, eg

| X1 | A1, A6, A17, A67 |
| X2 | A56, A87 |
| X3 | A6, A45, A89 | etc


I want to know which items are associated with each attribute A1 to
A100. I can do a simple query, but don't want to have to input each
attribute individually. Can I do a query to give me a list something
like this:

A1: X11, X26, X34, X41, X46
A2: X23, X43, X44
A3: X1, X12, X22, X41
etc

Thanks.

irwin