I have asked this in a roundabout way in another (more complex) post but thought that maybe as a standalone question it might be easier to find for future reference and maybe preventing the question being asked by anyone else. Obviously I would never use such small tables but for the clarity of the question I have kept it simple. Anyway......
In a database I have two tables
forename
ID
first_name
surname
ID
last_name
Say for example I wanted to create a table called 'full_name' from the data above. For example, if the entries in forename were ID first_name
1 John
2 Jim
3 Jack
and the entries in surname were ID last_name
1 Smith
2 Jones
3 Evans
How would I create a table called full_name which would allow me to take a first name from the forename table and a last name from the surname table. Obviously I would need to create a relationship so in a relationship window I would open the forename and surname tables. Now which field would I drag to which to create the relationship? And how would I create a form that would give me a dropdown menu with only the relevant options in it (for example, the only options in the first_name dropdown field would be John, Jim and Jack as they have come from that relevant table/field)?
I used to be able to do this in Access but cannot remember how, or if it is even the same process in OO.
Thanks in advance
Steve
Last edited by MargateSteve on Sun May 25, 2008 1:03 pm, edited 1 time in total.
I am not sure that I have fully understood the question, but let me try to propose three answers.
First, if your objective is to make a table full_name with the combination of First_Name and Last_Name that have the same idea, create a query with these two tables, then drag the ID field from the first table to the second, then double-click on the field First_Name, then on the field Last_Name. Save the query and run it. You will get a table:
John Smith
Jim Jones
Jack Evans
If you want to make all the possible combinations of first names and last names, proceed as above except that you mustn't drag the ID field to link the tables together. You will get a table with 9 combinations.
If you want to create a form with the possibility to choose the first name from the Forename table and the last name from the Surname table, and save the combination in the table full_name, then proceed this way (if someone knows a faster way, let us know!):
1. Click on Forms, then "Use Wizard to create a form"
2. Select the table full_name, and put all the fields but first name and last name on the form
3. Click the "Next" button until you reach step 5. then select the top right layout, then click Finish, give a name to the form and close it
4. Right-click on the form name and select edit.
5. Select the combo box tool from the toolbar Form Control, and draw a rectangle on the form where you want to put it
6. The combo box wizard opens, then select "Forename", click next, select "First_Name", click next, then "Yes, I want to save in" and select "First_Name'
7. Repeat steps 4 to 6 with the combo box for Last_Name (select the table Surname and the field Last_Name, of course...)
8. Your form is ready, it will store records in the table full_name with a first name from the table "Forename" and the last name from the table "Surname".
