New database design with lookup tables and forms

[mstumpo]

I have an existing database that my client gave me. They want pull-downs for
some fields (lookup tables) and forms created using the new design.

One of the columns they want is a memo field due to the amount of
information they have for that column. Apparently, you can't have a memo
field in a lookup column? Is that correct and if so, how do I handle this.

Also, one of the pull-downs (in the form) displays information from the next
line in the list, while others appear visually fine. I have checked the
properties for the pulldown and can't find what to adjust. Any comments would
be appreciated.

 

[Tim Ferguson]

I have an existing database that my client gave me. They want
pull-downs for some fields (lookup tables) and forms created using the
new design.

Please tell me that you really do mean lookup tables and not lookup
fields...

One of the columns they want is a memo field due to the amount of
information they have for that column. Apparently, you can't have a
memo field in a lookup column? Is that correct and if so, how do I
handle this.

This really sounds like a Design Problem. Is there a need for each record
to have its own large chunk of text (even if each one is nearly all the
same) or, if there are genuine repeats, they should not be linking to the
text stored in a different table.

Also, one of the pull-downs (in the form) displays information from
the next line in the list,

I don't know what you mean by the "next line in the list". If you want
the next record in the table, then bear in mind that it's a meaningless
concept unless you specify the sort order; in which case it's not too
hard to create either a DLookup or a query to get the value. Again, I
always suspect a Design Problem when one record depends on "the next
one" because it's not really a sound concept in db theory.

Are you able to post more information about what you are trying to
achieve?

B Wishes

Tim F

 

[mstumpo]

Yes, lookup table. In Table Design, I select the field and in the data type
column, I select the lookup wizard to select a field in another table.

Not all records in the field (called Style) I am discussing has multiple
lines. It depends on the furnitire being specified. For example, you may only
need LBS-236; TR-G in one record and then;

(1) NFDL-3066-1HR; H-34; HP-34; TR-G; TR-G (DESK)
(1) NFRL-2442-1JL; H-34; HP-34; TR-G (RETURN)
(1) NAK-2P (KEYBOARD)

in the next record.

If there is a better way to handle this, I am all ears.

If you need more information, let me know.

 

[John Vinson]

Yes, lookup table. In Table Design, I select the field and in the data type
column, I select the lookup wizard to select a field in another table.

Don't.

See http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature. The Lookup Wizard makes it easier to use table datasheets
for data entry - but you shouldn't be using table datasheets for any
purpose other than debugging in any case!

Not all records in the field (called Style) I am discussing has multiple
lines. It depends on the furnitire being specified. For example, you may only
need LBS-236; TR-G in one record and then;

(1) NFDL-3066-1HR; H-34; HP-34; TR-G; TR-G (DESK)
(1) NFRL-2442-1JL; H-34; HP-34; TR-G (RETURN)
(1) NAK-2P (KEYBOARD)

in the next record.

If there is a better way to handle this, I am all ears.

If you're talking about copying all of this information verbatim into
multiple records of a table, it's probably neither necessary nor
beneficial. Can you have a Style table with a unique ID, containing
these details? Or are the multiple lines and semicolon-delimited
strings in fact multiple atomic units of information which should
perhaps be stored as records in another table?

John W. Vinson[MVP]

 

[Tim Ferguson]

Yes, lookup table. In Table Design, I select the field and in the data
type column, I select the lookup wizard to select a field in another
table.

Oh Dear: this does sound like the LookUp Field Wizard. Remember that
Access wizards are really evil trolls that live under the stairs and will
eat your children and steal the silver. Oh, and they ruin databases too.

It takes hardly any time to create a table and a relationship, and then
you can rest in the knowledge that (a) you know what is being stored
where and (b) that it's been done right. Check out John's response.

Not all records in the field (called Style) I am discussing has
multiple lines. It depends on the furnitire being specified. For
example, you may only need LBS-236; TR-G in one record and then;

(1) NFDL-3066-1HR; H-34; HP-34; TR-G; TR-G (DESK)
(1) NFRL-2442-1JL; H-34; HP-34; TR-G (RETURN)
(1) NAK-2P (KEYBOARD)

Yes: this definitely sounds like a design problem. This chunk of text
obviously means nothing to me, but I guess it does have meaning to
someone. Do you need to
(a) search for all the TR-G
(b) search for all TR-G which don't have HP-34
(c) find all records with a Keyboard style
(d) find records with more than one style?

If the answer to any one of these questions (or several others) is even
"well, probably not" then you have a whole lot more analysis on your
hands.

Bear in mind that clients may (occasionally) have a clear idea of what
they want to achieve, but have no business telling you how they want to
achieve it. I don't tell my garage what brand of oil to put in my car or
what colour spanners to use on it.

All the best


Tim F