Parsing Key Field in one table in a Query

[Guest]

I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions alpha/numeric
The Old Customer number in the Old Customer table is 6 positions alpha/numeric
I want a query that looks at the first six positions (only) of the New
Customer number in the New Table and compare it to the six positions of the
Old Customer number in the Old Table and if there is a match show specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer Table to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Duane Hookom]

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

 

[Guest]

Duane,
Thank you for your help but it didn't work - let me decscribe my situation a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made up of the
first 3 characters of the persons last name and the first 3 numbers of their
address.
In the New Cust Table the Cust Number is 7 positions and is made up of the
first 3 characters of the persons last name and the first 4 numbers of their
address.
I need to find how many of the Old Custs Numbers are already in the New Cust
Table but because the New Cust Number is 1 position longer I only want to
read the first 6 positions of the New Cust Number and then compare and match
on the 6 Position Old Cust Number. I will wind up still seeing the 7 position
New Cust Numbers in the query but they will be their if they match on the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP

I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions
alpha/numeric
The Old Customer number in the Old Customer table is 6 positions
alpha/numeric
I want a query that looks at the first six positions (only) of the New
Customer number in the New Table and compare it to the six positions of
the
Old Customer number in the Old Table and if there is a match show specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer Table to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Duane Hookom]

Please provide the SQL of your query that didn't work. What do you mean by
"didn't work"?

--
Duane Hookom
MS Access MVP

Duane,
Thank you for your help but it didn't work - let me decscribe my situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made up of the
first 3 characters of the persons last name and the first 3 numbers of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made up of the
first 3 characters of the persons last name and the first 4 numbers of
their
address.
I need to find how many of the Old Custs Numbers are already in the New
Cust
Table but because the New Cust Number is 1 position longer I only want to
read the first 6 positions of the New Cust Number and then compare and
match
on the 6 Position Old Cust Number. I will wind up still seeing the 7
position
New Cust Numbers in the query but they will be their if they match on the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP

I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions
alpha/numeric
The Old Customer number in the Old Customer table is 6 positions
alpha/numeric
I want a query that looks at the first six positions (only) of the New
Customer number in the New Table and compare it to the six positions of
the
Old Customer number in the Old Table and if there is a match show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Guest]

Duane,
Please read the rest of my last message from yesterday. In that message I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to the 6
positions in the Old Cust Table. However these several records were entered
wrong in the New Cust Table - they should have been entered with 7 postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am trying to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

Please provide the SQL of your query that didn't work. What do you mean by
"didn't work"?

--
Duane Hookom
MS Access MVP

Duane,
Thank you for your help but it didn't work - let me decscribe my situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made up of the
first 3 characters of the persons last name and the first 3 numbers of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made up of the
first 3 characters of the persons last name and the first 4 numbers of
their
address.
I need to find how many of the Old Custs Numbers are already in the New
Cust
Table but because the New Cust Number is 1 position longer I only want to
read the first 6 positions of the New Cust Number and then compare and
match
on the 6 Position Old Cust Number. I will wind up still seeing the 7
position
New Cust Numbers in the query but they will be their if they match on the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions
alpha/numeric
The Old Customer number in the Old Customer table is 6 positions
alpha/numeric
I want a query that looks at the first six positions (only) of the New
Customer number in the New Table and compare it to the six positions of
the
Old Customer number in the Old Table and if there is a match show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Duane Hookom]

Seems to me that if you would take the time to type a few sample records and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP

Duane,
Please read the rest of my last message from yesterday. In that message I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to the 6
positions in the Old Cust Table. However these several records were
entered
wrong in the New Cust Table - they should have been entered with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

Please provide the SQL of your query that didn't work. What do you mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP

Duane,
Thank you for your help but it didn't work - let me decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made up of
the
first 3 characters of the persons last name and the first 3 numbers of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made up of
the
first 3 characters of the persons last name and the first 4 numbers of
their
address.
I need to find how many of the Old Custs Numbers are already in the New
Cust
Table but because the New Cust Number is 1 position longer I only want
to
read the first 6 positions of the New Cust Number and then compare and
match
on the 6 Position Old Cust Number. I will wind up still seeing the 7
position
New Cust Numbers in the query but they will be their if they match on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions
alpha/numeric
The Old Customer number in the Old Customer table is 6 positions
alpha/numeric
I want a query that looks at the first six positions (only) of the
New
Customer number in the New Table and compare it to the six positions
of
the
Old Customer number in the Old Table and if there is a match show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Guest]

Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table to the 6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a match then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER ADDRESS1
ABA2304 Angela Abate 2304 Lincoln ABA2304 Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No. to find
the 6 position Cust No of the Old Table and then list out both records (New
and Old).

