Tables :: Same Primary Key In One-to-one Relationships?

Aug 20, 2013

I have mapped everything out and built it in Access (but haven't entered any data yet). My question relates to structure. The data is based on artifacts, each piece is recorded with an automatically generated primary key in the main table. There are a number of features (type, decoration etc.) that are documented in related tables (included in/linked to the main table with foreign keys). Aside from these (the features I will query on), each piece has a number of characteristics to be recorded (size, colour etc.). Because of form/table size, I have broken the information into three tables (a main table with ID details, a second with Sizing and Dimensions, and a third with Materials etc.).

My question is: Can the main ID primary key from the main ID table also be used as the primary key in the 2 other basic information tables, with these 2 linked to the main table in one-to-one relationships (so it's an auto-number in the main table and an integer in the other two). This to me would be much easier to navigate than to have one long giant table with all these basic details.

I have created a main data entry form with a "navigation form". 5 tabs/pages. Tab/Page 1 = main ID. 2 and 3 are the two broken apart tables mentioned above. 4 and 5 are forms based on related tables that have their own subforms included. Both 4 and 5 are based on tables in which the main ID primary key is entered as a foreign key (in one-to-many relationships with the main table). Since this was done with a navigation form (instead of adding each of the 4 as subforms), will it still allow me to enter all relevant details for one piece into each tab/page, and save it all together automatically (whenever I enter a new piece into the main ID form)? Will the primary key be automatically generated throughout, or do I have to enter it manually in each tab/page?

View Replies


ADVERTISEMENT

Relationships/primary Key

Jan 22, 2007

I hope you guys can help me out, as 'Step by step Access' and the internet didnt help me out...

I'm about to create a database for monitoring assessment. Several patients will be assessed many a time. The main table will be one with a patientnumer and patient characteristics. For each period in time (T) I'd like to create tables which contain the different tests and dates on which they are administered and processed.

The problem I'm having though, is that the tables T don't have unique values and they need to be related to the patientnumbers.
Should I use autonumbering as primary keys in the tables T to relate to the patientnumber? I'd rather have the patientnumbers automatically appear in the tables T, or at least in the forms to be used by the final users of this database.

I hope this description makes some sense (English is not my primary language)

Can anyone help me out?

View 9 Replies View Related

Two One To Many Relationships From 1 Primary Key

Jan 8, 2008

Hi,

I have a table with a primary key (Sales Number) related to lower table with one to many to Transaction Number

The Transaction Number will contain many products so is related to a table fpr products via one to many.

BUT - also transaction number is also related to another table via a one to many relationship for Invoices for which there could be many.

Now I have set up all relationships and the tables look to be correct.

But how do I get the transaction number to flow down the 2 relationships.

I can only at this stage link either Invoices OR products via the transaction number (prim key).

How can I set it up so that when I update / enter data into the transaction table, the foriegn keys in both INVOICES and PRODUCTS Table?

I need to set this up in a form too?

Can anyone help?

View 1 Replies View Related

Lost Relationships & Primary Keys

Jun 6, 2007

I'm having lots of erratic problems with our Access database. Back end tables on the server, front ends on workstations. Running Windows 2000 server, Access 2003 SP2 on Windows XP SP2. I'm not the network administrator so don't have much detail on network. For instance, the back end is 'losing' relationships between tables, with the primary key on the 'one' end of a 'many' relationship disappearing - the relationship link between the tables also disappears. Other errors are:- erratic problems with indexes which are sometimes fixed with a compact / repair, sometimes not- data on forms disappearing- data on forms appearing in the wrong field- queries that ask for parameter input twice when only programmed to so once- sorting orders suddenly going wrong way round - inability delete a record via a form, caused Access to completely crash with Microsofts 'sorry unrecoverable error' message. To fix the query problem, I created a blank query and imported the old one into it, problem solved. But what caused the old query to act weird in the first place?To fix the inability to delete a record problem, I created a whole new blank database and imported everything into it. But what caused the database to repeatedly crash? The suddenly missing relationships I have no cure for. I'm at the end of my tether - can't figure out any pattern to this. Any suggestions? Network errors? Read / write on server? Disk space / fragmentation? Fundamental problem with the database?Any help much appreciated.Sorry if this post should have been in the tables forum.

View 6 Replies View Related

Tables :: Keep Primary Key Consistent As Data Saved In Other Tables Will Use Field

May 1, 2014

i have 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.

I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.

View 2 Replies View Related

Tables :: Setting Up Multiple Tables / Relationships

Dec 10, 2013

I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).

The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.

I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.

View 3 Replies View Related

Help With Tables Relationships

Nov 19, 2004

CompanyID pk (just one company)
CompName

EmployeeID pk
companyID fk
roomID fk
extensionID fk
LName
FName

LocationID pk
RoomNumber (many employees might share same room)

PhoneDirectoryID pk
ExtNumber (employees might share same extension number)
roomID fk

ItemID pk
ItmName (messengers take envelopes to different employees)
equipmentID fk
employeeID fk

EquipmentID PK
eqmtName (equipment might be used many times to deliver jobs)

I just need to know if the relationships for these tables are right.

If you need more information about this, please let me know.

View 9 Replies View Related

Relationships Between Several Tables!?!

Nov 30, 2004

I am converting / developing a database that stores information pertaining to individual birds and their recaptures over many years. Here is a condensed version of the many tables in this database:

tblIndividual Bird:
Autonumber (Primary Key)
Band Number - also, unique to the individual bird
Sex - M or F
etc ..

tblCaptureInformation:
Autonumber (Primary Key)
Band Number - look-up from tblIndividualBird (using hidden Primary Key)
Capture #- # which indicates what capture this is (ex. Intial capture - 1)
Place
Age
Date
etc ...

Each time a bird is captured, we record information pertaining to TIME, MEASUREMENTS, and NEST INFO. So, I have seperated the data based on these headings and made them into individual tables.

Now, my problem .... I have already created a relationship between CaptureInformation and Individual Bird. However, in the last 3 tables I would like to create a drop-down menu which shows the Band Number and Capture Number and make relationships there. What is the easiest way to do this? As of now, when I make a look-up field in the last 3 databases to show this info, the Band Number comes up with the Autonumber (because I am using the CaptureInfo table) which does not really help someone entering the data. Thanks for your help.

View 4 Replies View Related

Tables/Relationships

Nov 30, 2004

Hi,

I am trying to create a db for service orders for customers. At the moment I have four tables, customer, service_order, parts and totals.

I have one form for customer records that has a button that when clicked opens another form for that customer's service orders. The service order form has two subforms, one for parts and one for totals.

When I try to add a new service order for my test customer it says "you cannot add or change a record because a related record is required in the table 'customer'.

As you can see here (http://www.abstractmusic.org/relationships.gif) I have three relationships setup. cust_no in customer table is a PK and so is service_order_no in service_order table.

Also I am having problems with the totals, as the fields are from different tables the equations won't work from within the subform (I guess I need some kind of query). I need the totals in a seperate table other wise I have a total for every part entry.

Any help would MUCH appreciated.

Cheers

Housey

View 6 Replies View Related

Are My Tables And Relationships Set Up Right?

Mar 29, 2006

The attached application is what I need to design a form in Access around. Please see if i set up the tables correctly and the relationships. Thanks.

View 1 Replies View Related

What Relationships / Tables Should I Set Up

Sep 6, 2007

I am trying to set up a database to detail dances published in a magazine over the years.

I currently have all the information in an Excel Spreadsheet but know that Access would be better.

The columns in my spreadsheet are:

Dance
Choreographer(s)
Level
Count
Date Published
Song 1
Artist 1
Count In 1
Song 2
Artist 2
Count In 2
Song 3
Artist 3
Count In 3
Song 4
Artist 4
Count In 4
Song 5
Artist 5
Count In 5
Song 6
Artist 6
Count In 6
Song 7
Artist 7
Count In 7

There can be two or more dances with the same name
The same choreographer(s) could have written more than one dance
The same count can be used for many dances
About 15 dances are published on the same date
One artist can have more than one song used
One song can have more than one artist singing it
One song and relevant artist can be used for more than one dance

I tried using Access For Dummies but it has confused me even more. I cannot work out what tables there should be and what relationships.

Not all dances have 7 songs for it - some have 1, some 2, some 3, etc.

What is listed as song 4 for one dance could be song 1 for another or song 5, etc.

Any advice gratefully received!

Thanks in advance

Chris

View 14 Replies View Related

Tables And Relationships

Jan 31, 2008

I'm a novice and I'm confused. Maybe it's the way I think. I feel like there is an easier way that I'm overlooking, but I can't seem to get a satisfactory solution.How would YOU create your tables/relationships if you had the following:* The general purpose is to manage orders* You have to store information about the order (like order number, date)* You have to store information about from what company the order is from (like address)* You have to store information about from which department of that company the order comes from, each department has their own information that needs to be stored (like contact person).Keep in mind that you don't want to memorize which department is from which company nor do you want to be able to make the mistake of entering an order from a department that is not a part of that company.It seems like it should be an easy thing to do, but I'm stumped. I've thought about creating a new table for every company with a sub table for every department but that doesn't seem very practical. I tried creating one table called Company and one called Department, then merging them on a third table which is then linked to a fourth table called Orders. I'm not convinced this is the best way to do it, but it's my best guess at this moment.All help is greatly appreciated.

View 1 Replies View Related

Relationships Between Tables

Feb 10, 2008

I'm relatively new to Access so am unsure if i'm on the right lines with my system, but here goes:

The system should be able to have new records of students input, and their grades recorded.

At present I have it laid out as follows:

-tbl Pupil
--Pupil ID (pk)
--Surname
--Forename
--Year
--Address
--Phone Number
--Parent's e-mail

-tbl Present Grades
--Pupil ID
--Grade ID (pk)
--Subject
--Term
--Grade

-tbl Subjects
--Subject ID
--Name



Any advice on relationships between the tables would be appreciated. Thanks in advance.

View 1 Replies View Related

Tables With No Relationships

Oct 31, 2006

i know tecnically you can create a table with no relationships but is it "ok" to do so?

im using a table to store some values which are only referenced through a query but it is completly detatched and has no relationships with any other tables, im awear my database will function perfectlly happily but is it an acceptable programming standard?

cheers guys

mike

View 4 Replies View Related

Tables :: Multiple One-to-many Relationships Within Two Tables?

May 18, 2013

I am creating a database of medieval labor contracts and have come across an issue.

I have a table of Contracts, and a second table of People. I want the table of People to show every contract in which that person appears. Each contract has multiple roles - there is always at least a Laborer and an Employer.

The same person might appear as a laborer in one contract, and an employer in a second contract and I want my People table to pull every contract in which that person appears, regardless of the role they play in the contract.

So far I have not been able to get this to work. I set up two different one-to-many relationships which link the People table primary key (personID) to two separate columns in the contract table. However, in the People table, instead of pulling contracts in which the person appears as either Laborer or Employer, it will only pull contracts in which the person appears as both Laborer AND employer (a situation which will never occur in my actual data but which I tried out as a test).

View 2 Replies View Related

Tables :: Relationships Between Linked Tables

May 3, 2013

I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management.

So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:

Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.

So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:

But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result.I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:

Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed: Patch_key control source to Patch_ID and Device_key control source to UNID (which is in the cyber assets table)

As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.

View 7 Replies View Related

Tables :: Two Tables / Multiple Relationships Possible?

Oct 4, 2013

I have three fields in one table that need to be related to the PK of another table.

tblProject - Engineer_ID, Producer_ID, and Project_Maner_ID
tblEmployee - Employee_ID (PK)

employees can take on any of the positions for a given project, so i'll need to have multiple employees filling up different roles for each project.

when i try to set up the relationships i get the following message:

A relationship already exists.

Do you want to edit the existing relationship? To create a new relationship, click No.

I click No, and it creates a table named tblEmployee_1. Why? is this ok?

View 2 Replies View Related

Tables :: Number Of Tables And Relationships

Jun 27, 2014

I want to build a Financial Database. We are provided a certain amount of budget under different heads each year. Every month we spend some money from some or all heads. Then we provide a detail of expenditure during the month under each head and the balance thereof. My request is how many tables I need in my database. My opinion is 5 tables each for Years, Months, Heads of Expenditure,Budget Allotted, and Expenditure.

View 1 Replies View Related

Relationships With Linked Tables

Jun 19, 2006

I have a database with a table on a shared network drive. The table contains a list of buildings, building details and a unique building code. I want to be able to use that database as a master copy so any new buildings that need to be added can be.
I have another database with accounts and another database with some other information. I can create a relationship between the accounts and the linked table of buildings (by the unique building code) but if I am to go into the building table, there is no "expansion option" to see all the accounts for that building. Is there a way to create a proper relationship or at least make a copy of the buildings table so that each time the database starts up it can get the latest version?

Thanks in advance,
Bob

View 2 Replies View Related

Import Tables That Have Relationships

Oct 14, 2004

Hello,

I know this has been asked a few times before, and I did search it, but could not find an adequate answer.

I want to use code (VBA) to import tables from a different Access database into the current one, by first deleting the current database tables, and then importing the new tables from the other database. I found code in the following thread that does exactly this:

http://www.access-programmers.co.uk/forums/showthread.php?t=74700&highlight=import+tables

However, the code in this example only appears to delete tables in the current database that have no relationships with each other before importing the outside tables. My tables, however, are rife with relationships and when i run this code, i get the message, "You cannot delete the table "tblWhatever", it is participating in one or more relationships." Can someone give me a pointer or two about how I can solve this problem (if at all) ?

Thanks in advance!

View 4 Replies View Related

A Bit Confused With Tables And Relationships

Aug 11, 2006

Hello, ive done a bit of access before but ive got myself a bit confused on my next step.

I have a database of books that i loan out. I have a table containing the books instock. I want to be able to loan out book s and reserve books. The problem is whats the best way to do this? Should i have a form that allows me to click a button that takes the book out of the books instock list and adds it to the reserved list? The problem i see with this is when all the books had either been loaned or reserved then the books instock list would be empty and nobody could reserve anymore books which would be a vital floor. Does anyone have any ideas?

Thanks,
Marley.

View 1 Replies View Related

Questions About Tables/relationships

Oct 6, 2006

My boss has assigned me the task of constructing what is basically a time tracking database. Employee info will include Employee ID, Name, Work Area, and Supervisor's name. Each day, employees will record the time they spend working on any of 40 different potential tasks for that day. The forty different tasks are split into five main categories, each with eight tasks. The boss wants each employee to be able to go into a form and/or subforms (haven't gotten that far yet!) and be able to record the amount of time they spent working on any given task for that particular day.

So, the tables would need to contain the Employee info mentioned above, the date, the 40 different potential tasks, and the time spent on each task for each day. I can't quite get my arms around how i should set up the tables, particularly where the date would go. Hope i was clear enough describing what I'm looking for. If not, let me know.

Thanks in advance for any suggestions!

View 3 Replies View Related

Building Relationships In Tables

Jun 19, 2007

Hey Guys

Great Forum and another newbie here

I have created an invoicing system for my business, as i was unhappy with MYOB.
Basically i have Product ID and Desciption in 1 table.
In another, called registry, this is where i input the data for the order.

What i basically want to do is?
When i type in the Product ID in the registry table, i want the description field to automatically appear in the cell next to it. As this would save a lot of time

Any help would be much appreciated

Thanks

Tarek

View 3 Replies View Related

Making Sure My Tables/relationships Are OK

Nov 13, 2007

Hello,

I'm creating a database for a home builder to track a lot of info, but mainly: house info, prospect info, actual customer info, and contract info.

My main tables are:
tblCustomers
CustID PK
(a bunch of customer fields)

tblHouses
HouseID PK
(a bunch of house fields)

tblContracts
ContractID PK
CustID FK
HouseID FK
(a bunch of contract fields)

The releationships are as follows:
One house can have many contracts
One customer can have many contracts

I thought I could use one table, tblCustomers, to track the prospect info and actual customer info, as so much of it will be identical (technically someone should be entered as a prospect before they can be a customer, and all info will be the same for both except for extra information fields for prospects, but I always want to be able to refer back to those fields) and I would know a prospect became an actual customer because at least one contract in the tblContracts would have their CustID associated with it.

Is it acceptable to have two foreign keys in tblContracts? And am I missing something that should exist between House and Customer? House and Customer are only releated when there is a Contract, so it is implied (I think) that a Customer can be associated with many different houses through many different contracts. Is that the right way to look at it?

I've come up against a wall regarding linking two forms so that the second form displays the data associated with what was showing on the first form. If I'm good here with the tables I suppose I'll post my detailed question in the forms area.

Thank you so much!:o
~Merissa

View 6 Replies View Related

Linked Tables And Relationships

Mar 19, 2008

Each employee has their own database containing a table of log in and out times.

The main database links to that table in each of the employess databases, a UNION query compiles all these tables.

Is it possible to have a create relationships with linked tables?

View 8 Replies View Related

Relationships With Multiple Tables

Aug 17, 2005

Hi

I need three tables with a relationship between their primary id columns.

The first two tables:

CREATE TABLE [Table2] ([id] INTEGER PRIMARY KEY);
CREATE TABLE [Table3] ([id] INTEGER PRIMARY KEY);

and then I need the last SQL query to create Table1 with column id related to the column id in table2 and table3.
As I understand the relationship must to set when creating the table...
so this is what I got:
CREATE TABLE [Table1] (ID INTEGER PRIMARY KEY CONSTRAINT Table1ID REFERENCES [Table2](ID) REFERENCES [Table3](ID));

This produces no errors but when checking in MS Access there are no relationships between them.

Any help with this is very appreciated!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved