Solved

Query question

Posted on 2013-02-06
8
254 Views
Last Modified: 2013-02-16
Hi Experts,
I have a query question.  I have two table, 1). FP_Child, this table has a CHDOCKET (has 9 digits - 00D012345), 2). FP_SUP , this table has a SUPDOCKET too but not all the CHDOCKET are 9 digits, it might 8 digits - 00D12345).  I want to do a query this use CHDOCKET in FP_Child join the SUPDOCKET in FP_SUP, but I know when I join both table, the CHDOCKET must be exactly match the SUPDOCKET, bu tin my sitution , is any way I can make the query works even the SUPDOCKET is missing a one number compare to the CHDOCKET?  The CHDOCKET is always 9 digitis.  How can I make the 00D012345 (FP_Child table) Join 00D12345 (FP_SUP Table) will show 00D012345 in the query.

Thanks
0
Comment
Question by:jodstrr2
[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
  • 6
  • 2
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38861498
>is any way I can make the query works even the SUPDOCKET is missing a one number compare to the CHDOCKET?

No.  Access SQL can't do a 'join these two values even if one character is off'.

Can you predict with certainty which number will be off?  If yes, maybe there is a work-around.
0
 

Author Comment

by:jodstrr2
ID: 38861705
Yes, some of the SUPDOCKET in FP_SUP table are missing the "0" after the letter,  some of them are 9 digitis include the "0" after the letter, some of them are 8 digitis which missing the "0" after the letter.  But the FP_Child table always contained 9 digitis.
0
 

Author Comment

by:jodstrr2
ID: 38864560
Here is my query, but it seems not working:
SELECT FP_Child.CHDOCKET, FP_SUP.SUPDOCKET, IIf(Left([SUPDOCKET],3) & Right([SPDOCKET],5),[FP_child]![CHDOCKET]) AS expr1
FROM FP_Child INNER JOIN FP_SUP ON FP_Child.CHDOCKET = FP_SUP.SUPDOCKET
WHERE (((FP_Child.CHDOCKET)="00D012345"));

The SUPDOCKET in FP_SUP table is 00D12345
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:jodstrr2
ID: 38865057
I created a sub query (below) then add this sub query to my main query, works.
SELECT FP_SUP.SUPDOCKET, IIf(Left([SUPDOCKET],3) & Right([SUPDOCKET],5),Left([SUPDOCKET],3) & "0" & Right([SUPDOCKET],5)) AS [New Docket]
FROM FP_SUP;
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38865284
Based on your first comment, it looks like there are multiple scenarios in play for missing characters in order to do a successful join.

What I'd recommend in this situation is to add a column to the FP_SUP table that holds the correctly-formatted nine characters, then write a series of UPDATE statements to translate the eight-character SUPDOCKET to it's nine-character counterpart.

Then join on those two.

Doing three or four different expressions in a join is likely not going to work well.

Hope this helps.
Jim
0
 

Author Comment

by:jodstrr2
ID: 38865367
Hi Jim, the FP_SUP table is a history table and we don't want to update the table, is any way I can use the LEN in the query?  like if the LEN of the SUPDOCKET in the FP_SUP table is 8 digits then add a "0", otherwise leave as 9 digits.
0
 

Accepted Solution

by:
jodstrr2 earned 0 total points
ID: 38877998
The correct query is:
SELECT FP_SUP.SUPDOCKET, IIf(Len([SUPDOCKET])=9,[SUPDOCKET],(Left([SUPDOCKET],3) & "0" & Right([SUPDOCKET],5))) AS NewDocket
FROM FP_SUP;
0
 

Author Closing Comment

by:jodstrr2
ID: 38896186
This works.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

749 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