Ask Your Question

[Base] Link Surname to Single Name field?

asked 2015-08-06 13:45:28 +0100

IceColdRum gravatar image


I'm fairly new databases in general however I have a table of contacts (including address names telephone numbers etc.) and a list of plants which I have purchased from these people what I would like to do is create a form which displays those contacts details with a linked subform below showing what plants I have previously purchased from them.

What I thought I would do is use a subform with a linked name field between the two however I want to keep the first and surname's separate in the contacts table but the table for plant records has the full name in a single cell is there a simple way to get this to work?

Sorry if this sounds overly confusing I'm sure it's not but It's quite difficult to explain in words what you want to achieve...

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-08-06 20:47:40 +0100

doug gravatar image

Usually links between tables are accomplished using a UNIQUE numeric key, auto-generated, usually designated as the PRIMARY KEY, with relations enforced by the database engine. The relationships you proposed could not be enforced by the database engine.

This can be accomplished via the form, however. You will need the record set in both the primary form and the subform to have a column with the same value. Because neither of the tables meet this criteria (one has FirstName and LastName and the other has FirstLastName) you will need to swap out one of the Table record sources for a Saved Query, SQL or View record source with column that matches the other. Those are all different ways to save a query and all require you to write out the query.

The easier way is to CONCATENATE the separated columns. The query will look like this:

SELECT `Tbl1`.*, CONCAT(CONCAT(`FirstName`, ' '), `LastName`) AS `FirstLastName`
FROM `TableName1` AS `Tbl1`

Then you will link the form and subform on the column you just created. Watch out for dynamically changing the name in the same form however ... you will send your Plant subform records to limbo....

I have not tested this solution, but it should work.

(if this answers your question please accept the answer by clicking the check mark (image description) to the left)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-08-06 13:44:41 +0100

Seen: 215 times

Last updated: Aug 06 '15