Lookup

[genest11]

I created my database years ago in Access 2003 and have not changed much over the past several years. We now have Access 2010 and I need to add a second table to my small one table database. The database currently has the following columns:



Table Name: Owners
ID: auto number
First name: Text
Last name: Text
Address: Text
Citystzip: Text

The Table I want to add has the following:

Table Name: Annual Cert
certID: auto number
Last name: Text
Calendar Year: Date/Time
Certmailed: Date/Time
Certreceived: Date/Time

I would think that my relationship of one to many would be: ID (one) to Calendar Year (many) as this table will be updated yearly so one owner may have many calendar years. (Is my thinking right on this?)

What I need to do is type in an ID number in the Annual Cert table in a field that will automatically lookup the last name and insert it into the Annual Cert table field Last name field. From there I want to continue to input the dates in the last two fields.

I cannot figure out what I need to do next. Any help/suggestions? Thank you.

[drewetzel]

I may be misunderstanding what you're trying to accomplish, but it seems that instead of listing "last name" in both tables you could leave it out of the Annual Cert table, just query the two tables linking "ID" to "certID" and drop the information you need from each table.

[RayMilhon]

I am making a presumption here that CertID is the Identification Number of the Certification and ID is the Identification of the Owner. Replace the last name column in the Annual Cert table with OwnerID. You would then link the owner table to the certification table from ID to OwnerID. That way you would be able to track each owner and Keep a history of each Year's certifications.

[genest11]

drewetzel,

Thank you for your response. I am trying to add info into the new table Annual Cert so I can do a report. I will have the ID number for the owner, I need the Last Name to appear on the form so I can confirm that I am inputing the correct Certmailed & Certreceived dates.

So I create a form and on that form I have a field where I put in the ID number. In the next field the Last Name that matches the ID number will appear. That will confirm I have the correct owner and I can then fill in the Calendar Year, Certmailed and Certreceived dates.

Sorry if my explanations are not clear but I am not to fluent in Access.

[genest11]

RayMilhon,

I will try your suggestion about changing the Last Name to OwnerID. I am trying to perform (for a lack of the proper term) a lookup of the Last Name based on the ID. Thank you.