[Base] Link Surname to Single Name field?

[IceColdRum]

Hi,

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…

[doug]

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 () to the left)