List which field names are populated in table?

[wrek]

Hi guys,

I have a table with about 40 fields. For any given record, usually only about 3 to 10 are populated.

Is there a way I can run a query of some sort to eventually populate another field in that table with:

"Stock Number, Quantity, Dosage"

So in essence, this field tells which of the field names are populated for each record.

(I have a autonumber record number field)

Thanks.

 

[wrek]

(0) = "RecID"
(1) = "SAFTY_FUNC"
(2) = "UNIT"
(3) = "EQUIP_CODE"

(4) = "UNITS"
(5) = "ASS_EQUIP0"
(6) = "ASS_EQUIP1"
(7) = "ASS_EQUIP2"
(8) = "ASS_EQUIP3"
(9) = "ASS_EQUIP4"

(10) = "MANUF0"
(11) = "MANUF1"
(12) = "MANUF2"
(13) = "MANUF3"
(14) = "MANUF4"

(15) = "MODEL0"
(16) = "MODEL1"
(17) = "MODEL2"
(18) = "MODEL3"
(19) = "MODEL4"

(20) = "CATID0"
(21) = "CATID1"
(22) = "CATID2"
(23) = "CATID3"
(24) = "CATID4"

(25) = "SCN0"
(26) = "SCN1"
(27) = "SCN2"
(28) = "SCN3"
(29) = "SCN4"

(30) = "ROOM0"
(31) = "ROOM1"
(32) = "ROOM2"
(33) = "ROOM3"
(34) = "ROOM4"

(35) = "ELEVATION0"
(36) = "ELEVATION1"
(37) = "ELEVATION2"
(38) = "ELEVATION3"
(39) = "ELEVATION4"

(40) = "EQ_PKG0"
(41) = "EQ_PKG1"
(42) = "EQ_PKG2"
(43) = "EQ_PKG3"
(44) = "EQ_PKG4"

(45) = "Populated?"

OK, these are the 44 field names with my user defined #45. I'm not really having a problem with table structure. This table contains fields that the user MIGHT want to change (ie. they are only populated with proposed changes). So for most records, there are only a few changes.

What I want is to enumerate which fields are changed (ie. have data) for each record.

So for RecID 3913, if it only has data in SAFTY_FUNC, UNIT and EQUIP_CODE, field 45 should be populated with:

"SAFTY FUNC, UNIT, EQUIP_CODE"

Appreciate your time.

wrek

 

[wrek]

I think you misunderstood. ASS_EQUIP 0,1,2,3,4 does not refer to Associated Equipment Number1, Number 2....

It refers to the Associated equipment that each part has (only 1 for each) for each Unit - namely Unit 0, unit 1, unit 2 etc. for the Nuclear Plant I work for. Ass_Equip is just another data field, there is no need for it to be a primary key.

Here take a look at this:
http://www.angelfire.com/pw/access/EQprob.doc


Thanks Pat.