Automating a series of repeated queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

irwin@care4free.net - 20 Nov 2007 16:23 GMT

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.

Reply to this Message

Michel Walsh - 20 Nov 2007 17:04 GMT

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

> This is probably a simple question to answer, but I'm only a dabbler
> in Access so I'm not sure what to do.
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message

irwin@care4free.net - 23 Nov 2007 16:58 GMT

> 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

Reply to this Message

Michel Walsh - 23 Nov 2007 18:54 GMT

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
[quoted text clipped - 8 lines]
>
> irwin

Reply to this Message

irwin@care4free.net - 23 Nov 2007 22:22 GMT

On Nov 23, 6:54 pm, "Michel Walsh"
<vanderghast@VirusAreFunnierThanSpam> wrote:

> 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
[quoted text clipped - 17 lines]
>
> 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

Reply to this Message

Michel Walsh - 26 Nov 2007 14:13 GMT

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

> On Nov 23, 6:54 pm, "Michel Walsh"
> <vanderghast@VirusAreFunnierThanSpam> wrote:
[quoted text clipped - 46 lines]
>
> irwin

Reply to this Message