Pull only fields with data

[trock12]

So here is my situation and I'm not even sure how to properly word it - but I'll make an attempt.

I have a table with say 26 columns(fields). All are Currency except one field - the Identifier, which has data for all lets say 1000 records. All other 25 fields may/may not contain data.

I want to have users be able to input the Identifier into a form query as the parameter, so it returns only those correspondence fields that are either Above $0.00 or blank.

Example: User inputs "ABCD" as parameter, it returns column 2,6,7,21,22 as those are the only ones with values.
User inputs "HYGY" as parameter, it returns column 5,9,14,15,20 as those are the only ones with values.

Can this be done?

 

[Joe4]

Can this be done?

It could be done easily if the table was "normalized" (properly designed).

Instead of having 26 different currency fields, you probably should only have one, with a currency identifier also.

So instead of having a set-up like this:

Identifier    CurField1    CurField2   CurField3 ...
ABC               10             20          30
XYZ                0             15           0
...

It should look like this:

Identifier    CurIdentifier    CurValue   
ABC               1               10          
ABC               2               20
ABC               3               30
XYZ               1                0
XYZ               2               15
XYZ               3                0
...

Then querying the fields for values greater than zero becomes quite easy, as you only have to query one field instead of 26.

 

[alansidman]

In addition to Joe's response, for future reference, you may want to look at this link which describes how to design data bases that comply with normalization.

http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf