• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Query question

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
jodstrr2
Asked:
jodstrr2
  • 6
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
jodstrr2Author Commented:
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
 
jodstrr2Author Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
jodstrr2Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
jodstrr2Author Commented:
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
 
jodstrr2Author Commented:
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
 
jodstrr2Author Commented:
This works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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