Filter table as it is being built

[Guernsey Gaz]

Hi All
I'm new to this and this is my first post so please be gentle

I am building a correspondence database and I have 3 main Tables
tblInvestorDetails
tblClientDetails
tbleCorrespondence

I am using the lookup wizard in the tblCorrespondence to get the LastName and FirstName from the tblClientDetails.
Is there a way that once I have chosen the LastName only those records with that LastName show when I look in the FirstName ie as soon as I have chosen the lastname it will filter, then after that I want the next field CompanyName to Automatically be updated where the LastName and FirstName are matched.

I hope there is a simple answer to this

Kind Regards
Gaz

 

[neileg]

The table level lookups are a bit of a nightmare. I never, ever use them. You should be viewing/entering your data in a form where what you want is relatively straight forward.

tblClient Details should have a field that uniquely identifies the client. This is known as the primary key (PK). You then have a field in tbleCorrespondence that holds the relevant PK value for each correspondence record. This is know as the foreign key (FK). In a one to many relationship such as you have the FK will not be unique. It is the PK/FK that provides the link between your two tables. So, when you select the client you want to filter for, you use the PK value as the filter, not the name. This way you can be sure of exactly who you are getting data on. This also allows for the times when two or more clients have the same name, or the client name changes, because all you are holding is the PK value and that never changes.

 

[Guernsey Gaz]

Thanks neileg