I hope that helps.
Tahnks,
Paul

Seems to me that if you would take the time to type a few sample records and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP

Duane,
Please read the rest of my last message from yesterday. In that message I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to the 6
positions in the Old Cust Table. However these several records were
entered
wrong in the New Cust Table - they should have been entered with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

Please provide the SQL of your query that didn't work. What do you mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made up of
the
first 3 characters of the persons last name and the first 3 numbers of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made up of
the
first 3 characters of the persons last name and the first 4 numbers of
their
address.
I need to find how many of the Old Custs Numbers are already in the New
Cust
Table but because the New Cust Number is 1 position longer I only want
to
read the first 6 positions of the New Cust Number and then compare and
match
on the 6 Position Old Cust Number. I will wind up still seeing the 7
position
New Cust Numbers in the query but they will be their if they match on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions
alpha/numeric
The Old Customer number in the Old Customer table is 6 positions
alpha/numeric
I want a query that looks at the first six positions (only) of the
New
Customer number in the New Table and compare it to the six positions
of
the
Old Customer number in the Old Table and if there is a match show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Duane Hookom]

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP

Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table to the 6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER ADDRESS1
ABA2304 Angela Abate 2304 Lincoln ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No. to find
the 6 position Cust No of the Old Table and then list out both records
(New
and Old).

I hope that helps.
Tahnks,
Paul

Seems to me that if you would take the time to type a few sample records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP

Duane,
Please read the rest of my last message from yesterday. In that message
I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to
the 6
positions in the Old Cust Table. However these several records were
entered
wrong in the New Cust Table - they should have been entered with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made up
of
the
first 3 characters of the persons last name and the first 3 numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made up
of
the
first 3 characters of the persons last name and the first 4 numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already in the
New
Cust
Table but because the New Cust Number is 1 position longer I only
want
to
read the first 6 positions of the New Cust Number and then compare
and
match
on the 6 Position Old Cust Number. I will wind up still seeing the 7
position
New Cust Numbers in the query but they will be their if they match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions
alpha/numeric
The Old Customer number in the Old Customer table is 6 positions
alpha/numeric
I want a query that looks at the first six positions (only) of
the
New
Customer number in the New Table and compare it to the six
positions
of
the
Old Customer number in the Old Table and if there is a match show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Guest]

Duane,

Sorry my reply is two days late - my home phone was out for two days and my
DSL was the also out.

No, the format of my last message must not have come through properly with
this format of conversation. I don't want the [Old CUSTNO] to display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old Cust No.
ABA230
then print out the entire New Cust record as well as the entire Old customer
record.

When you are matching the New and Old Cust No's only use the first 6
positions of the New Cust No. to find the Old Cust No. record, if there is
one.

Hope this is a better explanation.
Thanks again,
Paul

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP

Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table to the 6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER ADDRESS1
ABA2304 Angela Abate 2304 Lincoln ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No. to find
the 6 position Cust No of the Old Table and then list out both records
(New
and Old).

I hope that helps.
Tahnks,
Paul

Seems to me that if you would take the time to type a few sample records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP


Duane,
Please read the rest of my last message from yesterday. In that message
I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to
the 6
positions in the Old Cust Table. However these several records were
entered
wrong in the New Cust Table - they should have been entered with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made up
of
the
first 3 characters of the persons last name and the first 3 numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made up
of
the
first 3 characters of the persons last name and the first 4 numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already in the
New
Cust
Table but because the New Cust Number is 1 position longer I only
want
to
read the first 6 positions of the New Cust Number and then compare
and
match
on the 6 Position Old Cust Number. I will wind up still seeing the 7
position
New Cust Numbers in the query but they will be their if they match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old Customer
The key relationship fields in each table is the Customer Number
The New Customer number in the New Customer table is 7 positions
alpha/numeric
The Old Customer number in the Old Customer table is 6 positions
alpha/numeric
I want a query that looks at the first six positions (only) of
the
New
Customer number in the New Table and compare it to the six
positions
of
the
Old Customer number in the Old Table and if there is a match show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Duane Hookom]

I'm fairly sure my most recent SQL view does this. Either try it and tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP

Duane,

Sorry my reply is two days late - my home phone was out for two days and
my
DSL was the also out.

No, the format of my last message must not have come through properly with
this format of conversation. I don't want the [Old CUSTNO] to display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old Cust No.
ABA230
then print out the entire New Cust record as well as the entire Old
customer
record.

When you are matching the New and Old Cust No's only use the first 6
positions of the New Cust No. to find the Old Cust No. record, if there is
one.

