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.

I think you have a table design problem. You appear to have a one-to-many relationship that you have implemented as a repeating group. If you post the table structure, I'll suggest a more appropriate structure.

(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

You do have a problem with the data structure (I expect a round of applause from the other members for deducing this from the original post http://www.access-programmers.co.uk/ubb/smile.gif ). You have a 1-to-many relationship and it will take two tables to represent it properly.

tbl1:
RecID (primary key)
SAFTY_FUNC
UNIT
EQUIP_CODE
UNITS

tbl2:
RecID (primary key field1)
ASS_EQUIP (primary key field2)
MANUF
MODEL
CATID
SCN
ROOM
ELEVATION
EQ_PKG0

Table 2 should be viewed/updated via a subform.

Does that reduce the number of columns sufficiently? It also allows you to manage more than 5 pieces of associated equipment.

Take a look at some relational design papers before continuing. It will save you a great deal of trouble in the long run. A good one to start with is:
http://support.microsoft.com/support/ kb/articles/q234/2/08.asp?FinishURL=%2Fdownloads%2Frelease%2Easp%3FRe leaseID%3D13473%26redirect%3Dno (http://support.microsoft.com/support/kb/articles/q234/2/08.asp?FinishURL=%2Fdownloads%2Frelease%2Easp%3FRe leaseID%3D13473%26redirect%3Dno)

I could do better with your table design but I don't really know enough about the application at this point. Your field names are somewhat cryptic and I can't translate them to anything I'm familiar with.


[This message has been edited by Pat Hartman (edited 08-08-2001).]

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.

You're right, I don't understand why you have 5 instances of each field in the same record. Each instance should be a separate record. There was only one instance of Unit which is why I put it in the first table. Is it the field which should be the second part of the primary key of the second table?