how to key in info that relate to another table
Subforms and unwanted duplicate records
I have two tables, one listing personnel, and another listing keys they are issued. I have a form for data entry of personnel information, and a subform for entering what keys they are issued (info that would normally be entered at the same time).
The problem arises because multiple people can have copies of the same key (which for the user's purposes essentially ARE the same key). When inputting one person's info it creates the new key in the key table, but when another person is entered with the same key number, it creates another new key, when obviously I just want it to relate the new record to the previous key
Student class payments
I building a database for karate classes and payments. I have these tables: tblStudents - for basic info (name, address etc)
tblKarateCls - a lookup table for the classes
tblEnrollment - a join table with these fields:
EnrollmentID - PK,
StudentID - Foreign key - to relate to the student table,
KarateClsID - foreign key to relate to the karate class table, and additional fields that only pertain to that
student for that class for that year like StartDate, EndDate, TuitionAmt etc.
tblPayment with DtPd, AmtPd, LtFee (if appropriate).
I can't figure out how to record the payments. Should I build a form based on tblEnrollment as the parent form and a subform on tblPayments? This would give me a list of payments for each student but then every payment must be entered in a separate form (based on the tblEnrollment).
I have a Multiple Tables with different information. the information is related to a table call ClientInfo. I have a form that has 4 Tabs. Each tab cooresponds to the different table. The first tab is the ClientInfo.
Second PaymentInfo and so on. Basically if I enter info into the ClientInfo, but if Ii want to come back to one of the other tabs later the form acts like no info was entered initially.
The ClientInfo is in the table, but nothing shows up in the form. If I enter info into each tab then the form will remember that ClientInfo.
I know the problem, but I don't know how to solve it. When I enter ClientInfo into the form the info goes into the table, but the other tables don't autonumber to accommodate for info to be entered that corresponds to that particular Client.
I think this has to do with Relationships, but not sure. I tried different relationships with ClientID as the Primary Key in all tablesthat relate to one another, but no luck.
Normalisation and Relationship Problem
I am creating a new database to store information on Jobs. The criteria are; A Job has a Job # and a Job Name
When allocating jobs, the job is broken down into Divisions. Each job has many Divisions e.g.
I created another table called tbl Job Division Info where I pulled the PK's from tbl Job Info and tbl Division Info. So this table looks like:
tbl Job Division Info
I did this because one job can have many divisions and one division can be on many jobs. Is this correct?
My other problem is how do I relate | tbl SubDivision Info to tbl Division Info ? I have attached what my relationship table looks like but to me I have Division_ID being related from one table to two other tables.
Relationships : Linking many tables to one
I've got a database that I'm designing and I can't get my relationships right. The tables I have so far are: Participants (Main Table - SS# Primary key), Case Log Info (SS# Primary Key and Link), Employment Info (SS# Primary Key and Link), Reporting Info (SS# Primary Key and Link). As you can see everything is linked by SS# because one participant will have many case logs, many employment records, many reporting info, etc.
Is that even allowed? I've never tried to link many tables to one using the same exact field before but I'm not sure how else to handle it. When I look at my relationships I can't get any of them to change from one to one and only one of my tabbed subforms will pick up the SS# from the Participant table.
One-to-One Table keys
Table1 is the parent table, Table2 is the child table. Table3 is joined to Table2 in a one-to-one relation. The purpose of Table3 is for additional fields that only apply to a subset of the records in Table2.
A form/subform is used to view Table1 header info with Table2 detail info. I think I want a similar form/subform to view Table1/Table3.
Q: Does this mean the same key field used to relate Tables 1 & 2 must also be present in Table3, making Table3 another child to Table1?
It seems this must be so; on the other hand it might be unnecessary in view of the one-to-one relationship. The records in 3 are like extensions of the records in 2, and could appear to somehow inherit the relationship back to the parent table.
referential integrity and tables
I have several tables.
Let's say that Table1 is Inventory - it is a superclass table and contains inventory ids as its primary key and some other general info that applies to each inventory item.
Table 2 is Car that has primary key that is a foreign key from Inventory table.
Table 3 is Container that has primary key that is a foreign key from Inventory table.
Table 2 and Table 3 are subclasses, they are disjoint and total in my design. I linked primary keys in Table 2 and 3 to the primary key in Table 1, and MS Access placed 1:1 relationship.
I need it to be 0:1 or 1:1 relationship, so that record for Table 1 is always there for either table 2 or 3.
As of now, when I'm typing information with primary key "1" in Inventory, it asks for the records with primary key "1" in both tables Table 2 and 3.
How do I make it, so for example, Table 1 has keys 1,2,3 where key "1" and "2" are primary keys in Table 2, but key "3" refers to Table 3?
Is there anyway to do it just by leaving primary keys in Table 1 be a foreign key which is at the same time a primary key in Table 2 and 3 without creating a new attribute in Tables 2 and 3 which will be a separate foreign key for Table 1?
Create a table
When you create a database, you store your data in tables - subject-based lists that contain rows and columns. For instance, you can create a Contacts table to store a list of names, addresses, and telephone numbers, or a Products table to store information about products.
This article explains how to create a table, how to add fields to a table, and how to set a table's primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.). It also explains how to set field and table properties.
Merge Duplicate records and Maintain relationships?
I have a messed up database not mine I inherited it. It contains duplicate records in one table. I need to get rid of all but one of each record. The trouble is that all the duplicates have a separate primary key to related information in other tables. It should just be one to many instead of many (duplicates) to many related data.
I need to merge each duplicate into one primary key id in table, but also reassign the foreign key on the records in the other related tables to the single remaining primary key id in the first table.
If I just delete the duplicates I lose the related data in other tables that I need to keep and relate to the sole remaining primary key.
Any VBA code examples out there that could get me started
Relate rows in a single table
I am in the following situation: I have made a table containing information about people I know. I am trying to find a way to keep track of marriages. Marriages are relations between people in the same table. So far I have made a table called marriages. This table contains two columns (and a key). These two columns each have a relation to the same column in the other table. PersonID is foreign key in man, and also in whife. I invented this way myself, and I am uncertain about it. But in my mind it should work fine. The problem I have is that I am unable to get the information back out. Do I use SELECT? Or does this way work at all
Populate primary key into imported tables
I've recently designed a fillable PDF file that users can type in and send it back as a xml file.
I want to import the xml into Access database so we can keep track of records. Unfortunately, it comes out a number of tables. And only 1 table has a primary key, other tables do not. Is it any way I can populate the primary key into those tables? I could do it manually but it would be tedious. I would like to find a smarter way. It might relate to programming but I have no idea of it
Populating a foreign key from a seperate form
I am working on a project to simplify a data entry system.My goal is to have the users select a number of criteria before entering in data to ensure that they are entering in data with the correct relationships.
Essentially, my problem boils down to this:I have two forms
On the first form, I am having the users select a series of categories from a number of combo boxes.
On the second form, I have a data entry form. This form has a number of fields that relate to different tables. The data on the second form is partially populated from the first form.
I have three tables all with a 1 to Many relationship. They are Program, Study, and Collection Table. Thus, there are many Collections in a Study and many Studies in a Program.On the second form, I am entering data that will create new records in either the study or collections tables.
I do not want to have the users re-enter the program level from the first form onto the second form in order to have the foreign key at the study level to populate and signify the relationship between the study and program table.However, when creating a new record from the second form, the foreign key is not populating for the Study table to relate back to the Program table,and I cannot save the new record.How can I take the information from the first form so the foreign keys populate onto the second form for the Study table?
Numbers pop up when I merge my Access DB form info
I have created a Form in Access with a few drop downs to look up info easier. But when I try to merge the info into MS Word.
The numbers are the ID (Key) from the table,. I have watched Tuts on Y.Tube but none address this problem.
Many to Many to Many
I'm still working on the Document Library. I have multiple types of sub documents, all relating to Manuals. All manuals and sub documents relate to part #s which in turn relate to multiple manuals.
I have set up a linking table between Manuals and Parts to establish the necessary many-to-many relationship between Parts and Manuals. However, the various sub documents also relate to multiple part #s.
And now, I have sub documents which relate to multiple part #s and multiple Manuals.
Copy and Past keys help
I am experienced FMP designer and have to work on a special project at work using MS Access 03.
I have two tables related by the pk of table 1 linked to table 2 as a foreign key. I want to create record in table two and relate the two by copying and pasting table 1's pk in the table 2's fk field.
I believe I have exhausted of the preset action arguments.
Warning, I will need more help soon after! I am sure!
Entering the same Primary Key in multiple tables via one form
I am attempting to create a a data base to track commercial information. This information is tracked on several tables. We have an opportunity reference number which I want to be the primary key on every table.
My question is, when I enter the opp ref number in the form for the first table. How can I link it so that, that opp ref number is immediately filled into that field on every table, but in the same order so all the data will relate to eachother
Creating a Self-Join Query to relate data within a table in Microsoft Access
When working with Microsoft Access Queries you will no doubt work with a range of join types, from the default Inner Join to the more complicated Outer Joins. In some cases, however, the related data is all within a single table. In this situation you will use a special join type that can be used to retrieve the desired data.
This type of relationship does not exist between multiple database tables, it is actually a relationship that exists between the records within a single database table.
Consider the scenario where all of the data you require is contained within a single table, but you may need to be able to relate columns within that table to each other to extract the desired data. Usual examples of this type of data relate to Employee information, where the table may have both an Employee's ID number for each record and also a field that displays the ID number of an Employee's supervisor or manager.
To retrieve the data you need to be able to relate the data to itself. For this purpose the Self-Join is designed to do exactly this action.
Query through multiple tables
My project is set up so that I have several tables with different TV sizes (ie 20 inch, 22 inch, . 60 inch) Each contain roughly the same types of info - like each have a TV Model no. and aproduct number with few containing specific info that only that TV would have, like a special part to the TV.
So what I want to do is relate these many TV TABLEs to the STAND TABLE that they would be using. I have like 10 different tables of TV sizes and 1 stand list. The TV TABLEs are related to the stand list by TV model. Sounds easy enough right?
But my problem is this- how do I make a query where I am able to just put in TV Model and it will search throught the tables and put out the necessary info - lets say I want TV Model, related Stand, and Product Number outputed.
Need to make a query that has Financial Info from basically any year. This includes Revenue, Gross Margin, and EBITDA. So far, I have created a Financial table with 6 fields:
Transaction# (primary key), Autonumber
"EntityID" is my primary key in my Entity Table, where I have a relationship with the Financial Table and "enforced referential integrity" so that I can edit info in either table.
Cascading Combo Boxes trouble
I am trying to get a series of combo boxes to interact and record the selections made in new records within a destination table. Here is situation with respect to the tables involved:
1 table lists the names/acronyms of various research facilities and the branch to which they belong (acronym is PK)
1 table lists Financial Points of Contact for each facility (acronym is FK to relate to earlier table)
1 table lists Technical Points of Contact for each facility (acronym is FK to relate to Facility table)
1 table lists program participants and the branch they belong to (Branch in FK to relate to Facility table)
I am trying to create a form that allows me to set the participant and, from this selection, restricts the facility choices in the Facility combo box to those that fall within the branch to which the participant is assigned.