Can U do this in Access?

[Roly Reefer]

Hi,

I am making a database for a project for a new MIS system we are implementing, which details training requirements. We are trying to analyse what programs in the various systems people use and will then train them on the new systems equivalent.

Where I am stuck is I have a load of programs with Yes/No formats and I want to show on a form the personal details of everybody and then only show the fields which are yes in the textboxes on one line and nothing else.

I have uploaded my database - the queries I want to do this to are:

Central Training People
CRM Training People
Crystal Training People
Windows Training People

Is this possible?

Thanks for your help,


Roly

[Dennisk]

Are you saying yo want to turn rows into columns but only where the Control in each row is YES?

[Roly Reefer]

Yes I am, can u do this?

[Dennisk]

It is possible. Infact I use this technique myself in my lastest DB.
Two way to do this either create a query to retrieve each row. then combine all the queries into one master query. Or create a function that retrieves each row and concatenates them all to a string and return that.
Either way you will not be able to update the columns.

[Roly Reefer]

There are going to be 4,500 rows, so creating that many queries would be to much to display in a form, or as a query - unless there's an easier way of doing it?

Thanks for your help,

Roly

[neileg]

I'm sorry to say this but your design is dreadful since you have taken a spreadsheet approach. Instead of having all those skills as fields in the table, they should be individual records in a related table. This is part of the process known as normalisation.

[Roly Reefer]

I know the database is awful, but it is only for a one off project and is just a short term thing.

I didn't know about this query when I set it up, otherwise I would have done it instead of thinking about it first.

Thanks for your help,

Roly

[Dennisk]

One off projects have a habit of staying around for years.