Select Noted Memo Field

[Rod]

Hello,

I have a table with 25 memo fields used sequentially for separate comments,
i.e. if MemoFielD4 is in use then so are 1, 2, and 3. The Note field in this
table has a number which is used to draw attention to a particular memo
field; if Note is 2 then MemoField2 should be the only memo field selected,
if Note is 23 then MemoField23 should be selected - later to be used in a
form and report. How can I have a query that displays only the memo field
marked by Note?

Thanks

 

[John Spencer]

BAD DESIGN. You really should have a memo table with three (or more fields)
MemoTable
ID (reference to the primary key field of your current table)
Sequence (a number from 1 to 25 indicating which memo this is)
TheMemo (field with the memo)

If you are stuck with the current design, you may be able to use the choose
function to return the desired memo field.

Field: GetTheMemo:
Choose([NoteField],MemoField1,MemoField2,MemoField3,MemoField4, ...,
MemoField24, MemoField25)



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Rod]

OK, your idea sounds better. A few questions:
1) I create tblMemos with fields 1-25. I will need to associate a memo with
a person from linked tblCandidates[Candidate]. Is this a simple join?
2) I have created tblMemo:
Field Nme Data Type
ID - Primary AutoNumber
Sequence Number
TheMemo Memo
Memo1 Memo
..
..
..
Memo25 Memo

Is this what you had in mind?

Each candidate should have as many as 25 possible memos. Only one will be
the one of interest, the others are for history only. How will this be
accomplished?

BAD DESIGN. You really should have a memo table with three (or more fields)
MemoTable
ID (reference to the primary key field of your current table)
Sequence (a number from 1 to 25 indicating which memo this is)
TheMemo (field with the memo)

If you are stuck with the current design, you may be able to use the choose
function to return the desired memo field.

Field: GetTheMemo:
Choose([NoteField],MemoField1,MemoField2,MemoField3,MemoField4, ...,
MemoField24, MemoField25)



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I have a table with 25 memo fields used sequentially for separate
comments,
i.e. if MemoFielD4 is in use then so are 1, 2, and 3. The Note field in
this
table has a number which is used to draw attention to a particular memo
field; if Note is 2 then MemoField2 should be the only memo field
selected,
if Note is 23 then MemoField23 should be selected - later to be used in a
form and report. How can I have a query that displays only the memo field
marked by Note?

Thanks

 

[John Spencer]

NO, you create a table with three or four fields
ID-Primary - Autonumber
Sequence - a number from 1 to the memo number
TheMemo - The text of a memo
CandidateId - the value of the primary key in the Candidate field


You can then link the tblCandidates to tblMemos on the CandidateID
fields and pull one specific memo record or all of them.

The tricky part will be setting the sequence numbers so the memos are in
order.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

OK, your idea sounds better. A few questions:
1) I create tblMemos with fields 1-25. I will need to associate a memo with
a person from linked tblCandidates[Candidate]. Is this a simple join?
2) I have created tblMemo:
Field Nme Data Type
ID - Primary AutoNumber
Sequence Number
TheMemo Memo
Memo1 Memo
.
.
.
Memo25 Memo

Is this what you had in mind?

Each candidate should have as many as 25 possible memos. Only one will be
the one of interest, the others are for history only. How will this be
accomplished?

BAD DESIGN. You really should have a memo table with three (or more fields)
MemoTable
ID (reference to the primary key field of your current table)
Sequence (a number from 1 to 25 indicating which memo this is)
TheMemo (field with the memo)

If you are stuck with the current design, you may be able to use the choose
function to return the desired memo field.

Field: GetTheMemo:
Choose([NoteField],MemoField1,MemoField2,MemoField3,MemoField4, ...,
MemoField24, MemoField25)



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I have a table with 25 memo fields used sequentially for separate
comments,
i.e. if MemoFielD4 is in use then so are 1, 2, and 3. The Note field in
this
table has a number which is used to draw attention to a particular memo
field; if Note is 2 then MemoField2 should be the only memo field
selected,
if Note is 23 then MemoField23 should be selected - later to be used in a
form and report. How can I have a query that displays only the memo field
marked by Note?

Thanks

 

[John W. Vinson]

Is this what you had in mind?

NO.

Reread his post.

"Fields are expensive, records are cheap" - you want ONE memo field, and 1 -
or 3 - or 25 - or 41 *records*, one record for each memo.

John W. Vinson [MVP]