Hope this is a better explanation.
Thanks again,
Paul

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP

Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER ADDRESS1
ABA2304 Angela Abate 2304 Lincoln ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No. to
find
the 6 position Cust No of the Old Table and then list out both records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few sample
records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP


Duane,
Please read the rest of my last message from yesterday. In that
message
I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to
the 6
positions in the Old Cust Table. However these several records were
entered
wrong in the New Cust Table - they should have been entered with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made
up
of
the
first 3 characters of the persons last name and the first 3
numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made
up
of
the
first 3 characters of the persons last name and the first 4
numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already in
the
New
Cust
Table but because the New Cust Number is 1 position longer I only
want
to
read the first 6 positions of the New Cust Number and then
compare
and
match
on the 6 Position Old Cust Number. I will wind up still seeing
the 7
position
New Cust Numbers in the query but they will be their if they
match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old
Customer
The key relationship fields in each table is the Customer
Number
The New Customer number in the New Customer table is 7
positions
alpha/numeric
The Old Customer number in the Old Customer table is 6
positions
alpha/numeric
I want a query that looks at the first six positions (only) of
the
New
Customer number in the New Table and compare it to the six
positions
of
the
Old Customer number in the Old Table and if there is a match
show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New
Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Guest]

Duane,
Thanks for staying in there with me.
I don't know SQL and therefore I don't know how to use the SQL code against
my Database with the New and Old Tables.
I am using Access Query Design Function and trying to use the Criteria area
to develop my comparison I need.
Can you advise me how to use your SQL code against my Data Base tables or
how to use the Query Design Function to do your SQL code objective. I do
believe your code (if I am reading it properly) will give me the results I
need.
Thank you,
Paul

I'm fairly sure my most recent SQL view does this. Either try it and tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP

Duane,

Sorry my reply is two days late - my home phone was out for two days and
my
DSL was the also out.

No, the format of my last message must not have come through properly with
this format of conversation. I don't want the [Old CUSTNO] to display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old Cust No.
ABA230
then print out the entire New Cust record as well as the entire Old
customer
record.

When you are matching the New and Old Cust No's only use the first 6
positions of the New Cust No. to find the Old Cust No. record, if there is
one.

Hope this is a better explanation.
Thanks again,
Paul

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP


Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER ADDRESS1
ABA2304 Angela Abate 2304 Lincoln ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No. to
find
the 6 position Cust No of the Old Table and then list out both records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few sample
records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP


Duane,
Please read the rest of my last message from yesterday. In that
message
I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to
the 6
positions in the Old Cust Table. However these several records were
entered
wrong in the New Cust Table - they should have been entered with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made
up
of
the
first 3 characters of the persons last name and the first 3
numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made
up
of
the
first 3 characters of the persons last name and the first 4
numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already in
the
New
Cust
Table but because the New Cust Number is 1 position longer I only
want
to
read the first 6 positions of the New Cust Number and then
compare
and
match
on the 6 Position Old Cust Number. I will wind up still seeing
the 7
position
New Cust Numbers in the query but they will be their if they
match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old
Customer
The key relationship fields in each table is the Customer
Number
The New Customer number in the New Customer table is 7
positions
alpha/numeric
The Old Customer number in the Old Customer table is 6
positions
alpha/numeric
I want a query that looks at the first six positions (only) of
the
New
Customer number in the New Table and compare it to the six
positions
of
the
Old Customer number in the Old Table and if there is a match
show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New
Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Guest]

Duane,
One more comment:
I know that there are many New Cust No's (using 1st 6 positions of 7
position field) that will match the Old Cust No's (6 position field).
I want to see all of the New Cust No's (all 7 positions) that match on the
first 6 positions of it's number against the 6 positions of the Old Cust No's.
I really hope I am getting my needs across.
Thanks,
Paul

Duane,
Thanks for staying in there with me.
I don't know SQL and therefore I don't know how to use the SQL code against
my Database with the New and Old Tables.
I am using Access Query Design Function and trying to use the Criteria area
to develop my comparison I need.
Can you advise me how to use your SQL code against my Data Base tables or
how to use the Query Design Function to do your SQL code objective. I do
believe your code (if I am reading it properly) will give me the results I
need.
Thank you,
Paul

I'm fairly sure my most recent SQL view does this. Either try it and tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP

Duane,

Sorry my reply is two days late - my home phone was out for two days and
my
DSL was the also out.

No, the format of my last message must not have come through properly with
this format of conversation. I don't want the [Old CUSTNO] to display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old Cust No.
ABA230
then print out the entire New Cust record as well as the entire Old
customer
record.

