Q: One to Many

G

Geoff Jones

Hi

I was wondering if anybody could help me with the following:

I have imported an excel file into Access 2003. The file was made up of two
worksheets. As a result I have two tables in my database. Both tables have
fields with common entries i.e. there is a field in Table 1 and a field in
Table 2 with have common numbers (for the sake of argument, 1, 2 and 3). In
addition, no other type of number exists in the fields.

I started to create a relationship between these two tables. My thinking
being that there will be records in Table 2 which match up with the value of
a field in Table 1 e.g. I'd like to find all the records in Table 2 which
have the value 3 for the common fields (which I've called VALUE).

When I used Tools->Relationships from the menu and dargged the VALUE entry
of Table 1 to Table 2, I was shown a relationship but it wasn't a
One-To-Many. Instead, it was a Indeterminate.

Is this because I imported the data? Is there a way to say that it is a
One-To-Many relationship?

Thanks in advance

Geoff
 
B

Brendan Reynolds

You need to add a unique index to the field, in the table on the one side of
the relationship.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
G

Geoff Jones

Hi Brendan

Many thanks. That has got me further. Unfortunately, it didn't quite get
there i.e. it now shows One-To-Many in the dialog box but the 1 and infinity
signs don't appear on the pictorial representation. Any ideas?

Geoff
 
B

Brendan Reynolds

Access does not display those symbols if you don't check the 'Enforce
Referential Integrity' check box in the 'Edit Relationships' dialog box.

It's a mystery to me why the designers chose not to check that check box by
default, as I can see no purpose in a relationship that doesn't enforce
referential integrity, but that's the way it is.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Top