How to manually select relationship type in Access 2007

[Beepa]

I am setting up a db I have a contracts table and a main table in the main
table the contracts are associated with an ID from another company. Each
contractID in the contracts table would appear once in the main table as
well. But when I try to set up a relationship between the two tables Access
2007 sets it as a one to many relationship instead of a one to one? I have
not found a way to change this and was wondering if someone might know of a
way to manually select the relationship type. So far I have not found this to
be possible?

Thank you for your time,

 

[Tom Wickerath]

Hi Beepa,

To create a one-to-one relationship, the field from each table must have a
unique index. For example, you can create a 1:1 relationship between two
primary key fields (a primary key field is indexed uniquely, by definition)
as long as they are compatible data types. If one of the fields is an
autonumber, then the other field must be a long integer--you cannot join two
autonumber fields and expect to have something that works.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Beepa]

Thank you Tom for you fast response. I did understand that. I was using
contractID in one table as a primary key and contractID in another table
where it wasn't the primary key but i had the indexing set to no duplicates.
I thought this would allow me to create a one to one relationship since no
duplicates were allowed in both instances? But it didn't work. So I wondered
if there was a way to force or manually set the relationship since I knew
there would not be duplicates in either table?
So far the only thing I can see to do is change my primary key in the one
table to the same key. (The person I'm doing this for didn't want it that way
hence my efforts here. )

Thank you again for your help.

 

[Tom Wickerath]

Hi Beepa,

I was using
contractID in one table as a primary key and contractID in another table
where it wasn't the primary key but i had the indexing set to no duplicates.
I thought this would allow me to create a one to one relationship since no
duplicates were allowed in both instances? But it didn't work.

This should work. In fact, it should not allow you to create a one-to-many
relationship. I haven't specifically tested this in Access 2007, but I'm
willing to fire up 2007 and do a quickie test if necessary.

So I wondered
if there was a way to force or manually set the relationship since I knew
there would not be duplicates in either table?

There is no way of manually forcing a relationship type.

Are you able to share a copy of your database? You can send a copy with
empty tables, for just the two tables involved. If you are interested, send
me a private e-mail message with a valid reply-to address. My e-mail address
is available at the bottom of the contributor's page indicated below. Please
do not post your e-mail address (or mine) to a newsgroup reply. Doing so will
only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Beepa]

Well I feel terribly embarassed because as I went to clear the data and put
the tables in the state they were when I was trying to do this, so I could
mail them to you; it now seems I can't duplicate the problem now and it is
working as it should.
So most likely I had something not set as I had thought it to be?

I do have one other isssue at the moment if you are interested and have the
time. It is in building a query related to these tables. I would be glad to
send them still so you could see first hand my dilemma.

I am working on a query in which I have a field called signedWeek. It is a
numerical field with the week the data is entered using the form. When I run
my query I need to count the number of contracts signed in the previous week
and the number of contracts signed in the current week. I haven't yet added
the second signedWeek column as I was going one step at a time and trying
first to get the query to count the contracts in the previous week. I figured
I have to enter that column in my query twice once with the criteria:
signecWeek -1 and then for the current. I may be making this too hard it's
been awhile since I worked in Access. I am trying to make a query to figure
payroll that is dependent on how many contracts were signed in the 2 week
period ending in the current payweek. There are different pay rates for
various ranges of the count of contracts signed in the two week period.
Example so if a person got 4 signed contracts previous week and 3 signed
contracts this week the pay for the 3 this week is determined on a scale like
this 1 -3 $100 4 - 7 $150 8 - 12 $200. so after determining the count of
contracts in each week I can go on to the calculated fields I need to figure
the pay.

I apologize if this is way too much of an intrusion. I am not asking for a
complete solution to my query just wanted you to understand what I'm trying
to accomplish so you might be able to assist me in how to get the counts I
want from the signedWeek field.

Thank you for all the time you have spent on my behalf.

 

[Tom Wickerath]

Hi Beepa,

I'm glad to read that you got the relationship working properly.

If you can convert your database to the 2000 or 2002/2003 .mdb format, I'm
willing to take a look at your second question. I honestly have not used
Access 2007 much at all, and when I attempt to do so, I feel like a fish out
of water.

I'm just leaving now to drive into Seattle, to return a puppy to my sister
(I've been puppy-sitting for her), so I'll be off-line for the next couple of
hours.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________