Combining two columns in one Table to link with one column (Relationship between Tables)

[HerrSober]

I am struggeling to make a relationship between these two tables in Access 2010.

To get the same value of the column on the first table I need to concentate two colums of the other one.

I have two tables.

Table "Orders"

"FirstnameSurname"
"John Smith"
"Tom Jensen"

Table "Costs"

"Firstname", "Lastname"
"John",         "Smith"
"Tom",         "Jensen"

The logical explaination would be;

Orders.FirstnameSurname = Costs.Firstname & " " & Cost.Lastname

But how do I write that in SQL or make it with the relationship manager so that it actually works?

Appriciate any help!

 

[Darren Bartrup]

Spot on with your logical explanation:

SELECT FirstNameSurname, FirstName, Lastname
FROM   Orders INNER JOIN Costs ON 
        Orders.FirstNameSurname = Costs.FirstName & " " &  Costs.Lastname

Edit: Access won't be able to represent this join in design view. Occasionally Access will change your query so it can be shown in design view - so keep a record of the SQL statement just in case.

 

[HerrSober]

Spot on with your logical explanation:

SELECT FirstNameSurname, FirstName, Lastname
FROM   Orders INNER JOIN Costs ON 
        Orders.FirstNameSurname = Costs.FirstName & " " &  Costs.Lastname

Edit: Access won't be able to represent this join in design view. Occasionally Access will change your query so it can be shown in design view - so keep a record of the SQL statement just in case.

Thanks! It really worked! I noticed the thing with design view. I couldn't open that anymore, but luckily I managed to add the rows I wanted by SQL-code anyways.

However, the Design View will be useful for a novice like me, when I start to add more criterias.

Will be be "ugly-coding" and wrong to write a SheetQuery where I have all the relationships or is this normal practice?

 

[HerrSober]

Spell error:

By SheetQuery I ofcourse ment CheatQuery or HelpQuery

 

[Darren Bartrup]

Not sure what you mean by a SheetQuery. Do you mean only putting the relationships into queries and not going into the relationship window?

I'm not sure - building relationships beforehand would help, but not sure what effect it has on the day to day running of the database.

Edit: Not sure what you mean by CheatQuery or HelpQuery either, sorry.

 

[HerrSober]

Not sure what you mean by a SheetQuery. Do you mean only putting the relationships into queries and not going into the relationship window?

I'm not sure - building relationships beforehand would help, but not sure what effect it has on the day to day running of the database.

Edit: Not sure what you mean by CheatQuery or HelpQuery either, sorry.

Nevermind. Thanks for your help. The question of the thread-topic is solved.

I'll guess the answers of this unclear question only requires that I play around some more in Access

 

[Joe4]

However, the Design View will be useful for a novice like me, when I start to add more criterias.

There is a way you can do this that will allow you to use Design View. You just do it in a series of two queries.

In your first query, you just have your Costs table, and you return all the fields and then create a calculated field in it to combine your names, i.e.

CombinedName: Costs.FirstName & " " &  Costs.Lastname

Then, create a new query that links your "Orders" table with the query you just created (linking the "FirstNameSurName" field to your calculated field). Then both queries can be viewed in Design View, so you can edit either one.