Query to list non-empty fields? (1 Viewer)

[dudezzz]

Hi...I have the following requirement:

I have a table called "tblselectrso" with the following fields:

stockno rso1 rso2 rso3 rso4 rso5
s1 1 3
s2 2 4 5


stockno is a text field and rso1 through rso5 is a Number field.

Given the value of "stockno" I want to run a query to list all the other fields (rso1 through rso5) that are NON-EMPTY.

Is this possible at all? Can anyone help me please?

Thanks

Please find attached a sample db.

 

[neileg]

Your problem arises because your design is not normalised. Instead of having 5 fields in a table you should have up to 5 records in another table. The record would hold the rso number and the value. This way, your query would be very simple.

The way you have it now, you could run a series of 5 queries, each one on one of the rso fields and test for Is Not Null.Then you can union these 5 queries to give you one list.

 

[dudezzz]

Thank you. This was part of my original problem. I am addressing my original problem in a different thread below:

http://www.access-programmers.co.uk...ead.php?t=97178


P.S. Moderator - I apologize for inadvertently creating a duplicate thread here.....But the main problem is addressed on the link above.