Topics Search

how to key in info that relate to another table

how to key in info that relate to another table
Views: 3
I have created a few tables. For example,

Table 1) Contact
Table 2) Invoice

So in Table 2, do I need to key in the customer name? or to relate it to table 1 by searching table 1's customer name?

When using excel, I can relate it by using index and match function. But with Access, how to actually doing it?
Sponsored Links:

Similar posts...


Subforms and unwanted duplicate records

Subforms and unwanted duplicate records Icon
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

Student class payments Icon
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).

Relationships problem

Relationships problem Icon
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

Normalisation and Relationship Problem Icon
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.

One-to-One Table keys

One-to-One Table keys Icon
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

referential integrity and tables Icon
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?

Relationships : Linking many tables to one

Relationships : Linking many tables to one Icon
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.

Create a table

Create a table Icon
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.

Relate rows in a single table

Relate rows in a single table Icon
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

Merge Duplicate records and Maintain relationships?

Merge Duplicate records and Maintain relationships? Icon
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