When you are matching the New and Old Cust No's only use the first 6
positions of the New Cust No. to find the Old Cust No. record, if there is
one.

Hope this is a better explanation.
Thanks again,
Paul




:

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP


Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER ADDRESS1
ABA2304 Angela Abate 2304 Lincoln ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No. to
find
the 6 position Cust No of the Old Table and then list out both records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few sample
records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP


Duane,
Please read the rest of my last message from yesterday. In that
message
I
tried to explain hopefully a little better what I am trying to do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only had 6
positions of the Cust Number and the query did fine these matched to
the 6
positions in the Old Cust Table. However these several records were
entered
wrong in the New Cust Table - they should have been entered with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is made
up
of
the
first 3 characters of the persons last name and the first 3
numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is made
up
of
the
first 3 characters of the persons last name and the first 4
numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already in
the
New
Cust
Table but because the New Cust Number is 1 position longer I only
want
to
read the first 6 positions of the New Cust Number and then
compare
and
match
on the 6 Position Old Cust Number. I will wind up still seeing
the 7
position
New Cust Numbers in the query but they will be their if they
match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


I have two tables in a data base - New Customer and Old
Customer
The key relationship fields in each table is the Customer
Number
The New Customer number in the New Customer table is 7
positions
alpha/numeric
The Old Customer number in the Old Customer table is 6
positions
alpha/numeric
I want a query that looks at the first six positions (only) of
the
New
Customer number in the New Table and compare it to the six
positions
of
the
Old Customer number in the Old Table and if there is a match
show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New
Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Duane Hookom]

Try open a new query and don't add any tables. Once in the design view
select View->SQL View and paste my suggested SQL into the query window. See
what happens when you then view the design view or datasheet view.

If you get an error, try to figure out what is wrong. If you can't, come
back here with your SQL view as well as your error message.

--
Duane Hookom
MS Access MVP

Duane,
One more comment:
I know that there are many New Cust No's (using 1st 6 positions of 7
position field) that will match the Old Cust No's (6 position field).
I want to see all of the New Cust No's (all 7 positions) that match on the
first 6 positions of it's number against the 6 positions of the Old Cust
No's.
I really hope I am getting my needs across.
Thanks,
Paul

Duane,
Thanks for staying in there with me.
I don't know SQL and therefore I don't know how to use the SQL code
against
my Database with the New and Old Tables.
I am using Access Query Design Function and trying to use the Criteria
area
to develop my comparison I need.
Can you advise me how to use your SQL code against my Data Base tables or
how to use the Query Design Function to do your SQL code objective. I do
believe your code (if I am reading it properly) will give me the results
I
need.
Thank you,
Paul

I'm fairly sure my most recent SQL view does this. Either try it and
tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP


Duane,

Sorry my reply is two days late - my home phone was out for two days
and
my
DSL was the also out.

No, the format of my last message must not have come through properly
with
this format of conversation. I don't want the [Old CUSTNO] to display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old Cust
No.
ABA230
then print out the entire New Cust record as well as the entire Old
customer
record.

When you are matching the New and Old Cust No's only use the first 6
positions of the New Cust No. to find the Old Cust No. record, if
there is
one.

Hope this is a better explanation.
Thanks again,
Paul




:

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old
Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust
Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old
CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP


Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table
to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not
ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a
match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER
ADDRESS1
ABA2304 Angela Abate 2304 Lincoln
ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No.
to
find
the 6 position Cust No of the Old Table and then list out both
records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few sample
records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP


Duane,
Please read the rest of my last message from yesterday. In that
message
I
tried to explain hopefully a little better what I am trying to
do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only
had 6
positions of the Cust Number and the query did fine these
matched to
the 6
positions in the Old Cust Table. However these several records
were
entered
wrong in the New Cust Table - they should have been entered
with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I
am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do
you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me
decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is
made
up
of
the
first 3 characters of the persons last name and the first 3
numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is
made
up
of
the
first 3 characters of the persons last name and the first 4
numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already
in
the
New
Cust
Table but because the New Cust Number is 1 position longer I
only
want
to
read the first 6 positions of the New Cust Number and then
compare
and
match
on the 6 Position Old Cust Number. I will wind up still
seeing
the 7
position
New Cust Numbers in the query but they will be their if they
match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field
to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


message
I have two tables in a data base - New Customer and Old
Customer
The key relationship fields in each table is the Customer
Number
The New Customer number in the New Customer table is 7
positions
alpha/numeric
The Old Customer number in the Old Customer table is 6
positions
alpha/numeric
I want a query that looks at the first six positions
(only) of
the
New
Customer number in the New Table and compare it to the
six
positions
of
the
Old Customer number in the Old Table and if there is a
match
show
specific
fields of each table in the query.
i.e. can you parse the Customer Number field in the New
Customer
Table
to
only read 6 positions when doing this compare ?
Thank you,
Paul M

 

