I am in the beginning of setting up a database and have NOT messed with multiple tables. I just want to make sure that I am setting up the tables as well as the relationships correctly.Here is what I currently have, 4 tables and they are named. Employee, phone, radio and spotter. Employee table is where my primary key is located and I currently have a 1-to-many relationship between Employee and the others.
What I want to be able to do is to store information in the multiple tables from 1 form, which I don't think will be an issue and then at a later date be able to pull up information. EX.Thus being able to skip anything being put into the spotter table. Then at a later date be able to pull up only the select information I originally put it?
I am still new at database design, and cant quite come to terms with my project and access way of doing things.
I have to keep a register of people who participate in projects. The projects can be of two different kinds. BUT (here comes the tricky part) The projects are being evaluated on three different indicators, with each one of these having 4 measurements, in the range of 4-0. That was a quick introduction. Now let me break it down in parts.
The people:
I have made a Uniqe identifier (Social Security number (PK))
First Name Last Name Department (This can be 4 different departsment) made a drop-down menu type.
The Projects:
Unique identifier (Project ID (PK)) Social Security number Project Type Start date End date
Project type:
Unique identifier (TypeID (PK) Project type (Cti / Regular)
How might i design this the best way, so i can combine the people with the projects there on. And which type.
There can only be one person, but he can be on many projects. These projects can vary in type. My problem is ensuring there connected proberly.
Furthermore, once the basic design is made i need to make evaluations based on their performance if they are on the projecttype "Cti".
here i need 3 x this:
Evaluations:
Objective (range 0-4) Baseline reading (range 0-4) Midway reading (range 0-4) End reading (0-4) Success = Yes/NO (here i will do a End reading <= Objective formula).
That was a rather long list, but i have sat working on this in three whole days, and im getting a little fed up with not knowing up-and-down.
I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.
Here is what I am wanting to do:
I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate
I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I have a table with all my contacts. Manufacturers, distributers and outlet shops. I want to be able to make relationsships that show which manufacturers sell to which distributers and outlet shops, but at the same time I also want to be able to see which manufacturers are being sold by a particular outlet shop or distributer.
I am reading everywhere that you need to make a table to link the 2 tables together for every one relationship you have and this I understand. Most of the time it is relations between 2 tables that you want to establish. But in this case all these companies are in the same table. Can I still make a many-to-many relations ship between different items that are all in the same table using one single extra table that holds these relationships?
i am making a database from which i can calculate fuel required from one point to other in any particular type of vehicle.the problem is fuel from Pt A to Pt B is not same as from Pt B to Pt A.A sample table is attached as zip. qp.bmpis there any way i can cross refer field values with that of rows .
When I originally created my access database, I used a text field/column to connect 2 tables together. I would like to change this to reference the Primary Key/ID column instead of the text field.
Is there an easy way to go about doing this? My original thought was to create some vba code to replace the data in the column of the sub table with the autonumber in the primary table but I cannot figure out how to do it.
I have a contacts database and I am trying to set the relationship between the contacts table and the locality table. The contacts table has a LocalityID field that is a long integer and the Locality table has an autonumber as the PK. When I drag the LocalityID on one table to the other LocalityID I get the Can't create this relationship. When I look at the Edit Relationship dialog box the primary table is the Locality table not the Contacts table. I want set up a lookup on the contacts form that relates to locality.
I have two tables containing data from two separate sources. The unique "link" between the two is a JobNumber field. I need to set up a relationship between the two tables using the JobNumber field.
Problem:
In one table, the JobNumber has been input in this format: 12-00345-01 In 2nd table, the JobNumber has been input in this format: 12-00345-1
How can I tie these when the 2nd table is missing the "0"?
I have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables? 2- how to cascade the updates to the related fields? 3- Is there any way to force the data update to other tables?
Code: Private Sub Command26_Click() If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then Forms![test site]![number correct] = Forms![test site]![number correct] + 1 End If DoCmd.FindNext End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
I'm sorry if this is obvious, but I have asked local professionals and scoured this site for answers before posting...
I have a database that I'm using to store and analyze language samples (basically, the mistakes that students make when trying to speak in another language). For each bit of language, I want to be able to say what domain the mistake belongs to - e.g. the 'clause', 'phrase', or 'word' domain. I have a table containing the five domains. I also have a table for each of these domains, containing the specific types of mistakes relevant to that domain. It is conceivable, but unlikely, that I will ever add to the domain table, but highly likely that I will add types of mistakes in the five related tables.
As I analyze bits of language, I will be appending each analysis (record) to a table as shown in the attached image. So far so good. The part that I cannot fathom, though, is how to display (let alone perform calculations on) this data: While the ErrorDomainID is a foreign key to the ErrorDomain table, the ErrorUnitID could refer to one of five tables, so that an ErrorUnitID of 2, for example, could refer to one thing if the Domain is 1 but quite another if the domain is 1 or 5 or whatever.
I've tried all kinds of coding and SQL workarounds, but I have a feeling I'm just missing something very basic here. If anyone could offer some guidance, I'd be extremely grateful!
Table 1: contains staff names and contact numbers Table 2: contains training above staff have been on or need to go on Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
I've only just started using Access 2007 at my new job. I've been asked to create a database that will show appointments for all 10 of the employees. I have created a table for the main schedule (where ill put all the data) then one for each of the employees. I've managed to link the tables no problem but it wont let me create and updating relationship. It keeps saying "no unique index found for the referenced field of the primary table". How do I fix this?
I want it to automatically update the date, time, location, customer name and description, if its changed on the main schedule for a certain appointment on the corresponding employees schedule.
I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
I am trying to define a relationship between tables. However, the unique nature of my data doesn't seem to be allowing typical relationships. I am not sure if I need to somehow create a relationship (junction table?) or just keep things as they are. Do I need a Foreign Key? FYI, I am only querying the data - no updates.
I have two tables that I am able to join by using a field in Table1 tied to a portion of a field in Table2. No other columns in either table can reliably relate the two tables. For example:
Table1.ColA has a 5 character string. Table2.ColA has a 10 character string.
I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.
This match, produces 1 to Many results (for every Table1.ColA string, there are 1 to many Table2.ColA records that match.
IMPORTANT FACTORS: - Table1 contains data at a SYSTEM level. - Table2 contains the parts that make up the "SYSTEM" in Table1 - The PARTS in Table2 can be in 1 to many SYSTEMS from Table1 - Table1.ColA is not unique by itself (it's part of a composite PK in Table1). - Table2.ColA is not unique by itself (it's part of a composite PK in Table2).
I have a master table with all of my Site Names in it. I have a collection table that when a barcode is scanned it records the site name as being received. I built a query that counts the number of times the site name has been received.
What I want to do is list all of my sites from the master then display the count next to the corresponding site name. This will identify those sites that did not send in an item. Is there criteria in the query I could use so that 1) I could list all of my sites from the master table and then the count can be displayed from the collection table next to the correct Site Name?
I have a db with two table. Table1 Trade has the following among other fields: RefNo (autoNo) PK, TradeDate, HostName, Methodology, etc.
Table2 Market has the following among other fields: MarketDate as Date ( ), USD/EUR_Rate, USD/CHF_Rate, USD/BRL_Rate, etc.
I need to link both tables so that the TradeDate correspondes to the MarketDate. what is that say on TradeDate if it is on 15/01/06 I can see what the Market values for the MarketDate corresponding to that TradeDate.
I tried to link MarketDate as PK on Market table2 to TradeDate on Trade Table1. It seems to work but it does not seem right as sometimes I get errors in entering data, etc on the Market table2. The MarketDate is unique meaning only one data of values for a given date, they can be modified but not duplicated.
Can anyone help, on the best solution for the links to work?
I feel confused about something and I have the need for assistance because solving this small thing would make my life easier. I very much appreciate you taking the time to read this.
I want to normalize my database as much as possible.
I will make a make a form for Breakpoint containing two comboboxes. The top combobox will contain all Modules and I want the second combobox to contain only the Functions that are related to the selected Module from the first combobox.
I know this can be programmed, that's how I do it now: in the onChange-event of the first combobox, I load the content of the second combobox.
However: what I would like to know specifically 3 things: --> what is a common way to solve this? --> Is there a way to solve this using just table-relationships? (drop 2 cbo's on the form and with correct relationships access populates the second and/or the first automatically) --> What are the correct table relationships for this and therefore what missing ID's (used for relationships) have to be filled in in the above 3 tables?
