By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,939 Members | 1,623 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,939 IT Pros & Developers. It's quick & easy.

problem with relationship between tables

P: n/a
Hi All,

I have a db with three tables which have a relationship.
The problem is that when I add a record to the ForAction table it
will automatically add a record to the Originator table but not to
the RFI table.

I do not want access to add a record to the Originator table but
only to the ForAction table. Is this possible?

RFI ForAction Originator
ID -1------00- RFIrefnr
BronID --00-----1- BronID
Originator
ID, RFIrefnr and BronID are numbers. Originator is text.

Regards
Marco
The Netherlands
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
If you leave BronID as Null in the ForAction table, it shouldn't add a
record to Originator.
"Krechting" <m.*********@chello.nl> wrote in message
news:a7*************************@posting.google.co m...
Hi All,

I have a db with three tables which have a relationship.
The problem is that when I add a record to the ForAction table it
will automatically add a record to the Originator table but not to
the RFI table.

I do not want access to add a record to the Originator table but
only to the ForAction table. Is this possible?

RFI ForAction Originator
ID -1------00- RFIrefnr
BronID --00-----1- BronID
Originator
ID, RFIrefnr and BronID are numbers. Originator is text.

Regards
Marco
The Netherlands

Nov 13 '05 #2

P: n/a
Yes,
but the whole idea behind it is that I want to link an Originator thru the
ForAction table to a certain
RFI. The RFI is the main table. Several Originators get the task to perform
an action which is
identified thru ForAction. I just don't want new records in the Originator
table. Like this:

RFI 001 ------------- 001 - 03 ---------------- Max
RFI 001 ------------- 001 - 07 ---------------- Sam
RFI 001 ------------- 001 - 04 ---------------- Zachery

etc...

Marco

"MacDermott" <ma********@nospam.com> wrote in message
news:tR****************@newsread2.news.atl.earthli nk.net...
If you leave BronID as Null in the ForAction table, it shouldn't add a
record to Originator.
"Krechting" <m.*********@chello.nl> wrote in message
news:a7*************************@posting.google.co m...
Hi All,

I have a db with three tables which have a relationship.
The problem is that when I add a record to the ForAction table it
will automatically add a record to the Originator table but not to
the RFI table.

I do not want access to add a record to the Originator table but
only to the ForAction table. Is this possible?

RFI ForAction Originator
ID -1------00- RFIrefnr
BronID --00-----1- BronID
Originator
ID, RFIrefnr and BronID are numbers. Originator is text.

Regards
Marco
The Netherlands


Nov 13 '05 #3

P: n/a
I'm afraid I don't understand completely.

Each record in your ForAction table links an RFI to an Originator.
It includes a BronID field.
If the BronID field is left Null, it doesn't refer to an Originator (until
you specify one).
If it contains a BronID from your Originator table, no new record should be
created there.
If it conatains a BronID which is not in your Originator table, and
referential integrity is enforced with cascade updates, this could create a
new Originator.

The only thing that comes to mind which would cause a new BronID to be
assigned to each new record would be if this is defined as an Autonumber.
Could that be the case? (That would make it impossible to assign several
RFIs to the same Originator, too.)

HTH
"Marco Krechting" <ma************@zonnet.nl> wrote in message
news:da***************************@news1.zonnet.nl ...
Yes,
but the whole idea behind it is that I want to link an Originator thru the
ForAction table to a certain
RFI. The RFI is the main table. Several Originators get the task to perform an action which is
identified thru ForAction. I just don't want new records in the Originator
table. Like this:

RFI 001 ------------- 001 - 03 ---------------- Max
RFI 001 ------------- 001 - 07 ---------------- Sam
RFI 001 ------------- 001 - 04 ---------------- Zachery

etc...

Marco

"MacDermott" <ma********@nospam.com> wrote in message
news:tR****************@newsread2.news.atl.earthli nk.net...
If you leave BronID as Null in the ForAction table, it shouldn't add a
record to Originator.
"Krechting" <m.*********@chello.nl> wrote in message
news:a7*************************@posting.google.co m...
Hi All,

I have a db with three tables which have a relationship.
The problem is that when I add a record to the ForAction table it
will automatically add a record to the Originator table but not to
the RFI table.

I do not want access to add a record to the Originator table but
only to the ForAction table. Is this possible?

RFI ForAction Originator
ID -1------00- RFIrefnr
BronID --00-----1- BronID
Originator
ID, RFIrefnr and BronID are numbers. Originator is text.

Regards
Marco
The Netherlands



Nov 13 '05 #4

P: n/a
OK,

I will try to explain what I need.
I have three tables in my database. They are called RFI, ForAction and
Originator. RFI is the master table. It is filled with lets say
orders.
Originator is a table with names of costumers. ForAction is the table
that will have all the relations between RFI and Originator.

So I want to link several Originators to a record from the RFI table.
For example:
RFI 001 ----------- Johnson
----------- Peters
----------- Rice etc......

So every rec in RFI can have more than one Originator. The ForAction
table is there to store this data. Like the RFI.RFIrefnr, the
Originator.OriginatorID, the ForAction.DateCreating and a
ForAction.reply.

I have a form RFI form, from which I call a subform with a listbox
that holds
all the Originators so I can multiple Originators. The subform uses a
query.

Should I create relations in Access or only in the query and how
should it
look like?

Hope this helps

Marco


"MacDermott" <ma********@nospam.com> wrote in message news:<bt****************@newsread3.news.atl.earthl ink.net>...
I'm afraid I don't understand completely.

