Access Relationship

[Kevin]

Its been a while since I have had to create a new Access
database, and while
I can swear I am not doing anything wrong, Access insists
that I am.

To simplify my situation, I have 2 Tables: Contractors &
Services.

Table 1 - Contractors

ContractorID (Autonumber)
ContractorName (Text)
ContractorAddress (Text)

Table 2 - Services

ServiceID (AutoNumber)
ServiceValue (Text)
ContractorID(Text)

I create a relationship in Access from Contractors
(contractorID) to
Services (contractorID).

I populate both tables with sample data, making sure that
the contractorID
in the Services table corresponds with valid ContractorID
in the Contractor
table.

Now when I open the Contractor table in dataview, I click
the + sign next to
a record to view the contractors related services and I
get the following
error:

"This expression is typed incorrectly, or it is too
complex to be evaluated.
For example, a numeric expression may contain too many
complicated elements.
Try simplifying the expression by assigning parts of the
expression to
variables. (Error 3071)"

Am I committing some sort of database crime without even
realizing it?
I should add that I spoke with Microsoft support last
evening and they told
me that my Services table should have 2 Primary Keys
(serviceID &
contractorID). I never heard of doing that before.

Thanks!

 

[Jeff Boyce]

Kevin

In the real world (as real as database folks get), you probably need three
tables. Unless you have a "sole source" situation, a Service could be
offered by more than one Contractor ... and unless your Contractors are so
specialized they can only do one thing, each Contractor could offer many
Services.

This may be a many-to-many situation. To resolve it, you need a
tblContractor, tblService, and trelContractorService.

The first two tables have information ONLY about the topic, while the third
holds pairs of IDs from the first two, to indicate which Contractor provides
which Service (one row per valid combination).

Good luck!

(By the way, you probably DON'T want to be working directly in the tables,
using the "+" sign. Work from forms, where you have much better control
over events.)

Jeff Boyce
<Access MVP>

 

[Kevin]

Jeff:

Thanks for thr great response. I do appreciate it.

Mind if I compliuate the situation a little?

I originally wanted to have three tables in this
relationship: Contractors, ParentServiceCategories,
&ChildServiceCategories.

An example of ParentService woudl be "Interiors" and
Child for this woudl be " painting, counter tops,
bathroom remodeling, etc.".

How would this affect your proposed 3 table solution?

Thanks,
Kevin

Team Macromedia

 

[Henry]

You have a DB design normalization problem.
First, take a look at what you purpose is for creating
the DB. Make sure you have defined, in detail, all the
resulting outputs you want the DB to accomplish. Now, do
all your tables store all the data rquired to accomplish
the output? How do you get the data into your defined
tables? Are the tables normalized? What relationships
do these tables have? Normalization techniques will help
you achieve correst relations between tables. It appears
you are jumping into the deep end of the pool without
considering the consequences. DB design follows a set
procedure that will lead you to success. Invest in a set
of books, "Access 2000 Developer's Handbook", author Ken
Getz, published by SYBEX. In this two volume set you
will find most all of your answers from DB design to SQL
to Forms design, to VBA coding, to automation.

Wish you luck
Henry

 

[Scott McDaniel]

Not sure if this is a typo, but Contractors.ContractorID is a Long value,
and Services.ContractorID is a Text value ... I assume this is a typo, since
Access wouldn't have allowed you to make the relationship ...

However, as someone else said, you need a third table:

tblContractors
----------------
lngContractorID
strName
etc

tblServices
-----------
lngServicesID
strService
etc

tblContractor_Services
----------------------
lngContractorID
lngServicesID
etc


Note that in the last table, the PK is a combination of lngContractorID and
lngServicesID ... this ensures that a Contractor can only be listed as
supplying a particular service one time (although a contractor can provide
many different services).

 

[Tony Wainwright]

If Contractors.ContractorID is an Autonumber then Services.ContractorID
should be data number(long) not text