how to key in info that relate to another table
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?
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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
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).
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.
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.
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.
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?
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.
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.
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