Solved

joining two tables with record formatting required

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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.
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…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

761 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