Relational query to flat table

[duklaprague]

I've created a query based on the two tables, joined by ID.

And using that created a cross tab query, which works up to a point, but is impractical due to there being around 80 categiories which means 80 columns in the new table, with only a max of six checked for any feature.

What I'd really want to do is have maybe six columns (Category 1 - 6), populated where appropriate.

Of course this does beg the question of why we didn't just have those fields in the main table in the first place, but there's an extra field in the categories table (Primary Category, which is just a yes / no).

So basically now have a query that looks like :

Feature(tblFeatures), Category(tblCategories), Category1, Category2, Category3, Category4, Category5, Category6 (tblFeatures)

Feature1, Category1
Feature1, Category2
Feature1, Category3

etc

And would like to update the query to :

Feature1 (tblFeatures), Category1 (tblCategories), Category1, Category2, Category3 (tblFeatures)

etc

But not sure how to automate this process..?

 

[SkipVought]

Hi,

When you stated, "...why we didn't just have those fields in the main table in the first place..." are you referring to Category1, Category2, Category3... or WHAT?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]