Solved

joining two tables with record formatting required

Posted on 2013-10-30
3
240 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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

751 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