Flexible "Matrix" query

  • Thread starter Thread starter Bryan Dickerson
  • Start date Start date
B

Bryan Dickerson

Hi all. Newbie to the group and woefully lacking in SQL skills and
terminology so I'll beg your patience.

I have an existing query that I want to make data-driven. The query
produces 3 columns: Part Number, Warranty Type, and Qty. I would like to
make each Warranty Type a column that sums the quantities of each part
number. That way if at some point a new Wrty Type is added, the query
automatically picks it up. Now having said that, the query is part of a
stored procedure that uses several temp tables to create and hold data until
a final query pulls them all together. So there would need to be some way
to pick up all warranty types in the final query without hard-coding any
values. Surely this has been done before, right?

TIA!
Bryan
 
Dear Bryan:

In Access Jet you would typically want to look at having a crosstab query.
If this is the case, I recommend you study up on crosstabs.

If you have a stored procedure, then it sounds like you're using MSDE/SQL
Server. In this case, I recommend having code write the query for you.
You'll need to have a subquery generated for each Wrty Type found in the
data.

Please get back with which route sounds useful, and what information you
might need to implement it.

Tom Ellison
Microsoft Access MVP
 
Back
Top