473,326 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

problem with relationship between tables

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
5 1449
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: middletree | last post by:
Posted this to Access group, meant to do it here: I have what I call a composite table. Can't recall what they called it in database class, but it's where you take the PK of two different...
2
by: Keith | last post by:
I am having a problem creating a many-to-many-to-many type relationship. It works fine, but when I create a view to query it and test it, it does not generate the results I expected. Below...
8
by: Andante.in.Blue | last post by:
Hello, I have just inherited a legacy Access 97 database. While going through it, I noticed something strange... its Relationships window (the one accessed by Tools --> Relationships) is almost...
4
by: Mark Hanley | last post by:
I have found similar problems to mine on this and other newsgroups but I still haven't been able to solve my problem... I have two tables 'Pupil' and 'SEN' which are related on a field called...
6
by: David Allison | last post by:
Relationship problem ~ How to tell Owner his db is crap. His db was made in 1999 - 12 tables - none normalised - none with relationships.(YES REALLY) He only knows about inputing data - 22,000...
2
by: DataB | last post by:
Hi everyone! I have a forms problem. Bakground: I have created a number of tables. Of these, I have a main parent table (Personal Details) and a number of other child tables (Tax file No.,...
8
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record...
7
by: Ron | last post by:
Hi All, Using Access2000, winXP. Table 1 = tblClients displayed on frmClients via qryClients. 2nd table = tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails shown on...
3
by: shawnews | last post by:
Ok...I'll first describe briefly what I've done. Working from a paper form with over 200 fields - broken into 10 areas, I created a database with 10 tables. I then created a form using those 10...
6
by: NicoleCartrette | last post by:
Going back to school is easier said than done.. This was posted to an older thread earlier but I don't think it got any attention. Your help is appreciated Professor requires we create a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.