Hope someone can help with this.

Basically we have an Access database with a main table, and several linked tables, joined on a unique ID.

For example :

tblFeatures :

FeatureID
FeatureName
etc

tblCategories
FeatureID
Category

So each feature can have many categories in a one-to-many relationship.

What I have a requirement to do is to pull that out as a flat file in Excel, ie

FeatureID, FeatureName, Category1, Category2
etc

What's the best way to go about this?

First, do not double-post. Since you created the duplicate post, you have the power to delete it. Start by editing it. The Delete button will show up and you can remove the duplicate.


Second, when you have a requirement to flatten something, use a query. You can export queries just like they were tables.

Sorry 'bout that - didn't mean to post twice, but I thought I hadn't finished posting the second one, and didn't realise I could delete it - quite often that's a mods thing only.

Anyway - back to the problem.

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..?