Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

joining two tables with record formatting required

Posted on 2013-10-30
3
229 Views
Last Modified: 2014-01-08
I have two tables that I need to left join but the records need to be manipulated first and I can't quite get it to work...

the following illustrates what I have better than I can explain it:

table1.custname    left joined to             table2.name
john smith                                                 smith, john
Alpha Beta                                                 bETa, AlPhA

because my query is a grouping query (or simply because i'm screwing it up somehow), I can't simply do the join in the criteria of the design view.

if someone can provide the sql on how to join them i'd appreciate it!

thanks for your help!
0
Comment
Question by:intsup
3 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 39613951
It would be better to write a view/query to get what you need from table2 and convert the names than to try to do it in the join.

IN the join every record of table 2 will be converted once for each record in table 1 that is selected

Basic idea would be

SELECT Left(YourField,InStr(YourField,",")-1) + " " + Mid(YourField,InStr(YourField,",")+1)  as PersonName, Your, Other, Fields
FROM Table2

Use that table to join to table 1

mlmcc
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 39614974
Probably the reason the join doesn't work is that there is no match.  The customer name is formatted differently in the two tables.  There should be an AutoNumber CustomerID field to uniquely identify the customers, and split the names (at least) into FirstName and LastName fields, so they can be displayed either first name first or last name first, as desired.  Then you should be able to link on the CustomerID field, and you can remove the customer name field from the second table, since you can get the name from the Customers table.
0
 

Author Comment

by:intsup
ID: 39765172
i'm afraid I got caught up with two other projects and wasn't able to work on this one at all so i'll close for now until I get back to this one..

thanks for the input, though!
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question