I am a components engineer at an electronics manufacturer. Part of my job is to maintain a db of parameters for every part used in our products. I get these parameters from PartMiner, an online component search tool. My current db mirrors the PartMiner structure, but I keep running into two problems: 1) they change their attribute names from time to time, and 2) they do not use the same name across categories for seemingly common attributes (e.g.: tolerance or temperature). This makes it extremely hard to use flat tables for each category. I end up either adding columns or editing the column names to keep up with the changes.

My current db has a flat table for each PartMiner category of interest, a table of parts with their category ([Mfg Part Numbers]), and an [AllParts] table that has multiple Attribute/Value pairs for each component regardless of category. I have to search the [Mfg Part Numbers] table to find out what flat table to use to get the rest of the data. This will give me all the possible attributes for a given part, which is useful for comparing parts in the same category from different vendors, and determining all possible attributes for that category. The [AllParts] table makes it easier to get attribute/value pairs for any part without the use of a category.

Since there is always a possibility of adding a category (and the necessary table), this scheme becomes rather tedious. I have been looking into the TRANSFORM/PIVOT feature of MS SQL, and I have written some queries that create category "tables" from the [AllParts] table as needed. However, I am having problems filtering the results unless I actually make a temporary table from the transform, which becomes a problem for more than one user.

Here is my query:
Code:
TRANSFORM First(p.Value) AS FirstOfValue
SELECT p.VendorName, p.CleansedPN, First(p.Value) AS [Total Of Value]
FROM (SELECT  Status.VendorName AS VendorName, Status.*, AllParts.VendorName as PMName, AllParts.*
FROM Status INNER JOIN AllParts ON (Status.CleansedPN = AllParts.VendorPN) AND (Status.VendorName = AllParts.VendorName)
WHERE (((Status.Category)="Ceramic Chip Capacitor"))
ORDER BY Status.VendorName, Status.CleansedPN)  AS p
GROUP BY p.VendorName, p.CleansedPN
PIVOT p.Attribute
I want to wrap this in a SELECT statement with a WHERE clause something like this:
Code:
SELECT * FROM (the transform table) WHERE [Capacitance (Nominal) (uF)]='100p'
Any ideas?

The db is currently in MS Access, and I use ASP to display the results.

Thanks for your help!