Relationships

[Aurora]

I am using Access 2003.
I created a main database with several small db tables to use for look-ups.
Ex: JobTitle in the main Db is a drop down menu to look up the job title in
a secondary small db. I do this so that the input is always the same.
For some reason, I started having a problem with this field - it wasn't
picking up the information correctly. So I created a new Job title field
(called jobtitle2) and made this one work correctly. Now I want to delete
the original job title field, but Access won't let me because of the
relationship. I went into tools > relationships and deleted the relationship
between the Main db and the Look Up db. But Access is still telling me there
is a relationship and will not allow me to delete this field. How do I
delete this relationship and the field?

Can someone please help me? I know I can work around this and simply not
use the original job title field - but I want to learn how to do this
correctly.

Aurora

 

[Jeff Boyce]

Aurora

I can't be sure from your description, but it sounds like you used the
Lookup wizard to create your dropdown menus within a table definition.
Lookup FIELDS cause problems like what you are experiencing. Using lookup
tables via forms, with listboxes and/or combo boxes is the preferred
approach.

If your table(s) has lookup fields, they are storing one value, but
displaying something else. Convert these to store the value of the ID from
the looked-up table, and get rid of the drop-down. Besides, Access tables
are for storing data, not for data entry/edit. That's what forms are for.

When you say you "deleted" the relationship, did you right-click on the line
relating the two tables and delete the line (i.e, the relationship)?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Aurora]

I don't want to use the Combo box because it is hard to change/update.
Besides I use this look up table for 2 different fields. The look up table
lists 25 job titles and job codes. All information IS entered via a form
(that is why I like to use drop down menus). It makes it easier to keep the
all of the information consistent. In my main table I have both of these
fields (job title and job code) looking up information from the same look up
table and inserting the correct information into the correct field within the
main Db; job titles in to [job titles] and job codes into [job codes]. When
I went into Relationships, I deleted not only the lines but the look up table
also. Can you offer any other suggestions?
Aurora

 

[Jeff Boyce]

Aurora

I'm still not entirely clear...

It sounds like you have [job title] and [job code] values in TWO tables,
your "main" table and your "lookup" table. This seems like it could be
redundant, but I don't have a clear picture of your data structure.

Since everything in Access starts with (needs to start with) data, could you
post your table structure for these two? For example, if you were doing a
student enrollment database, your tables might look like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Laying table structure out like this makes it easier to see how they are
related, and it seems to work reasonably well within the limitations of
email formatting.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I don't want to use the Combo box because it is hard to change/update.
Besides I use this look up table for 2 different fields. The look up
table
lists 25 job titles and job codes. All information IS entered via a form
(that is why I like to use drop down menus). It makes it easier to keep
the
all of the information consistent. In my main table I have both of these
fields (job title and job code) looking up information from the same look
up
table and inserting the correct information into the correct field within
the
main Db; job titles in to [job titles] and job codes into [job codes].
When
I went into Relationships, I deleted not only the lines but the look up
table
also. Can you offer any other suggestions?
Aurora

Aurora

I can't be sure from your description, but it sounds like you used the
Lookup wizard to create your dropdown menus within a table definition.
Lookup FIELDS cause problems like what you are experiencing. Using
lookup
tables via forms, with listboxes and/or combo boxes is the preferred
approach.

If your table(s) has lookup fields, they are storing one value, but
displaying something else. Convert these to store the value of the ID
from
the looked-up table, and get rid of the drop-down. Besides, Access
tables
are for storing data, not for data entry/edit. That's what forms are
for.

When you say you "deleted" the relationship, did you right-click on the
line
relating the two tables and delete the line (i.e, the relationship)?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/