SQL Query Help

[AC5FF]

I've got a tough one - for me - that I am hoping others here may know a simple answer to. I am terrible (read extreme novice) at SQL queries. I have always done my queries via design view.

I have found myself needing to correct a query but do not believe it is possible to do in design view; basically because it has to do with a join. When I look at the query in SQL I can read through it well enough to say where I need to make my changes. That portion of the query is here:

INNER JOIN (CCUST INNER JOIN [BASE: APTINFO] ON CCUST.[Account #] = [BASE: APTINFO].[CCC-AAA])

Because I was not involved with these databases when they were started and I am pulling data from numerous sources in this query some do not match up any longer. That is what I am trying to overcome.

What I need this join to do is to ignore the first digit of the fields and only join on the left portion. All the data in both fields are the same except for a few accounts. One field in CCC-AAA is entered as "W11-010" but in the CCUST Account# field it is entered as 011-010.

So is it possible to write this join information so that it would see a match between "W11-010" and "011-010" ?? If so, how?

If required, I can post the whole SQL query here; I just didn't think that would be necessary.

Thank You!

 

[jdraw]

It would be better for others if you could post the entire SQL for the query involved.
Even better would be the structure of tables involved and a few sample records/values.

 

[vbaInet]

1. Create a query to return the right part
2. Join on this field using:

INNER JOIN (CCUST INNER JOIN [BASE: APTINFO] ON CCUST.[Account #] LIKE "?" [COLOR="Blue"]NewQuery[/COLOR].[CCC-AAA])

... can only be done in SQL View.

Edit: Didn't notice you there jdraw.

 

[AC5FF]

Uh Oh.... Maybe I bit off more than I can chew???

vbaInet: Using a new query could work; but I'm worried that I will start to mess up the rest of this monster.

Here is the whole query:
(I have added a blank line to isolate the area in question...)

SELECT [Zero Run Time List].ReportDate, [Zero Runtime Report Date].ActiveReportDate, [Zero Run Time List].[ACCT NUMBER], [Zero Run Time List].[High-Low-Zero], CCUST.[Complex Name], CCUST.[Resident Manager (or Contact)], [BASE: APTINFO].[Apt Unit #], [BASE: APTINFO].[Apt Street Address], CCUST.[Extreme usage report to:], CCUST.[Extreme usage company], CCUST.[Extreme usage address], CCUST.[Extreme usage city, st, zip], CCUST.[Complex Phone #], CCUST.[Fax #], CCUST.[Preferred Contact], [Zero Run Time Ignore List].Reason, [Zero Run Time Ignore List].RequestDate, [Zero Runtime Responses].Response_Date, [Zero Runtime Responses].Response, [Zero Runtime Responses].Response, [Zero Runtime Responses].Response_Date, CCUST.[Owner Name], CCUST.[Management POC]FROM 	[Zero Runtime Responses] RIGHT JOIN ([Zero Runtime Data Last 20 days]  RIGHT JOIN ([Zero Runtime Report Date]  INNER JOIN (([Zero Run Time Ignore List]  RIGHT JOIN [Zero Run Time List] ON [Zero Run Time Ignore List].[ACCT NUMBER] = [Zero Run Time List].[ACCT NUMBER]) 

INNER JOIN (CCUST INNER JOIN [BASE: APTINFO] ON CCUST.[Account #] = [BASE: APTINFO].[CCC-AAA]) 

ON [Zero Run Time List].[ACCT NUMBER] = [BASE: APTINFO].[CGas Acct #]) ON [Zero Runtime Report Date].ActiveReportDate = [Zero Run Time List].ReportDate) ON [Zero Runtime Data Last 20 days].[ACCT NUMBER] = [Zero Run Time List].[ACCT NUMBER]) ON [Zero Runtime Responses].[ACCT NUMBER] = [Zero Run Time List].[ACCT NUMBER]

GROUP BY [Zero Run Time List].ReportDate, [Zero Runtime Report Date].ActiveReportDate, [Zero Run Time List].[ACCT NUMBER], [Zero Run Time List].[High-Low-Zero], CCUST.[Complex Name], CCUST.[Resident Manager (or Contact)], [BASE: APTINFO].[Apt Unit #], [BASE: APTINFO].[Apt Street Address], CCUST.[Extreme usage report to:], CCUST.[Extreme usage company], CCUST.[Extreme usage address], CCUST.[Extreme usage city, st, zip], CCUST.[Complex Phone #], CCUST.[Fax #], CCUST.[Preferred Contact], [Zero Run Time Ignore List].Reason, [Zero Run Time Ignore List].RequestDate, [Zero Runtime Responses].Response_Date, [Zero Runtime Responses].Response, [Zero Runtime Responses].Response, [Zero Runtime Responses].Response_Date, CCUST.[Owner Name], [Zero Runtime Data Last 20 days].[ACCT NUMBER], CCUST.[Management POC]
HAVING ((([Zero Runtime Data Last 20 days].[ACCT NUMBER]) Is Null))
ORDER BY [Zero Run Time List].ReportDate, [Zero Run Time List].[ACCT NUMBER];

As for providing the structure of the tables; not sure how to do that here. Both fields in question are TEXT. As for sample data, here are a few below. The ones on the left are what would be listed in the [BASE: APTINFO].[CCC-AAA] field. The ones on the right are what would be in the CCUST.[Account #] field:

002-001 . 002-001
002-002 . 002-002
002-003 . 002-003
002-004 . 002-004
...
009-900 . 009-900
W10-101 . 010-101
W10-102 . 010-102
W10-103 . 010-103
...
W25-900 . 025-900

Hope this helps!

 

[vbaInet]

You need the new query or else you can't join using the LIKE expression explained.

Otherwise, create a new query to replace the first character of [Account #] with the first character of [CCC-AAA] and join on this new field.

You need a new query either way.

 

[AC5FF]

Thanks.

I know what I need to do by using another query. Was just hoping to be able to do it using a 'LIKE' or LEFT() command in the SQL.

Worth a shot anyway.. Thanks!

 

[vbaInet]

You can but it will fail if any of the values is Null, that's why you need another query.

The following was my second suggestion in my last post (but then again it will fail if any values CCC-AAA values is Null

INNER JOIN (CCUST INNER JOIN [BASE: APTINFO] ON CCUST.[Account #] = Left(CCUST.[Account #], 1) & Right([BASE: APTINFO].[CCC-AAA], Len([BASE: APTINFO].[CCC-AAA]) - 1))

You can always filter out the Nulls and it will run just fine.

 

[AC5FF]

vbaInet
I tried that line of yours; well one I wrote almost identical at least; and it didn't work. Error said LEFT or RIGHT were not valid in the SQL. It looked like that command needed to be in the SELECT area only.

I gave up the SQL approach. I put together a simple subQuery and everything works just as expected. Way easier than I thought it would be. However, this was worth it just to try and learn a little more SQL

 

[vbaInet]

It needed an extra closing parentheses to match this one "INNER JOIN (CCUST"

 

[AC5FF]

Okay; now I am really confused.
I put your code in and it worked as I wanted/expected it to. Why it didn't work for me when I was basically doing the same thing though is beyond me. Unfortunatly I didn't save my tries. Oh well.

This will help me out in a lot of different queries in the future. Thank You!

 

[vbaInet]

Use sparingly!

Your'e welcome.