can people help me how to define table relationship
i need some example to understand but make sure it's easy to understand
coz my knowledge level still low in access
3  2872  PEB 1,418
Recognized Expert Top Contributor
You have table students and table students notes...
You have student ID in the 2 tables...
In Students each ID is unique
But in students notes it is indexed with dublicates... Because one student can have more than 1 note...
So the relation is ID_student with Id student from the 2 tables The relation is one to many!
To create the relation choose Toosl relationships, choose your tables and drag between the fields that you want to be in relation...
MMcCarthy 14,534
Recognized Expert Moderator MVP
To use the Customer and Invoice example. Underlined fields are the Primary keys and fields followed by (FK) are the foreign keys. Foreign keys are fields in a table that relate to the primary keys of other tables.
A customer can be issued with many invoices. Therefore the relationship is one to many. The foreign key goes in the many table.
tblCustomer {CustomerID, CustomerName, Address, Phone}
tblInvoices {InvoiceNo, CustomerID(FK), InvoiceDate, Amount}
I have got a list of individuals and a list of addresses. One individual may have more than one address, and one address may house more than one individual.
What is the best way of defining that relationship ?
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: toedipper |
last post by:
Hello,
I am designing a table of vehicle types, nothing special, just a list of
unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor
etc etc
For the table design I am proposing a single column table with a field name
called vehicle_type and this will contain the vehicle type.
Sot it will be
|
by: John |
last post by:
I'm trying to normalize my database the best I can this time around and I'm
running into some trouble. I've been avoid normalizing for a long time now
because while I know how to normalize, I'm weak on SQL strings so populating
the database is a pain in the kee... but this is another story...
ok, first of all is this a many to many or no?
projMain (table)
projID (PK)
clientID (FK)
|
by: Robert Stearns |
last post by:
I have a pair of tables which have foreign key dependencies on each
other. Is there any better way to define them than to create both of
them then, after both are defined, 'alter add' the constraints? I have
basic distrust of separating the table creations from the constraint
definitions, probably irrational.
|
by: Larry R Harrison Jr |
last post by:
I have an Access 97 database with 2 tables that have a one-many
relationship. I have a SQL statement in the "one" table which I want to
execute and insert 7 records into the "many" table, and I want them to be
linked to the "main" table.
Problem is, it won't let me run the SQL run as long as the one-many
relationship is established in the relationship window.
The SQL is:
|
by: Sami |
last post by:
Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.
I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields
under each. The common field will be the StudentID field, which is
their student number assigned...
| | |
by: PAUL |
last post by:
Hello,
I have 2 tables with a relationship set up in the dataset with vb
..net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I do the update the changed
parentid in the child table fails to change. No error is given its just that
the change is not written to the Database.
When I step through the records for the child table the one I would expect
to be changed has a row...
|
by: ChasW |
last post by:
I have a database with multiple tables. I want to query all tables
that have a match for a specified field value. Each of the tables may
have duplicate values for this field.
From what I have read, I need to define a many to many relationship,
but I am not sure how to go about doing this.
example:
4 tables with the same structure
ID (primary key)
|
by: MMcCarthy |
last post by:
Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form) and BCNF (Boyce-Codd Normal Form). There are others but they are rarely if ever used. A database is said to be Normalised if it is in 3NF (or ideally in BCNF). These steps are descibed as follows:
Note: When attribute...
|
by: BD |
last post by:
Hi, all.
I need to enforce a one-to-many relationship on 2 tables, with a join
table.
Say the join table contains account information. It has cust_no and
acct_no. Both cust_no and acct_no are child FKs to other tables (no
nulls will exist in these columns).
Logically, one customer can have several accounts, but one account can
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
