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
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.
I need to create an inventory database that will track customer owned inventory as it is received stored serviced and sent back to my customer. I am in the oil and gas industry and store large amounts of customer owned pipe. many joints will have the same part number but each joint is one of a kind in that it has a joint,heat, lot, and batch number. here is an example,
Job Name: Shell ex Part Number: 129001 Quantity: 1000
Each one of the thousand joints will have a combination of this information making it one of a kind.
My system needs to allow my users to receive jobs, record where we store the pipe in a rack location, pull the specific joints out of inventory as the customer calls for them. We will receive 1000 at a time but the customer calls for portions of that job over long periods of time..
Hi, I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me. Actually, I want to make a database regarding ultrsound scan examinations of patients. I have five tables. 1. Patients. (patinetid*, patientname, age, sex, address, contact no) 2. Physicians. (physicianid*, physicianname, speciality, address, contact no) 3. Scans. (Scanid*, scanname, charges) 4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges) 5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)
I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table?? All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship. So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.
I can post an image of relationships or blank database.
My db is split into front-end and back-end. In the f-e's Relationships schematic, I can see the relationships as they were defined at the time when the db was split, complete with the one-to-many symbology.
I can add a new table to the b-e and set its relationship as one-to-many, enforcing referential integrity and cascading as I wish - and the schematic (in the b-e) reflects that.
In the f-e, I can then use the <Get External Data - Access Database> function to link to the new table, and I can add the new table to the relationships diagram in the f-e. I can also drag and drop to link primary and foreign keys (within the f-e), but cannot select one-to-many. I'm OK with that, as I understand that the relationship is within the b-e, and this is just a diagrammatic representation.
But I can see the one-to-many relationships between the tables which existed when the db was split, and I would like to be able to see the new table's relationships in a consistent fashion. Updating the linked tables via the Linked Table Manager does not do the trick.
Surely I don't have to split the database again in order to achieve a consistent diagram - do I ?
I need to redesign a database to include more complex relationships between 3 variables non of which are mutually exclusive.These are my 3 tables/fields. Each field is unique in its table.
There is no clear relationship between the variables - ie they can all be 1 to 1 or 1 to many or do not have to exist at all. So I realise now (after 14 years of working with this data), that I need to have what I would think of as holding tables between the primary tables that hold combinations of the variables. Do I do this in one table that holds all 3 variables in non-repeating combinations (although this would need to allow nulls) or do I do it in 3 separate holding tables?
I run a soccer league where we track players attendance for each game. I currently do it on a spreadsheet where each game date is a column and each player is a row. We also track which team they play on at each game (they can play on different teams different weeks). I currently have a second tab in the spreadsheet to record which team a person plays on each week.
Setting up a table of fields for this is relatively easy. The problem comes to data entry. I want to be able to visually see the data like I can in a spreadsheet (names in rows, dates in columns, intersections containing either team name or whether attended) and whilst a cross-tab query gives me the layout, I cannot input data in a cross-tab query.
I have a table which contains a list of names and the trades that each one is qualified for and a second table for a project which requires a number of different trades to fill specific roles. How do I set up a proper relationship between the two tables.
Tbl_employee. empl_ID| Empl_Name | Discipline 001 | Mathew | Construction Manager 002 | Mark | Safety officer 003 | Luke | Construction Manager 004 | John | Architect
Tbl_Proj. Project_ID Project_name Project_Architect Project Manager Health & Safety
Each employee could have more than one role on each project, one role on each project, or no roles on a project.
I have a form that shows a complete record for each project and permits me to select who fills what roles from a combo box for each role.
Using the relationship editor I try to create relationships between the following:
I then set up a SELECT in the rowsource of each combo box on the form WHERE the Discipline is whichever is required by the combobox
The raltionship editor allows the creation of a relationship between Discipline and the (multiple) various roles on the project, but if I try to 'Enforce Referential Integrity' It refuses to work.
Do I need 'Referential Integrity' and if so How do I make it work?
I dont' know what happend but now when i tried to add something to one of my subforms i get this error The LinkMasterFields property setting has produced this error:'The object doesn't contain the Automation object'Claims Header." i had it working not sure when it got screwed up. i'm freaking out. i have to get this working correctly.
EDIT: just to make sure it's ok to do this. my subform is running from a table that has my uid from my main form claim_id and i have it linked from those fields. currently the claim_id i'm looking to add to isn't in the table that my subform runs off of.
Ok here goes another Newbie question, I've been doing a lot of reading on the web and in books about relationships and setting them. But when I go to Access and set the relationships, theres a button called "Join Types". When clicked it brings up the "Join Properties" window with these three options in it:
1. Only Include rows where the joined fields from both tables are equal.
2. Include ALL records from "Company" and only those records from "Customer" where the joined fields are equal.
3. Include ALL records from "Customer" and only those from "Company" where the joined fields are equal.
I think im having trouble understanding what exactly these are refering to. The terms I think are throwing me off. If I had a table called "Company" with all the common info in it like name, address and such, What exactly is the "ROW" in that table, and the "RECORDS" of the table.
I think I need clarification on this so I can properly identify them, also if its possible could somebody supply an example for when I would use each join type? Your help is much appreciated. Thanks! ~[MikE]~
17412 Hi, I am trying to set up a database for organizing Track and Field meets. I'm a newbie to a lot of the Access capabilities. I've attached a PDF of what I'm looking at doing for my Access tables. Some of the tables I think I have right. I don't know how to organize the meets, events, days. Should I have a table for each event? Can you look at my tables and give me advice on problems you can see? In the end I would like to enter the event results by race. Some races could have 10 or more heats. There are also field events that have different number of attempts depending on the meet. There are different track meets with different events each time and the order in which the events occur changes from meet to meet. Any help or advice you can give me would be great. Thanks!
Hi, I'm working on this huge project and would like to see if anyone has any advice on how to structure and build my database. This is for the Accounting of Payroll for Staff, Contractors, and Per Diems (untaxed travel and housing exp) for a theater company about to do a major production.
The problem is there are several categories of Staff (Directors, Producers, Actors, Orchestra, Sound / Light, Costume, etc.) and each one gets paid differently. Some may get a weekly salary, a per diem, or paid contract in installments throughout the performance (e.g. 500 on openining night, 500 on closing night). Some get paid a combination of the above. The hard part is some get processed through an online payroll, while others are paid through manual check (yet in this case, certain deductions must be made). That's what made this so complicated as some will require a % of gross paid to their agent, a % to union, and balance to them.
I thought I had this figured out w/ a massive spreadsheet showing who gets what and in what period, To further complicated it, payroll is bi-weekly, so then my data within a spreadsheet became just too much to even make sense of although the data is correct.
So my question is, what tables should I create? Pay Periods / Actors / Agents / Pay Type / Contractors / or all of these in one table? I don't expect anyone to reply to this as it is asking so much and not clearly defined. But if anyone has had a similar experience I'd appreciate your help.
I have data entered all current employee training and dates that training was done.
I am looking for an explanation on how I can set it up so i can see who of the employees have not recieved the training.
I am also looking for code for the query to calculate recurrent training dates (when the next training is due for each employee) Dates of initial training for each employee may be different, as well the training may be required every one year or every two years etc.
I am hoping that I am explaining this more clearly in hopes to get some input back from the experts on this site.
What i'm trying to do is create a worksheet that contains information regarding childrens birthdays for a playcentre, so that it will show me at least 2 weeks before the birthdate so i can get a letter sent out to the parents reminding them of our party facilities,
However its been years since i've used windows programs and i'm struggling pls help,
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.
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.
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.
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.
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?
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).
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.
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.
