I have a form with 18 seperate fields.
frm_Points
Run_point_Venue_A
Run_point_Venue_B
Run_point_Venue_C
and so on...
If this Form reflects the structure of your Table... as I think it may from
previous discussions here... your table structure IS WRONG. Someday you will
have a nineteenth venue and be out of luck; even without that, you'll have a
nightmare searching "which runs involved venue X at some point?" as you'll
need to use a an 18-level OR clause in your query. OUCH!
I have another form which is Called 'frm_Getrounds' with a field called 'Note'
Though there are not 18 matching records for 'Note'; any one of the 18
fields can match any of the contents of 'Note'. Though they only match once.
Case in point. If you had a one to many relationship from a Runs table to a
Points table, with ONE record per point, this would be easy.
In other words the 'Notes' contents are unique, as are the fields on
'frm_Points' and will not repeat.
How do you enforce this? With code?
Both Forms are bound by a Primary Key: [Run_No]
an example of both forms is below:
frm_Points (Run_No 17)
Run_point_Venue_A = Main St
Run_point_Venue_B = South St
Run_point_Venue_C = Narrow St
and so on...
'frm_Getrounds' (Run_No 17)
[GetRound_ID] 177 [Note] = Main St
[GetRound_ID] 181 [Note] = South St
[GetRound_ID] 192 [Note] = Narrow St
Is this a memo field with all of these records concatenated into it?
Gnnnnnn...
This is what I want to do:
For everytime a matching record field in 'NOte' occurs, I want to be able to
say "Yes" in (18) text boxes on 'frm_Points. They will be called TRB_A,
TRB_B, TRB_C and so on...
If someone can help me with the code for one matching sequence, I can make
the adjustments for the remaining 17.
So you want to match *any value anywhere in a big memo field* with *any one of
18 fields*? You could write some rather snarky VBA code to do so, if you can
be rock solid certain that you have absolutely consistant spelling in both
redundant fields...
I'm just afraid you are quite a ways down a BADLY DENORMALIZED unmanagable
data structure.
--
John W. Vinson [MVP]
John,
I understand your points that you made; but unfortunately you appear to have
got the wrong end of the stick.
This database is not entirely 'conventional' it is effectively a fixed,
rigid book. The 18 fields will never ever get to 19 fields. it is not a
matter of lack of foresight, it is a matter of a fixed rule that is actually
intrinsic to the working of 'the book'. Though, you are correct when you say
that searching across 18 fields can be a nightmare.
regading the 'Note' field. It is not a memo field. It is a text field. The
enforcement is natural, not machine/code driven. Each Main run (with 18
venues) is unique, and by defintition are not repeated.
The two example forms I gave (with relevant record structure) do not contain
memo fields (I know the limitations of memo fields). The one's I use are all
text or number fields. I was merely showing them to give an expert an idea of
what can be linked. Nothing is conactenated; they are stand alone fields in
their own respective tables.
I don't want to match *any value in a big memo field. I just want to match a
text field in frm_Getrounds with a field in frm_Points.
Perhaps my use of syntax and description has led to this misunderstanding,
and I apologise for such error. I know and understand why you people regard
unnormalized tables and structure as the deadliest sin.
The reason for the initial 18 fields (venues) per Master Run was I needed a
way of keeping them in absolute order per page; relative to each Run. and I
am not skilled enough to do it any other way.
The table looks like this.
Run_point_List_ID (Autonumber)
Run_No (Number child field)
Run_point_Venue_A (Text)
Run_point_Venue_B (Text)
Run_point_Venue_C
Run_point_Venue_D
Run_point_Venue_E
Run_point_Venue_F
Run_point_Venue_G
Run_point_Venue_H
Run_point_Venue_I
Run_point_Venue_J
Run_point_Venue_K
Run_point_Venue_L
Run_point_Venue_M
Run_point_Venue_N
Run_point_Venue_O
Run_point_Venue_P
Don't think of this as an initial blank database, think of it has a very
(delibrately) fixed, rigid book. There is no need to add further data. The
only possible editing that is required is simply the ability to change a
venue name. It is essential that each venue appears in the order that the
fields denote; I have no other means of ensuring that this can be so, unless
I implement this (unorthodox and non-normalized) structure. But in saying
that; it is somewhat normalized, in the sense that each set of 18 fields
relate to a master record [Run_point_List_ID], which in turn relates to a
parent master record (Run_no). This system has been working perfectly for a
long time, and I have not encountered the problems that you mentioned, except
the 'search issue', but even then, because of the 'book' structure, the user
will not use an orthodox search process, becuase the Run_No will dictate what
he is looking for. I hope this clears things up for you and makes a little
more sense than my initial posting.
