Solved

Query question

Posted on 2013-02-06
8
252 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now