You would be right in needing two tables.
The idea is that you create two tables that each have their own ID field.
This field should be a number field with the auto enter option of "Serial Number"
Then in one of tho two fields you provide a number field to store the ID of the other table. That's called a "Foreign Id".
So you'll have something like this:
Customers = is the table
ID - is a number field with the auto enter option of "Serial Number" So the first record is number "1" the second one "2" etc.
You could also make a calculation field that calculates the full name.
Then you make a second table. I don't know what you need to make exactly,
but let's say you are a doctor and you need to create admissions for patients.
You'll have a second table with the information for each admission. It'll look something like:
Admissions = is the table
ID - is a number field with the auto enter option of "Serial Number".
PatientIdFk - this is where the patient ID will come and this is the field that you need to relate to the PatientID in the Patient table.
AdmissionDate could be another field here. This would be a date field.
Now you can fill out the PatientIdFk fiend on your admission in different ways. The simplest way is that you just manually type the ID number of that patient. But you might not know them all by heart. So you'll probably use a dropbox. Or you'll start from a patient record and then you make a button "Create New Admission" that sets the patientID in the PatientIDFK field in the Admissions table.
When a record in the admissions table has a PätiendId in the PatiendIdFk field it can display data from the Patients table.
So the idea is that you just put the fields "FirstName", "LastName" etc from the Patients table on your Admissions table.
In the beginning they will be empty, but as soon as you fill in the PatientIdFk field, magic happens :)
Wow, that sounds rather complicated. Don't worry, it isn't. But if you want to cheat. I made a quick little example:
This is really basic and you can take this a lot further, but this is the general Idea.
What a Star you are... I'm reading your answer several times and its slowing sinking in. Quiet simple in a complicated way.!!
Your attachment you drop boxed over is just the kiddy. Exactly what I'm trying to do...Time to get the knives out and cut open and dissect.
Thankyou for your plane English explanation, much appreciated.
Now this will keep me busy for hours.....
Hehe, don't worry, you'll get it and you'll be surprised at how simple this really is.
If you have any more questions, just let me know.