Solved

joining two tables with record formatting required

Posted on 2013-10-30
3
242 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
[X]
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
3 Comments
 
LVL 101

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

615 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