[Guest]

Duane,

Thank you. Getting pretty late back here in Cleveland, will try your
suggestions below tomorrow morning (or I should say later this morning).
I will let you know results.
Thanks again for your endurance with me.
Good Night,
Paul

Try open a new query and don't add any tables. Once in the design view
select View->SQL View and paste my suggested SQL into the query window. See
what happens when you then view the design view or datasheet view.

If you get an error, try to figure out what is wrong. If you can't, come
back here with your SQL view as well as your error message.

--
Duane Hookom
MS Access MVP

Duane,
One more comment:
I know that there are many New Cust No's (using 1st 6 positions of 7
position field) that will match the Old Cust No's (6 position field).
I want to see all of the New Cust No's (all 7 positions) that match on the
first 6 positions of it's number against the 6 positions of the Old Cust
No's.
I really hope I am getting my needs across.
Thanks,
Paul

Duane,
Thanks for staying in there with me.
I don't know SQL and therefore I don't know how to use the SQL code
against
my Database with the New and Old Tables.
I am using Access Query Design Function and trying to use the Criteria
area
to develop my comparison I need.
Can you advise me how to use your SQL code against my Data Base tables or
how to use the Query Design Function to do your SQL code objective. I do
believe your code (if I am reading it properly) will give me the results
I
need.
Thank you,
Paul

:

I'm fairly sure my most recent SQL view does this. Either try it and
tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP


Duane,

Sorry my reply is two days late - my home phone was out for two days
and
my
DSL was the also out.

No, the format of my last message must not have come through properly
with
this format of conversation. I don't want the [Old CUSTNO] to display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old Cust
No.
ABA230
then print out the entire New Cust record as well as the entire Old
customer
record.

When you are matching the New and Old Cust No's only use the first 6
positions of the New Cust No. to find the Old Cust No. record, if
there is
one.

Hope this is a better explanation.
Thanks again,
Paul




:

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old
Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust
Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old
CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP


Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table
to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not
ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a
match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER
ADDRESS1
ABA2304 Angela Abate 2304 Lincoln
ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No.
to
find
the 6 position Cust No of the Old Table and then list out both
records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few sample
records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP


Duane,
Please read the rest of my last message from yesterday. In that
message
I
tried to explain hopefully a little better what I am trying to
do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only
had 6
positions of the Cust Number and the query did fine these
matched to
the 6
positions in the Old Cust Table. However these several records
were
entered
wrong in the New Cust Table - they should have been entered
with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I
am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do
you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me
decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is
made
up
of
the
first 3 characters of the persons last name and the first 3
numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is
made
up
of
the
first 3 characters of the persons last name and the first 4
numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already
in
the
New
Cust
Table but because the New Cust Number is 1 position longer I
only
want
to
read the first 6 positions of the New Cust Number and then
compare
and
match
on the 6 Position Old Cust Number. I will wind up still
seeing
the 7
position
New Cust Numbers in the query but they will be their if they
match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field
to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


message
I have two tables in a data base - New Customer and Old
Customer
The key relationship fields in each table is the Customer
Number
The New Customer number in the New Customer table is 7
positions
alpha/numeric
The Old Customer number in the Old Customer table is 6
positions
alpha/numeric
I want a query that looks at the first six positions
(only) of
the
New
Customer number in the New Table and compare it to the
six
positions
of
the
Old Customer number in the Old Table and if there is a
match
show
specific
fields of each table in the query.

 

[Guest]

Duane,

It worked, I got 103 same records based on the 1st 6 positions of the New
Cust DB.
Also I know a little bit about SQL.
Thank you so much for getting me through this.
By the way are you really an Access MVP at Microsoft ? (figured PST time is
Seattle Wash. time).
Thanks again very much,
Paul

Duane,

Thank you. Getting pretty late back here in Cleveland, will try your
suggestions below tomorrow morning (or I should say later this morning).
I will let you know results.
Thanks again for your endurance with me.
Good Night,
Paul

Try open a new query and don't add any tables. Once in the design view
select View->SQL View and paste my suggested SQL into the query window. See
what happens when you then view the design view or datasheet view.

If you get an error, try to figure out what is wrong. If you can't, come
back here with your SQL view as well as your error message.

--
Duane Hookom
MS Access MVP

