Extracting data from multiple fields

[jimleon]

I have a table of customers business categories with 13 fields - customer, category1, category2, ....... category12. The first field contains the customers name and the next 12 contain categories, so the customer may have between 1 & 12 different categories (ie plumber, delivery driver). These category descriptions where generated from a pull down box on a form, from the table 'category'.
I need to write a query to output tabulated results with each category listing all the customers who have selected that category thus...

Architect
Smith & sons
wright ltd

Electrician
jones electrical
wright ltd
petersons

Builders
petersons

etc...

Any help would be most appreciated.

Thanks in advance guys

[George Oro]

Not so sure if this will help but try Crosstab Query.

I believe its more easy to make some queries if you will change your structure. Make one new table e.g tblCustomerCate gory then add 3 fields:
CustomerID - this will link to the main customer table
Category - same approach
Timestamp - default value now() just for your info when the record inserted.

from the structure you are not limited.

HTH,
George

I have a table of customers business categories with 13 fields - customer, category1, category2, ....... category12. The first field contains the customers name and the next 12 contain categories, so the customer may have between 1 & 12 different categories (ie plumber, delivery driver). These category descriptions where generated from a pull down box on a form, from the table 'category'.
I need to write a query to output tabulated results with each category listing all the customers who have selected that category thus...

Architect
Smith & sons
wright ltd

Electrician
jones electrical
wright ltd
petersons

Builders
petersons

etc...

Any help would be most appreciated.

Thanks in advance guys

[jimleon]

Still not sure how that would work as I have 3 lists of data to work with, customer, category (1-12) and the category description.

[NeoPa]

Try working from something like this :

SELECT Customer
FROM Categories
WHERE Category1 & Category2 & Category3 & Category4 & Category5 & Category6 & Category7 & Category8 & Category9 & Category10 & Category11 & Category12 Like '*' & [Enter required Category] & '*'

[jimleon]

Thanks NeoPa
That works fine.

Cheers

[NeoPa]

My pleasure.
It's good to be able to post ideas rather than the full solution with all i's dotted and t's crossed for a change :)