Query or Code?

[bluenose76]

I have been asked to help with a database that someone is writing.

He has a Table that has 200+ columns and approximately 70 of those columns are Qualifications. The first few columns are Title, Forename, Surname.

What he is trying to do is query the table to find all people with a specific Qualification and that is a Mr.

If I had written the db from the start then I would not have produced so many columns for qualifications. Instead I would have 1 column for quals and the populate each record in this way.

My question to the forum is the following:

With the current structure of his table (200+ Columns) Is there a way that I can produce a Query that will find the results that he requires, 1 specific Qualification and Title, but without displaying all other details in the table?

I understand that I can choose what field to include in the query but what I really do not with to do, is produce 70 different queries in order to filter each qualification.

Any and all help with this question really is appreciated.

Thank you

Bev

 

[wazz]

If I had written the db from the start then I would not have produced so many columns for qualifications. Instead I would have 1 column for quals and the populate each record in this way.

the good news is, you seem to know that a redesign is in order. that's good. the bad news is, for now you want to ignore that. oh well. (btw: the way you suggest designing the table isn't quite right, but we can ignore that for now).

With the current structure of his table (200+ Columns) Is there a way that I can produce a Query that will find the results that he requires, 1 specific Qualification and Title, but without displaying all other details in the table?

it's totally possible. you can get some results showing only the fields you want to see, but...it's unlikely that you'll get completely accurate results.

I understand that I can choose what field to include in the query but what I really do not with to do, is produce 70 different queries in order to filter each qualification.

so it sounds like you want to be able to select which qualification goes into the query "on the fly". if so, you need to gather all of the qualifications into a combobox so a user can select one, or show all the qualifications in a list so more than one can be selected. [note: if this were designed differently you would already have a table holding all of the qualifications (and nothing else), thereby making the creation of your list a snap. is this what you're after?

 

[bluenose76]

WAZZ

I think we are thinking along the same lines, even if i didnt explain myself correctly in my first post.

My solution would be to have a tbl_qualifications and to use a combo box, but as highlighted i did not creat this and am now just trying to ascertain if it is possible to achieve the end result using the design that he currently has.

Bev

 

[stopher]

You could write a query involving 70 UNION's thus effectively stacking each column into a single column. This would give you a more normalised view to work with. I don't know if there's a limit to the amount of script you can do in a single SQL though.

Another way I guess is to iterate through the columns in code. I pretty sure you could do this by compiling a new SQL string on each iteration using a different column name (say from a list in a seperate table), then seaching that dataset. I haven't tried but I can't think of any reasons why it can't be done.

Chris

 

[ajetrumpet]

Hey bluenose,

How about letting us "looksee" at this? Sounds interesting...

 

[Mike375]

Make another table with one field.

Copy and PasteAppend for each column and you will have 'em all in one column

Edit.....You would need to do two fields so as to have ID next to qualification etc and move the ID field next to the first column of qualifications and then remove each column of qualifications are you do copy/paste append....obviousy from a copy of the table