Adding a column for notes in lookup table

[Guest]

Hi,

I have a lookup table that has a column of names, and a column of numbers,
which are vendor names and ID #s, which are used for SAP.

I would like to some way add a column that is just notes: names, websites,
relevent into.

Can I just add a column to my table, or is there a better way?

Thanks,

Eric in FL

 

[Amy Blankenship]

Hi,

I have a lookup table that has a column of names, and a column of numbers,
which are vendor names and ID #s, which are used for SAP.

I would like to some way add a column that is just notes: names, websites,
relevent into.

Can I just add a column to my table, or is there a better way?

That depends. For instance, if you expect to have multiple types of
information on each vendor, you might actually need two more tables, one for
note types (another lookup table) and the other for the actual notes. This
will make it easier to categorize the information you have and search it.
However, if you know that each vendor will have a website, for instance,
then that should probably be its own column in the vendors table.

HTH;

Amy

 

[Guest]

That depends. For instance, if you expect to have multiple types of
information on each vendor, you might actually need two more tables, one for
note types (another lookup table) and the other for the actual notes. This
will make it easier to categorize the information you have and search it.
However, if you know that each vendor will have a website, for instance,
then that should probably be its own column in the vendors table.

HTH;

Amy

Well, there are some that have websites, some that have contact names,
numbers, while some have nothing at all.

Basically, since it's a hodgepodge of information, some of it's on post-it
notes attached to a printout of the table, and I just wanted to add it
electronically. So it would be a "catch all " kind of field. Seems to work
on a a copy of the database without issue.

As long as it doesn't do any harm, I'll leave it like that, unless anyone
thinks of a better method.

Thanks,

Eric

 

[Chris L.]

Well, there are some that have websites, some that have contact names,
numbers, while some have nothing at all.

Basically, since it's a hodgepodge of information, some of it's on post-it
notes attached to a printout of the table, and I just wanted to add it
electronically. So it would be a "catch all " kind of field. Seems to work
on a a copy of the database without issue.

As long as it doesn't do any harm, I'll leave it like that, unless anyone
thinks of a better method.

Thanks,

Eric- Hide quoted text -

- Show quoted text -

If for example 80% of that additional data is websites and contact
names, it makes sense to have dedicated columns / tables for websites/
contact names. That way your "catch-all" field only stores 20% of the
assorted data.

 

[Guest]

I would always recommend making tables as granular as possible. In other
words, add columns for email addresses, URLs, point of contacts, etc. It is
so easy to do with Access. However, I do use a lot of memo fields in my
applications, commonly ten or more in an individual table but then I am using
them to store large blocks of text, as much as 65,000 characters is possible
and I've done that on occasion.

Actually, I would propose that you create a separate table for the points of
contact with fields for people's names, telephone numbers, emails,
cellphones, addresses, etc. depending on how you may be required to use the
data in the future. You could use a find text function ("Like") in a query
to retrieve records that match your criteria but parsing out the data into
separate fields is a more effective use of the RDBMS.

Moreover, if you ever need to recombine data in a query, you'll only be
pulling data that are needed at the time. For example, if you want to map
vendors geographically, a program like MapPoint could use the Address field
directly. Manually moving a 1,000 or 10,000 addresses later would be a waste
of time. Similarly creating a spreadsheet or Outlook table from an Access
table with names, addresses, etc. would be a snap if the data were parsed.

If parsing data into different fields is time consuming now, consider using
dual monitors if the source data are already in digital format. Your fields
can be as large as you need them to be for dragging text from one monitor to
another.

LDN

 

[Amy Blankenship]

I would always recommend making tables as granular as possible. In other
words, add columns for email addresses, URLs, point of contacts, etc. It
is
so easy to do with Access. However, I do use a lot of memo fields in my
applications, commonly ten or more in an individual table but then I am
using
them to store large blocks of text, as much as 65,000 characters is
possible
and I've done that on occasion.

Using a "Notes" table and a "NoteType" table satisfies her desire not to
have empty fields stored for some records where they are not needed. And I
do agree with you that Contact information should not be just thrown into
either a notes field or table. My thought is that she should have a
Contacts table to store the information for the individuals who can be
contacted and a VendorContacts table to allow as many contacts as necessary
to be associated with a given vendor.

-Amy