MS Access: Enhanced Crosstab Table Query

[Hydrapipe]

I am trying to create a custom table from a master database.

DB1 Columns:
COMP DWGTYP DWGNB
-------------------------------
item1 typea 12345
item1 typeb 54321
item1 typea 12346
item2 typeb 12458

I want to create a table that has unique records based on the item numbers
(COMP column), and the rows following the item numbers to include the drawing
type and number like so:

item1 typea 12345 typea 12346 typeb 54321

Anyone know how to do this, I am stumped!

-Brian

 

[[MVP] S. Clark]

I'd do it with VBA code.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Duane Hookom]

I wouldn't use a crosstab. Check out my response to "Concatnate Query" from
Dean Fraiquin from a little later today.

 

[Tom Ellison]

Dear Hydra:

A crosstab query is a limited, non-standard method that is a good
introduction to more complex query capabilities. The limited capabilities
of a crosstab query can be performed in another way, and the enhancements
you seek can be added.

First, I'd like to see if I understand your question. I'm thinking your
column headings would be:

typea typeb
COMP 12345 12346 12458 54321

In case this doesn't come through clearly, I mean to show a hierarchy of
column headings, where the DWGTYP is in the upper row and is subdivided into
DWGNB values in the second row of headings. One thing I mean to say,
however, is that "item 1" would not be a column heading, but would be a
value in the COMP column. I'm not sure whether that's what you mean, but it
isn't what you showed.

If you query the database first to find the column headings you seek, you
can then generate the query you need. From your sample data the query and
its results would be:

SELECT DISTINCT DWGTYP, DWGNB FROM YourTable

typea 12345
typea 12346
typeb 12458
typeb 54321

From this you can dynamically create the query necessary to return whatever
values, aggregate or simple, are stored for the derived columns. The SQL
itself is created in code. The varies whether you are using Jet, where you
would probably code VBA, or MSDE, where a stored procedure can do this for
you.

That's a rough overview of the process needed. The task is pretty much an
expert level job.