[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

joining two tables with record formatting required

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
intsup
Asked:
intsup
2 Solutions
 
mlmccCommented:
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
 
Helen FeddemaCommented:
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
 
intsupAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now