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 Replies


ADVERTISEMENT

Setting Up Database - Need Help With Relationships Between Tables

Sep 9, 2007

Hi,

I would like to create a small HR database that holds:

* Employee details (Name, Date of birth, Entry Date, Exit Date, Function,Department, Shift)
* Departments
* Departments and capabilities needed
* Employee, departments and capability achieved (1=yes, 0=no)

The idea would be to have a link between the employee the departments capabilities I can tick fields of the capabilities they have or have achieved.

When I have this I could run a report that shows a score per employee
For example:

Name: Department: Orderpicking: Packing: Cutting:

John Warehouse 1 1 0

As the tasks / capabilities required are different per department you would see something different if you would have user Jane that works in accounting


Is there anyone that can give me some hints so that I can get any further??

For the moment I have 3 tables:

1. Employee information - John, Jane
2. Departments - Warehouse, Accounting
3. Department tasks - Department, Cap1, Cap2, Cap3, Cap4

View 7 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 :: 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

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

Problems With Multiple Relationships Over Multiple Tables - See Picture.

Jul 6, 2006

I have the following problem.

I have 3 tables made up of ID and NAME and other parameters.
NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.

Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.

see attached picture for the relationships.

http://www.members.iinet.net.au/~thydzik/temp_diag.jpg

View 5 Replies View Related

Data From Multiple Tables Without Relationships

Aug 15, 2006

K,

Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,

View 6 Replies View Related

How To Specify Relationships When Two Tables Are Linked Multiple Times

Nov 16, 2006

Hey all, i would appreciate some help with Access here.

I am an attorney, and i am trying to set up an access database for all of my cases.

I have one table in which i keep the names, contact information, etc of all the witnesses who i have used or will be using (especially expert or law enforcement witnesses who i will be using in multiple cases).

I also have a main table in which i have a record for each case. Because I have multiple witnesses for most of my cases, I have had to create multiple relationships between my main cases table and my witnesses table.

My question is this: How do I specify when setting up a report (or a form/query/etc) that when i ask for the witness's address i am looking for the address i made with a particular witness, specify that relationship between the two tables, as opposed to the other relationships between the same two tables.

I hope that makes sense, and i would very much appreciate if someone could give me instructions.

Thank you.

Jay B

View 1 Replies View Related

General :: Creating Multiple Relationships Between Two Tables

Aug 2, 2013

I'm trying to create multiple relationships between the same two tables, but I run into problems every time I try. I'm using Access 2007.

Specifics:
I have a table with information on meetings and there are two hosts. So I have fields Host 1 and Host 2. I have a second table that lists possible hosts and their personal information. I have a relationship between table 1 "Host 1" and the Host in table 2; I cannot create another working relationship between table 1 "Host 2" and the Host in table 2.

how I can get that to work? With just the one relationship, I can go to table 2 of the hosts, click on their name, and see all their meetings.But if I add another relationship, it takes out all of the information. I've been working on this for over an hour,

View 2 Replies View Related

Tables :: Multiple Many-to-many Relationships With Hierarchical Data

Aug 5, 2013

I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.

tblLangInfo: LangID (PK), language name, language family, etc.

tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in

tblSegmentLangJoin: LangID (PK), SegmentID(PK)

I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:

tblProcesses: ProcessID (PK), Process name

tblProcessLangJoin: LangID (PK), ProcessID (PK)

Here's the fun part...

I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:

Process1_triggers
Process1_targets
Process2_triggers
...

This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.

The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.

E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.

I've attached a picture to illustrate the relationship I need, since that's likely clearer.

One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.

View 3 Replies View Related

One Main Table With Multiple Tables Relationships?

Sep 14, 2014

I'm trying to create a database at work keep track of projects I'm working on and all the different events that happen during the project.

I want the main table to be customers, which will include the job number (as the primary key) name, address etc. Then I need other tables that have information about the building permit that will include dates city names etc and then another table that would include information on our sales people and so on, there would be maybe 5 tables that all connect back to the customer table.

I have tried this several times and keep running into problems, I'm sure it has something to do with the relationships. I'm missing something. I can create 2 tables and it works fine but once I add a 3rd it wont work.

View 4 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 :: 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

Append Query, Multiple Tables To Multiple Tables In Another Database

Nov 29, 2007

Can a Append Query move all my data stored in multiple tables to another database with a identical table structure?

Because as I try to work the query, I keep getting prompted to "Select a table" I want to append to, and I don't want to append to just one table...

View 4 Replies View Related

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Tables :: Export Multiple Tables Into Single MS Excel 2010 Workbook

Feb 10, 2015

I'm using the MS Access 2010 ExportwithFormating action to export three tables to a single MS Excel 2010 workbook. The action overwrites the first excel worksheet each time instead of saving all three worksheets in a single excel workbook.

How can I export three tables into a single excel workbook.

View 1 Replies View Related

Tables :: Updating Fields In Multiple Tables Without Onclick Event

Oct 23, 2013

I am working on a database which has two tables used as part of a registration and login process.

I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.

The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.

View 1 Replies View Related

Tables :: Adding New Customers To Multiple Tables At Same Time

Mar 13, 2014

I am new with Access and I am setting up a data base that will have 16 tables and each table with have over 3,000 customers. The reason for 16 tables is that we will be inputting information on a monthly basis for each of these customers (12 of the tables) and I also have 4 tables that will be perfomring different "tasks" for each customer (one is just to keep the notes we make for each customer, one will be to show any billing done, one is going to be a summary of the entire year along with some additioanl information not entered elsewhere and the final one is our customer information table).

The customer account number is the primary key for all of my tables.I want to know how I can add a new customer (either using query or form) at one time that will populate all the tables. Right now I have everything set up as tables but I can set up queries if need be.

View 14 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







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