Duane,
One more comment:
I know that there are many New Cust No's (using 1st 6 positions of 7
position field) that will match the Old Cust No's (6 position field).
I want to see all of the New Cust No's (all 7 positions) that match on the
first 6 positions of it's number against the 6 positions of the Old Cust
No's.
I really hope I am getting my needs across.
Thanks,
Paul



:

Duane,
Thanks for staying in there with me.
I don't know SQL and therefore I don't know how to use the SQL code
against
my Database with the New and Old Tables.
I am using Access Query Design Function and trying to use the Criteria
area
to develop my comparison I need.
Can you advise me how to use your SQL code against my Data Base tables or
how to use the Query Design Function to do your SQL code objective. I do
believe your code (if I am reading it properly) will give me the results
I
need.
Thank you,
Paul

:

I'm fairly sure my most recent SQL view does this. Either try it and
tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP


Duane,

Sorry my reply is two days late - my home phone was out for two days
and
my
DSL was the also out.

No, the format of my last message must not have come through properly
with
this format of conversation. I don't want the [Old CUSTNO] to display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old Cust
No.
ABA230
then print out the entire New Cust record as well as the entire Old
customer
record.

When you are matching the New and Old Cust No's only use the first 6
positions of the New Cust No. to find the Old Cust No. record, if
there is
one.

Hope this is a better explanation.
Thanks again,
Paul




:

So, according to your final display, the [Old CUSTNO] would display
"ABA2304" even though the value of the [Old CUSTNO] from the [Old
Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust
Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old
CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP


Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust Table
to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not
ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there is a
match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER
ADDRESS1
ABA2304 Angela Abate 2304 Lincoln
ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust No.
to
find
the 6 position Cust No of the Old Table and then list out both
records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few sample
records
and
desired output, we could complete this thread in short order.

--
Duane Hookom
MS Access MVP


Duane,
Please read the rest of my last message from yesterday. In that
message
I
tried to explain hopefully a little better what I am trying to
do.
When I said that it "didn't work" that was not 100 % correct.
There were several records in the new Customer table that only
had 6
positions of the Cust Number and the query did fine these
matched to
the 6
positions in the Old Cust Table. However these several records
were
entered
wrong in the New Cust Table - they should have been entered
with 7
postions,
3 first letters of last name and 4 first numbers of address.
Again, please read my more full explanation of the situation I
am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work. What do
you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


Duane,
Thank you for your help but it didn't work - let me
decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions and is
made
up
of
the
first 3 characters of the persons last name and the first 3
numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions and is
made
up
of
the
first 3 characters of the persons last name and the first 4
numbers
of
their
address.
I need to find how many of the Old Custs Numbers are already
in
the
New
Cust
Table but because the New Cust Number is 1 position longer I
only
want
to
read the first 6 positions of the New Cust Number and then
compare
and
match
on the 6 Position Old Cust Number. I will wind up still
seeing
the 7
position
New Cust Numbers in the query but they will be their if they
match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber field
to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


message
I have two tables in a data base - New Customer and Old
Customer
The key relationship fields in each table is the Customer
Number
The New Customer number in the New Customer table is 7
positions
alpha/numeric
The Old Customer number in the Old Customer table is 6
positions
alpha/numeric
I want a query that looks at the first six positions
(only) of

 

[Duane Hookom]

Glad you got this working. I (and several others here that use MVP in the
signature) are Microsoft MVPs. None of us are employees of Microsoft. We
were basically recognized for doing what you have just witnessed us doing.
For more information on the MVP program see
http://mvp.support.microsoft.com/default.aspx.

There are also many non-MVPs that are invaluable in these news groups. Just
because they don't use "MVP" in their signature they deserve a lot of
thanks.

--
Duane Hookom
MS Access MVP

Duane,

It worked, I got 103 same records based on the 1st 6 positions of the New
Cust DB.
Also I know a little bit about SQL.
Thank you so much for getting me through this.
By the way are you really an Access MVP at Microsoft ? (figured PST time
is
Seattle Wash. time).
Thanks again very much,
Paul

Duane,

Thank you. Getting pretty late back here in Cleveland, will try your
suggestions below tomorrow morning (or I should say later this morning).
I will let you know results.
Thanks again for your endurance with me.
Good Night,
Paul

Try open a new query and don't add any tables. Once in the design view
select View->SQL View and paste my suggested SQL into the query window.
See
what happens when you then view the design view or datasheet view.

If you get an error, try to figure out what is wrong. If you can't,
come
back here with your SQL view as well as your error message.

--
Duane Hookom
MS Access MVP


Duane,
One more comment:
I know that there are many New Cust No's (using 1st 6 positions of 7
position field) that will match the Old Cust No's (6 position field).
I want to see all of the New Cust No's (all 7 positions) that match
on the
first 6 positions of it's number against the 6 positions of the Old
Cust
No's.
I really hope I am getting my needs across.
Thanks,
Paul