Each record in your ForAction table links an RFI to an Originator.
It includes a BronID field.
If the BronID field is left Null, it doesn't refer to an Originator (until
you specify one).
If it contains a BronID from your Originator table, no new record should be
created there.
If it conatains a BronID which is not in your Originator table, and
referential integrity is enforced with cascade updates, this could create a
new Originator.

The only thing that comes to mind which would cause a new BronID to be
assigned to each new record would be if this is defined as an Autonumber.
Could that be the case? (That would make it impossible to assign several
RFIs to the same Originator, too.)

HTH
"Marco Krechting" <ma************@zonnet.nl> wrote in message
news:da***************************@news1.zonnet.nl ...
Yes,
but the whole idea behind it is that I want to link an Originator thru the
ForAction table to a certain
RFI. The RFI is the main table. Several Originators get the task to

perform
an action which is
identified thru ForAction. I just don't want new records in the Originator
table. Like this:

RFI 001 ------------- 001 - 03 ---------------- Max
RFI 001 ------------- 001 - 07 ---------------- Sam
RFI 001 ------------- 001 - 04 ---------------- Zachery

etc...

Marco

"MacDermott" <ma********@nospam.com> wrote in message
news:tR****************@newsread2.news.atl.earthli nk.net...
If you leave BronID as Null in the ForAction table, it shouldn't add a
record to Originator.
"Krechting" <m.*********@chello.nl> wrote in message
news:a7*************************@posting.google.co m...
> Hi All,
>
> I have a db with three tables which have a relationship.
> The problem is that when I add a record to the ForAction table it
> will automatically add a record to the Originator table but not to
> the RFI table.
>
> I do not want access to add a record to the Originator table but
> only to the ForAction table. Is this possible?
>
> RFI ForAction Originator
> ID -1------00- RFIrefnr
> BronID --00-----1- BronID
> Originator
>
>
> ID, RFIrefnr and BronID are numbers. Originator is text.
>
> Regards
> Marco
> The Netherlands


Nov 13 '05 #5

P: n/a
Define your relationships in the Relationships window, not just in the
query.
There should be 2 one-to-many relationships, one from RFI to ForAction on
your RFIRefNr, and one from Originator to ForAction on your OriginatorID.
Enforce referential integrity with cascade updates. Deletes are up to you.

Please post the SQL for the query behind your subform.

You say the main form "calls" a subform.
If the "subform" isn't embedded in the main form, it's not what's usually
called a subform, but could be called a "secondary form." I'd recommend
embedding it, however, as Access makes this configuration especially
convenient to use.

HTH
- Turtle

"Krechting" <m.*********@chello.nl> wrote in message
news:a7**************************@posting.google.c om...
OK,

I will try to explain what I need.
I have three tables in my database. They are called RFI, ForAction and
Originator. RFI is the master table. It is filled with lets say
orders.
Originator is a table with names of costumers. ForAction is the table
that will have all the relations between RFI and Originator.

So I want to link several Originators to a record from the RFI table.
For example:
RFI 001 ----------- Johnson
----------- Peters
----------- Rice etc......

So every rec in RFI can have more than one Originator. The ForAction
table is there to store this data. Like the RFI.RFIrefnr, the
Originator.OriginatorID, the ForAction.DateCreating and a
ForAction.reply.

I have a form RFI form, from which I call a subform with a listbox
that holds
all the Originators so I can multiple Originators. The subform uses a
query.

Should I create relations in Access or only in the query and how
should it
look like?

Hope this helps

Marco


"MacDermott" <ma********@nospam.com> wrote in message

news:<bt****************@newsread3.news.atl.earthl ink.net>...
I'm afraid I don't understand completely.

Each record in your ForAction table links an RFI to an Originator.
It includes a BronID field.
If the BronID field is left Null, it doesn't refer to an Originator (until you specify one).
If it contains a BronID from your Originator table, no new record should be created there.
If it conatains a BronID which is not in your Originator table, and
referential integrity is enforced with cascade updates, this could create a new Originator.

The only thing that comes to mind which would cause a new BronID to be
assigned to each new record would be if this is defined as an Autonumber. Could that be the case? (That would make it impossible to assign several RFIs to the same Originator, too.)

HTH
"Marco Krechting" <ma************@zonnet.nl> wrote in message
news:da***************************@news1.zonnet.nl ...
Yes,
but the whole idea behind it is that I want to link an Originator thru the ForAction table to a certain
RFI. The RFI is the main table. Several Originators get the task to

perform
an action which is
identified thru ForAction. I just don't want new records in the Originator table. Like this:

RFI 001 ------------- 001 - 03 ---------------- Max
RFI 001 ------------- 001 - 07 ---------------- Sam
RFI 001 ------------- 001 - 04 ---------------- Zachery

etc...

Marco

"MacDermott" <ma********@nospam.com> wrote in message
news:tR****************@newsread2.news.atl.earthli nk.net...
> If you leave BronID as Null in the ForAction table, it shouldn't add a > record to Originator.
>
>
> "Krechting" <m.*********@chello.nl> wrote in message
> news:a7*************************@posting.google.co m...
>> Hi All,
>>
>> I have a db with three tables which have a relationship.
>> The problem is that when I add a record to the ForAction table it
>> will automatically add a record to the Originator table but not to
>> the RFI table.
>>
>> I do not want access to add a record to the Originator table but
>> only to the ForAction table. Is this possible?
>>
>> RFI ForAction Originator
>> ID -1------00- RFIrefnr
>> BronID --00-----1- BronID
>> Originator
>>
>>
>> ID, RFIrefnr and BronID are numbers. Originator is text.
>>
>> Regards
>> Marco
>> The Netherlands
>
>

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.