Call query with parameters provided

[hufflehuffle]

Hi, thanks for your help in advance.



Given is one HUGE table of items (I get the table by the customer and have no influence on it's structure)

ITEMTYPE | MATERIAL | DIAMETER | PRICE

Now I have to make a catlogue with one page for each item type like:

TYPE A:
DIAMETER | MATERIAL1-PRICE | MATERIAL2-PRICE | [...] | MATERIAL 7-PRICE

Currently i do:
QUERY1: SELECT diameter, price FROM Table WHERE itemtype = "A" and material = "1"
QUERY2: SELECT diameter, price FROM Table WHERE itemtype = "A" and material = "2"
[...]
QUERY7: SELECT diameter, price FROM Table WHERE itemtype = "A" and material = "7"

and then a connecting query like:

SELECT query1.diameter, query1.price, query2.price, [...] query7.price
FROM query1
INNER JOIN query2 ON query1.diameter = query2.diameter
INNER JOIN query3 ON query1.diameter = query3.diameter
[...]
INNER JOIN query7 ON query1.diameter = query7.diameter


This works pretty fine so far, but for each itemtype I need to build (and maintain) 8 SQL-queries (or 1 giant SQL monster). I have like 100 item types :-)

Is there a way to have a parametrized query like
SELECT diameter, price WHERE itemtype = $ITEMTYPE and material = $MATERIAL
and to call this from the connecting query so I have only 100 different queries but not 800?

Additional problem: The queries are called through ODBC from outside access, so I cannot do VB magic :-)

Thanks and best regards
Martin

[orange]

???? The general format of a SELECT query is

SELECT column_name,column_name
    FROM table_name
WHERE condition;

Your examples do not include a Tablename???

An efficient 1 table database is extremely rare. I think you have a database structure issue.
Can you tell us in 3-4 lines of plain English ---WHAT is the database about? And also how the ODBC is involved?
The more detail the better, but focus on WHAT you are trying to do.

You may wish to preprocess your incoming data to separate out the ITEMTYPES....

[hufflehuffle]

Your examples do not include a Tablename???

Hi! Thanks for the quick reply first of all. I just forgot the table name (as there is only one :-)) Fixed that in the post.

An efficient 1 table database is extremely rare. I think you have a database structure issue.

I am aware of that. But as I wrote: "I get the table by the customer and have no influence on it's structure"

Can you tell us in 3-4 lines of plain English ---WHAT is the database about?

Sure. It's a table of plastic articles, each article has an article type, a material, a diameter and a price. The same article exists in several materials. For each article type I shall produce a table sorted by diameter featuring colums where the material prices are listed.

Example Table:
Tube | PVC | 50 mm | 6,90 $
Tube | PVC | 60 mm | 7,90 $
Tube | PE | 50 mm | 6,95 $
Tube | PE | 60 mm | 7,95 $
Bend | PVC | 50 mm | 18,99$
Bend | PE | 50 mm | 21,99 $
Bend | PVC | 60 mm | 28,99$
Bend | PE | 60 mm | 31,99 $

Desired Result:

Tube:
50 mm | 6,90 $ (PVC) | 6,95 $ (PE)
60 mm | 7,90 $ (PVC) | 7,95 $ (PE)

Bend:
50 mm | 18,99 $ (PVC) | 21,99 $ (PE)
60 mm | 28,99 $ (PVC) | 31,99 $ (PE)

... and so on.

And also how the ODBC is involved?

The table will be included in a print catalogue produced in a layout software called "Adobe InDesign". I can only import the result of a query (or a table) through ODBC but have no way of sorting or filtering the data.

The more detail the better, but focus on WHAT you are trying to do.

Sure - I just tried to remove irrelevant complexity :-)

You may wish to preprocess your incoming data to separate out the ITEMTYPES....

I'd love to, but the customer will frequently update his data, so I'd like to plug in the current table into my database and have the catalog at a click of a mouse.

Thanks for your ideas :-)
Martin