query from linked table

[Guest]

The end result is to produce a report showing products that indicate a value
higher than $0.00
My linked table doesn't have any relationships with other tables.
The first column heading is product name and the rows list all the products.
The other columns headings show the suppliers and the cells below them show
the price quoted.
Example:
Product Name Supplier 1 Supplier 2 Supplier 3
Product A $0.00 $0.85 $0.87
Product B $1.45 $0.00 $1.48
Product C $2.70 $2.72 $2.69

I want to produce a query from this linked table showing only suppliers that
quoted prieces higher than $0.00 and sorted (organized) as follows: (sorted
by product)
Supplier 2 Product A $0.85
Supplier 3 Product A $0.87

Supplier 1 Product B $1.45
Supplier 3 Product B $1.48

Supplier 1 Product C $2.70
Supplier 2 Product C $2.72
Supplier 3 Product C $1.45

Thank you!!!!!

 

[Douglas J. Steele]

I don't suppose there's any hope of getting that linked table changed so
that it's properly normalized? You could easily create a table that looks
the same as that from a normalized table using a Cross-tab query.

If you can't change the source, though, you can create a query that
normalizes the table, and use that query as the source for your subsequent
queries.

Something like:

SELECT "Supplier 1" AS Supplier, [Product Name], [Supplier 1] AS Price
FROM MyLinkedTable
UNION
SELECT "Supplier 2" AS Supplier, [Product Name], [Supplier 2] AS Price
FROM MyLinkedTable
UNION
SELECT "Supplier 3" AS Supplier, [Product Name], [Supplier 3] AS Price
FROM MyLinkedTable
.... (continue as required)

Saving that as qryMyLinkedTable, you could then have:

SELECT Supplier, [Product Name], Price
FROM qryMyLinkedTable
WHERE Price > 0
ORDER BY [Product Name], Price