:

Duane,
Thanks for staying in there with me.
I don't know SQL and therefore I don't know how to use the SQL code
against
my Database with the New and Old Tables.
I am using Access Query Design Function and trying to use the
Criteria
area
to develop my comparison I need.
Can you advise me how to use your SQL code against my Data Base
tables or
how to use the Query Design Function to do your SQL code objective.
I do
believe your code (if I am reading it properly) will give me the
results
I
need.
Thank you,
Paul

:

I'm fairly sure my most recent SQL view does this. Either try it
and
tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP


Duane,

Sorry my reply is two days late - my home phone was out for two
days
and
my
DSL was the also out.

No, the format of my last message must not have come through
properly
with
this format of conversation. I don't want the [Old CUSTNO] to
display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old
Cust
No.
ABA230
then print out the entire New Cust record as well as the entire
Old
customer
record.

When you are matching the New and Old Cust No's only use the
first 6
positions of the New Cust No. to find the Old Cust No. record,
if
there is
one.

Hope this is a better explanation.
Thanks again,
Paul




:

So, according to your final display, the [Old CUSTNO] would
display
"ABA2304" even though the value of the [Old CUSTNO] from the
[Old
Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust
Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old
CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP


Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN
Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust
Table
to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not
ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there
is a
match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER
ADDRESS1
ABA2304 Angela Abate 2304 Lincoln
ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust
No.
to
find
the 6 position Cust No of the Old Table and then list out
both
records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few
sample
records
and
desired output, we could complete this thread in short
order.

--
Duane Hookom
MS Access MVP


message
Duane,
Please read the rest of my last message from yesterday. In
that
message
I
tried to explain hopefully a little better what I am
trying to
do.
When I said that it "didn't work" that was not 100 %
correct.
There were several records in the new Customer table that
only
had 6
positions of the Cust Number and the query did fine these
matched to
the 6
positions in the Old Cust Table. However these several
records
were
entered
wrong in the New Cust Table - they should have been
entered
with 7
postions,
3 first letters of last name and 4 first numbers of
address.
Again, please read my more full explanation of the
situation I
am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work.
What do
you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


message
Duane,
Thank you for your help but it didn't work - let me
decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions
and is
made
up
of
the
first 3 characters of the persons last name and the
first 3
numbers
of
their
address.
In the New Cust Table the Cust Number is 7 positions
and is
made
up
of
the
first 3 characters of the persons last name and the
first 4
numbers
of
their
address.
I need to find how many of the Old Custs Numbers are
already
in
the
New
Cust
Table but because the New Cust Number is 1 position
longer I
only
want
to
read the first 6 positions of the New Cust Number and
then
compare
and
match
on the 6 Position Old Cust Number. I will wind up still
seeing
the 7
position
New Cust Numbers in the query but they will be their if
they
match
on
the
first 6 positions of the Old Cust Number.
I hope that explains the opportunity better ?
Thank you,
Paul M


:

You can set a criteria under the OldCustomerNumber
field
to:
=Left([NewCustomerNumber], 6)

--
Duane Hookom
MS Access MVP


message
I have two tables in a data base - New Customer and
Old
Customer
The key relationship fields in each table is the
Customer
Number
The New Customer number in the New Customer table is
7
positions
alpha/numeric
The Old Customer number in the Old Customer table is
6
positions
alpha/numeric
I want a query that looks at the first six positions
(only) of

 

[Guest]

Duane,
Thanks for the explanation and also again for your valuable help.
Maybe our paths will cross again down the road.
Paul

Glad you got this working. I (and several others here that use MVP in the
signature) are Microsoft MVPs. None of us are employees of Microsoft. We
were basically recognized for doing what you have just witnessed us doing.
For more information on the MVP program see
http://mvp.support.microsoft.com/default.aspx.

There are also many non-MVPs that are invaluable in these news groups. Just
because they don't use "MVP" in their signature they deserve a lot of
thanks.

--
Duane Hookom
MS Access MVP

Duane,

It worked, I got 103 same records based on the 1st 6 positions of the New
Cust DB.
Also I know a little bit about SQL.
Thank you so much for getting me through this.
By the way are you really an Access MVP at Microsoft ? (figured PST time
is
Seattle Wash. time).
Thanks again very much,
Paul

Duane,

Thank you. Getting pretty late back here in Cleveland, will try your
suggestions below tomorrow morning (or I should say later this morning).
I will let you know results.
Thanks again for your endurance with me.
Good Night,
Paul


:

Try open a new query and don't add any tables. Once in the design view
select View->SQL View and paste my suggested SQL into the query window.
See
what happens when you then view the design view or datasheet view.

If you get an error, try to figure out what is wrong. If you can't,
come
back here with your SQL view as well as your error message.

--
Duane Hookom
MS Access MVP


Duane,
One more comment:
I know that there are many New Cust No's (using 1st 6 positions of 7
position field) that will match the Old Cust No's (6 position field).
I want to see all of the New Cust No's (all 7 positions) that match
on the
first 6 positions of it's number against the 6 positions of the Old
Cust
No's.
I really hope I am getting my needs across.
Thanks,
Paul



