Wildcard Query between two tables (Ms Access novice) (1 Viewer)

iwantoh

New member
Local time
Today, 11:33
Joined
Oct 7, 2003
Messages
6
I have two tables, The first table is a transaction table and the other one is a list of part numbers (thousand of parts numbers in the table).

I need to query these two tables by connecting them with part number fields. My problem is the part numbers in the other table is not correctly formatted.

Is there a way to run query from the two tables by using wildcard query from the other table?

Any advice or help would be much appreciated.

Many Thanks,

Iwanto
 

dcx693

Registered User.
Local time
Today, 07:33
Joined
Apr 30, 2003
Messages
3,265
This might be possible, but it all depends on how the part numbers are structured. Post an example and we'll tell you if it's possible.
 

iwantoh

New member
Local time
Today, 11:33
Joined
Oct 7, 2003
Messages
6
Here are examples of the two tables and unformated part number in the PART table


Trancation table:

Transaction Code Part Number
AN10002 QA1122BA
AN10002 PB765AN


Part Table:

Part Number
Q A1122 B_
PB765 A#


As you can see part numbers in part table are not correctly enterred. What make these parts unique are the three digits in the middle of the part e.g. 122 and 765 of the parts. Therefore, it is safe to use wildcard like *122* and *765*.

Thanks,

Iwanto
 

dcx693

Registered User.
Local time
Today, 07:33
Joined
Apr 30, 2003
Messages
3,265
This is possible, but that first example, where you have "QA1122BA" and say that the unique 3-digit number is 122....that's a tough case.

You could write a function to extract the three numbers in the middle of the string, but how would Access know that in the case of "QA1122BA", that you want "122"?

How about this: for "Q A1122 B_", you could strip out the extra space and match up the first 6 characters to "QA1122BA". The first 6 characters of "PB765AN" also match up to "PB765 A#". Would that work as a general rule?
 

iwantoh

New member
Local time
Today, 11:33
Joined
Oct 7, 2003
Messages
6
The three digit numbers represent part types and can be found in different part numbers e.g. QA3122GA, QR34122FG, TB765NA etc. The desired results would be any part maches the three digit numbers or the closed possible result that can be found. All parts have a fix length of 8 characters.
 

dcx693

Registered User.
Local time
Today, 07:33
Joined
Apr 30, 2003
Messages
3,265
OK, but which of the numbers in:
QA3122GA, QR34122FG, TB765NA
are the unique 3 digits? You must be able to give Access a general rule to follow.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:33
Joined
Aug 11, 2003
Messages
11,696
Code:
Public Function GetFirst3numbers(PartNo As String) As Integer
    Dim I As Integer
    Dim tempStr As String
    For I = 1 To Len(PartNo)
        'between 0 and 9
        If Asc(Mid(PartNo, I, 1)) >= 48 _
        And Asc(Mid(PartNo, I, 1)) <= 57 Then
            GetFirst3numbers = Mid(PartNo, I, 3)
            Exit Function
        End If
    Next I
End Function

?GetFirst3numbers("QA3122GA")
312

?GetFirst3numbers("TB765NA")
765

Something like that?

Regards
 

iwantoh

New member
Local time
Today, 11:33
Joined
Oct 7, 2003
Messages
6
Sorry, there I did not give a good example, hear are the good ones:


QA3122GA
QR3122FG
TB 765NA (the blank space after TB is required)


As general rules, the three digits can be identified from the forth position or before the last two characters. Can this be done in query instead of codes?
 

dcx693

Registered User.
Local time
Today, 07:33
Joined
Apr 30, 2003
Messages
3,265
Don't worry about giving "good" examples in the sense of giving those that follow a general rule. We really need to see data that is typical of the actual data in the db. We can easily design some type of general rule for some data that won't fit all the data - and in that case, the general rule would be worthless.

If those three examples you just posted are typical, then you can take the 4th, 5th, and 6th characters of the string, and those would be the 3-digit unique identifier.

However, the examples you posted earlier did not follow this pattern. Like these:
Q A1122 B_
PB765 A#
Are these actual examples of data from your db? If so, then the general rule will fail for these.
 

iwantoh

New member
Local time
Today, 11:33
Joined
Oct 7, 2003
Messages
6
We do have badly typed in part numbers like Q A1122 B_ and PB765 A#. I don't think this would work with general rule, we have decided to do manual clean up on badly typed in parts. Then use the query to extract the disired part numbers. This would save us a lot of time.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:33
Joined
Aug 11, 2003
Messages
11,696
the three digits can be identified from the forth position ?mid("XXX123XX",4,3)
123
before the last two characters
?mid("XXX123XX",len("XXX123XX")-4,3)
123
?mid("XXXXX123XX",len("XXXXX123XX")-4,3)
123

This can be done in a query the same way....

Regards
 

dcx693

Registered User.
Local time
Today, 07:33
Joined
Apr 30, 2003
Messages
3,265
Well, if you can fix the bad entries, then great. Can you post an example of how the revised entries will look on one table and how they might look on the other table. I think with the formula that namliam suggested, we should be able to join these two tables using a query.
 

Users who are viewing this thread

Top Bottom