Query to exclude all blank fields in each record

[BarryMK]

I have a simple dbase that lists individual staff members' qualifications, skills and hobbies.

One person may have 20 qualifications and another may have 1 or none.
I have a separate field for each qualification.

Is it possible to write a query that only returns fields that are not null in each record so that the subsequent report is not full of blanks? I know I can use Can grow - Can shrink on the report but I'd rather eliminate blanks via a query if I can.

 

[gemma-the-husky]

do you mean you have fields (columns) in the table for each qualification. does that mean you have to add another field (column), if you want to track another qualification

 

[BarryMK]

do you mean you have fields (columns) in the table for each qualification. does that mean you have to add another field (column), if you want to track another qualification

HI Gemma, sorry I've been in a meeting.
This is a quick and dirty database I ran up to enable staff to input their details more easily than by using a spreadsheet. Each record has 53 fields each of which can hold a qualification. Naturally a lot of these will be left blank. I want to make a quick report minus the blanks.

 

[gemma-the-husky]

are the qualifications just any old data, or is each column a heading for a particular qualification?

 

[BarryMK]

Any old data, the qualifications are so varied. Probably ony 10 are shared by more than 1 person. It's a very crude dbase and if the worst comes to the worst I might just export it to a spreadsheet for distribution. I just prefer an access report.

 

[gemma-the-husky]

if you don't want to sort or use the data and its just text ,the easiest way would be to have it as a memo field, then you could put all the qualifications in a single entry, and report the details really easily.

what i meant was eg is column1 a checkbox for British Computer Society membership, so you can find all employees with that qualification

if not, and 1 employee puts BCS in column 1, but another puts BA or MA in column 1, then as I say you could use a memo field.

Then on your report you just set the properties for the textbox to can grow and you wont have any display problems

 

[BarryMK]

if you don't want to sort or use the data and its just text ,the easiest way would be to have it as a memo field, then you could put all the qualifications in a single entry, and report the details really easily.

what i meant was eg is column1 a checkbox for British Computer Society membership, so you can find all employees with that qualification

if not, and 1 employee puts BCS in column 1, but another puts BA or MA in column 1, then as I say you could use a memo field.

Then on your report you just set the properties for the textbox to can grow and you wont have any display problems

Thanks but I discarded that solution at the start as I need to be able to search on a qualification key word and memo won't do. The sheer number of possible qualifications and the fact that I don't know what the qualifications are until they're listed rules out the check box method.

Frankly all I need is to know if a query can return nonblank fields only from a record. Anything else is not justified by the time spent on it. My gut feeling is if it can be done it would need some sort of loop through the records but that's outside my expertise.

 

[gemma-the-husky]

I don't think you can selectively exclude blank columns, but I may be wrong.

Try this way then.

defining another column in a query, and populate it with

QualList: (qual1 +", ") & (qual2 +", ") etc

doing it like this means nulls will stay null, and any non-nulls will have comma separators appended to them. Then you only get a single string to put in a text box

 

[BarryMK]

I don't think you can selectively exclude blank columns, but I may be wrong.

Try this way then.

defining another column in a query, and populate it with

QualList: (qual1 +", ") & (qual2 +", ") etc

doing it like this means nulls will stay null, and any non-nulls will have comma separators appended to them. Then you only get a single string to put in a text box

Thanks that may do it - I'll let you know. I think the problem is bad database design, I'm trying to make a silk purse out of a sow's ear. If your suggestion doesn't work I'll rebuild from the ground up.

 

[BarryMK]

Thanks for the help Gemma but I've called time on it for now. I've asked for all staff to complete their inputting and then I'll sort all the skills into a few broad types and assign them to each record with combos, so someone with four Civil Engineering qualifications just gets one C Eng marker. That will be easier to report on. Cheers Barry

 

[Rich]

Surely this is just a simple one to many set up?

 

[BarryMK]

Surely this is just a simple one to many set up?

You're right Rich but the main problem I have is the purpose of this data keeps changing. I've got a boss who doesn't quite know what he wants until he sees it on this one, so now we're going to bottom all that out and start again with a clear brief. It looks at the moment as if the requirements will end up being a bit simpler. I can only hope!

 

[yammoo]

check out www.lebans.com this has a lot of obscure sample dbs that were created. one has a search of the memo field. another has memo limited on a document via a module or function. dont recall which.... newayz hope this helps, i have certainly benefited from it.

good luck!

 

[yammoo]

or try lookin at this post, it might help if u dont wanna try memo fields.

Fields (Variables) in Report with null values

w/the questioner as Mechele.

hope it helps.

 

[BarryMK]

Thanks yammoo

Following my last post I rebuilt completely and the problem no longer arises.