Help doing a simple task

M

Mike

Here's the situation. I'm trying to make an addition to an old (and non
ideal) DB

There are tables named for different pannels they nave spaces and sometimes
hypens in the name. ex "ABC-DEF C123 Aux" and "ABC T2345"

inside these tables there are 3 fields i'm interested in. Color, Length and
Size

all of these fields are text fields.

there is another table "Usable Panels" that lists the "active" panels/tables.

Here's my task.

I want to develop a way to take a panel from the "active" table, then search
the desired table and addup the lengths of each specific color/size combo in
that table.

i.e. there may be 3 listings for a size blue 10, 40 for a yellow 14 and 130
for a yellow 16.

any ideas on the best way to go aobut this?
 
K

Ken Sheridan

Firstly create a union query which tacks all the individual panels tables
together to return a single result table:

SELECT "ABC-DEF C123 Aux" AS Panel,
Color, Length, Size, <more columns>
FROM [ABC-DEF C123 Aux]
UNION ALL
SELECT "ABC T2345",
Color, Length, Size, <more columns>
FROM [ABC T2345 Aux]
UNION ALL
<and so on>;

I'd suggest you use this query to populate a new table, PanelSpecs say, by
appending all rows from it into an empty table with the requisite columns.
If there are a large number of panel names then you can fill the new table in
stages with a number of such queries. You can then delete the original
tables, which are now surplus to requirements.

Then create a query based on the PanelSpecs table which sums the lengths by
color and size and which includes a parameter which prompts for the panel
name. By testing for Null you can make a response to the prompt optional, so
all panels will be returned if the user leaves it blank when prompted.

SELECT Panel, Color, Size,
SUM(Length) As Total Length
FROM PanelSpecs
WHERE Panel = [Enter panel name:]
OR [Enter panel name:] IS NULL
GROUP BY Panel, Color, Size

Ken Sheridan
Stafford, England
 
Top