:

Duane,
Thanks for staying in there with me.
I don't know SQL and therefore I don't know how to use the SQL code
against
my Database with the New and Old Tables.
I am using Access Query Design Function and trying to use the
Criteria
area
to develop my comparison I need.
Can you advise me how to use your SQL code against my Data Base
tables or
how to use the Query Design Function to do your SQL code objective.
I do
believe your code (if I am reading it properly) will give me the
results
I
need.
Thank you,
Paul

:

I'm fairly sure my most recent SQL view does this. Either try it
and
tell me
works or doesn't work and why OR tell me why you didn't try it.

--
Duane Hookom
MS Access MVP


Duane,

Sorry my reply is two days late - my home phone was out for two
days
and
my
DSL was the also out.

No, the format of my last message must not have come through
properly
with
this format of conversation. I don't want the [Old CUSTNO] to
display
ABA2304.

What I want to see from the query is as follows:

If there is a match between the New Cust No ABA2304 and the Old
Cust
No.
ABA230
then print out the entire New Cust record as well as the entire
Old
customer
record.

When you are matching the New and Old Cust No's only use the
first 6
positions of the New Cust No. to find the Old Cust No. record,
if
there is
one.

Hope this is a better explanation.
Thanks again,
Paul




:

So, according to your final display, the [Old CUSTNO] would
display
"ABA2304" even though the value of the [Old CUSTNO] from the
[Old
Cust
Table] is "ABA230"?

My earlier suggestion was something like:
SELECT [New Cust Table].*, [Old Cust Table].*
FROM [New Cust Table], [Old Cust Table]
WHERE Left([New Cust Table].[New CUSTOMER_ID],6) = [Old Cust
Table].[Old
CUSTNO];

This will show the actual value of the Old CUSTNO under the Old
CUSTNO
column, not the value from the New CUSTOMER_ID.
--
Duane Hookom
MS Access MVP


Example Partial Record from New Cust Table:
New CUSTOMER_ID First name Last Name Address
ABA2304 ANGELA ABATE 2304 LINCOLN
Ave

Example Partial Record from Old Cust Table:
Old CUSTNO USER ADDRESS1
ABA230 Angela Abate 2304 Lincoln Ave

I am trying to match the first 6 positions of the New Cust
Table
to the
6
positions of the Old Cust Table
In other words in the example above - Matching ABA230 (not
ABA2304) and
matching to the Old Cust Table CustNO (ABA230) and if there
is a
match
then
list out the following:

New CUSTOMER_ID First name Last Name Address Old CUSTNO USER
ADDRESS1
ABA2304 Angela Abate 2304 Lincoln
ABA2304
Angela
Abate 2304

In other words match on the first 6 positions of the New Cust
No.
to
find
the 6 position Cust No of the Old Table and then list out
both
records
(New
and Old).

I hope that helps.
Tahnks,
Paul

:

Seems to me that if you would take the time to type a few
sample
records
and
desired output, we could complete this thread in short
order.

--
Duane Hookom
MS Access MVP


message
Duane,
Please read the rest of my last message from yesterday. In
that
message
I
tried to explain hopefully a little better what I am
trying to
do.
When I said that it "didn't work" that was not 100 %
correct.
There were several records in the new Customer table that
only
had 6
positions of the Cust Number and the query did fine these
matched to
the 6
positions in the Old Cust Table. However these several
records
were
entered
wrong in the New Cust Table - they should have been
entered
with 7
postions,
3 first letters of last name and 4 first numbers of
address.
Again, please read my more full explanation of the
situation I
am
trying
to
do below in my message of yesterday.
Thank you for your patience and continued help.
Paul

:

Please provide the SQL of your query that didn't work.
What do
you
mean
by
"didn't work"?

--
Duane Hookom
MS Access MVP


message
Duane,
Thank you for your help but it didn't work - let me
decscribe my
situation
a
little different way to see if it may help:
In the Old Cust Table the Cust Number is 6 positions
and is
made
up
of