How do I do a partial match query based on a primary key?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm attempting to merge 2 tables based on a similar primary key field, but
one table uses an extra zero in the middle of the number.

How do I run a query to match the corresponding records?
 
What is the type of the field?
Is the zero always in the same place?
Where the zero located in the middle?
 
It's a numeric field, but some contain dashes and some don't.

Two example numbers would be:

99004127 2790
9904127-2790

Separating it out, the first three digits appear in every number (990),
then, sometimes, the second zero is dropped.

The next four digits (e.g., 4127) occasionally repeat, but not with the same
following four digits (e.g. 2790), and whether or not it has a dash or a
colon seems to be up to the whim of the data entry person.
 
It doesn't make sense that the fields are numeric, they must be string type
other wise you wont be able to insert - or space into it, check again please


So from what I understand the fields are build as follow

3 digits + 0 sometimes that we need to remove so we can link the two fields.
and remove spaces if we have.
would that match the two fields, or there some other things we need to knew
about that can make a different between the two fields
 
Actually, right now, the fields are cells in two different Excel spreadsheets
that I'm going to import into Access.

In one spreadsheet, there is an extra zero and no dash (e.g., 99007766
1508). In the other spreadsheet, there's a dash and one less zero (e.g.,
9907766-1508).

I was wrong, in fact, when I said that all numbers begin "990". All numbers
begin "99", but not all have that zero. The missing zero, however,
corresponds to a missing zero in the other number as well.

Example:
99010053 2102
9910053-2102
 
You can use that to replace the two fields so you can join them together
?cdbl(left("99010053 2102",2) & cdbl(mid(replace("99010053 2102"," ",""),3)))
?cdbl(left("9910053-2102",2) & cdbl(mid(replace("9910053-2102","-",""),3)))

Create a query for each table to select all the fields with an extra field,
above, and then join the two queries with the extra field
 
Sorry. You lost me.

Is that a SQL script?

So, I should create a new table with that SQL script (say I call it
"tblLink"), and link the two tables to tblLink.

Then, I should do a query off of that?
 
Open the table with the extra zero in design view and add a field named
PseudoPK. Create a query based on that table. Change the query to an update
query. Update PseudoPK to the primary key less the zero in the middle of the
number. You can do this with a combinatin of the Instr, Left, Mid and Right
functions. Once you have PseudoPK, you can then link the PK of the other
table to PseudoPK.
 
After you impoert the two Excel spreadsheets to Access you get two tables.
For each table create a query
1. Field1 = "99010053 2102"
Select MyTable.*, cdbl(left(Field1,2) & cdbl(mid(replace(Field1," ",""),3)))
as NewKey From Table1
2. Field1 = "9910053-2102"
Select MyTable.*, cdbl(left(Field1 ,2) & cdbl(mid(replace(Field1
,"-",""),3))) as NewKey From Table1

Now, after creating this two quries, create a third query that include this
two queries, when they are linked between them with the new field - NewKey
 
Back
Top