help with a query

[Shawn Johnson]

I have two tables
The first table has information about various postal codes like what
streetname the postal code services, the from civic number, the to civic
number (to identify the range) and so on.
The second table is simply a list of customers with their addresses and
their postal codes.

What I am trying to accomplish is to link the postal code data in the first
table to the customers in the second table. This is fairly easy just by
linking postal code to postal code from both tables. The problem I'm having
though is when 1 postal code lets say E1G1T1 services more than one range or
more than one street. The customers that have the postal code E1G1T1 get
pulled for every street and or range that the postal code services.
Fortuneately the Canada Post system is fairly granular so this does not
happen very often except in rural areas. Nevertheless I need the customers
to be routed to the right "version" of the Postal code.

Normally there is a different postal code for each street segment but
sometimes it will have these entries

Postal Code Street Name Street Type Civic
From# Civic To #
E1G1T1 Main St
1 37
E1G1T1 Main St
2 38
E1G1T1 Water Rd
1 7

What I was thinking was that since we know what the street and the civic
number of each customer is we should be able to match them based on postal
code and street name. The problem I'm having is trying to figure out how to
link a customer to the correct civic range

for example Billy Bob @ 35 Main St needs to be put in the postal code that
services Main St, Civic range From 1 To 37 and not in the postal code that
services Main St, Civic range From 2 To 38

Can anyone help me?! I hope this is a clear.

Thanks

Shawn Johnson

 

[James Hahn]

You need to add a criteria that the street name matches and that the street
number is within the range of the civic from and to. This may involve
extracting the street name number from the street address. It may also
involve inserting default values for the civic from and civic to in those
cases where the whole street is covered (to ensure that any street number is
a match). From your description it seems that you are already matching on
street name, so it's just a matter of adding the test for the street number
range.

 

[elwin]

I had to make some assumptions about your table designs since you weren't
specific, but you should get the idea. I'm using an update query to retrieve
the unique identifier of the civic range from your postal codes table and
writing it to your customers table.

tblCustomer: PostalCode, Cus_StreetName, Cus_StreetType, Cus_CivicNum,
CivicRange

tblPostals: CivicRange, PostalCode, Pos_StreetName, Pos_StreetType,
Pos_CivicLow, Pos_CivicHigh

The following query uses joins to link the two tables on PostalCode,
StreetName, and StreetType. It uses the WHERE clause to retrieve the correct
CivicRange from tblPostals and writes it to tblCustomer. The 'mod'
comparison operator makes sure that even civic numbers get compared against
even civic ranges, and odd get compared to odd.

UPDATE tblPostals INNER JOIN tblCustomer ON (tblPostals.PostalCode =
tblCustomer.PostalCode) AND (tblPostals.Pos_StreetName =
tblCustomer.Cus_StreetName) AND (tblPostals.Pos_StreetType =
tblCustomer.Cus_StreetType) SET tblCustomer.CivicRange =
[tblPostals]![CivicRange]
WHERE (((tblCustomer.Cus_CivicNum) Between [Pos_CivicLow] And
[Pos_CivicHigh]) AND (([Cus_CivicNum] Mod 2)=[Pos_CivicLow] Mod 2));

With the CivicRange field populated in both tables you can then use it as
the sole field in both tables to base future queries on. I hope this helped.
Good luck Shawn!

Elwin

 

[Shawn Johnson]

Hi, Thanks for your response. From what I understand I will need to add a
new field in the Postal table with the civic range (maybe seperating the
numbers with a -) because as it stands now there in no such field. As
mentioned there are seperate From and To fields.

From your suggestion I created a new field in the customer database called
AD_Civic_Range. This is the field that will be updated with the appropriate
range from the Postal table.

I changed your sample query but it does not work. The new range field in
the Customer remains blank. Is there anything in this query that you see as
the problem?


UPDATE Type_1_NB_Data INNER JOIN Aliant_Customer ON (Type_1_NB_Data.[Street
Type Code] = Aliant_Customer.ST_TYP) AND (Type_1_NB_Data.[Street Name] =
Aliant_Customer.ST_NAME) AND (Type_1_NB_Data.[Postal Code] =
Aliant_Customer.DEL_POST_CD) SET Aliant_Customer.AD_Civic_Range =
[Type_1_NB_Data]![Civic_Range]
WHERE (((Aliant_Customer.CIV_NO) Between [Street Address From Number] And
[Street Address To Number]) AND (([CIV_NO] Mod 2)=[Street Address From
Number] Mod 2));

 

[elwin]

The data type of the 3 Civic Number fields must be numeric for the query to
work. Other than that I see no reason why your revised version isn't
working. It runs just fine in the sample database I created to test your
solution on. I'll email the mdb to the address listed in your profile.
Hopefully that will help.

Hi, Thanks for your response. From what I understand I will need to add a
new field in the Postal table with the civic range (maybe seperating the
numbers with a -) because as it stands now there in no such field. As
mentioned there are seperate From and To fields.

From your suggestion I created a new field in the customer database called
AD_Civic_Range. This is the field that will be updated with the appropriate
range from the Postal table.

I changed your sample query but it does not work. The new range field in
the Customer remains blank. Is there anything in this query that you see as
the problem?


UPDATE Type_1_NB_Data INNER JOIN Aliant_Customer ON (Type_1_NB_Data.[Street
Type Code] = Aliant_Customer.ST_TYP) AND (Type_1_NB_Data.[Street Name] =
Aliant_Customer.ST_NAME) AND (Type_1_NB_Data.[Postal Code] =
Aliant_Customer.DEL_POST_CD) SET Aliant_Customer.AD_Civic_Range =
[Type_1_NB_Data]![Civic_Range]
WHERE (((Aliant_Customer.CIV_NO) Between [Street Address From Number] And
[Street Address To Number]) AND (([CIV_NO] Mod 2)=[Street Address From
Number] Mod 2));

I had to make some assumptions about your table designs since you weren't
specific, but you should get the idea. I'm using an update query to
retrieve
the unique identifier of the civic range from your postal codes table and
writing it to your customers table.

tblCustomer: PostalCode, Cus_StreetName, Cus_StreetType, Cus_CivicNum,
CivicRange

tblPostals: CivicRange, PostalCode, Pos_StreetName, Pos_StreetType,
Pos_CivicLow, Pos_CivicHigh

The following query uses joins to link the two tables on PostalCode,
StreetName, and StreetType. It uses the WHERE clause to retrieve the
correct
CivicRange from tblPostals and writes it to tblCustomer. The 'mod'
comparison operator makes sure that even civic numbers get compared
against
even civic ranges, and odd get compared to odd.

UPDATE tblPostals INNER JOIN tblCustomer ON (tblPostals.PostalCode =
tblCustomer.PostalCode) AND (tblPostals.Pos_StreetName =
tblCustomer.Cus_StreetName) AND (tblPostals.Pos_StreetType =
tblCustomer.Cus_StreetType) SET tblCustomer.CivicRange =
[tblPostals]![CivicRange]
WHERE (((tblCustomer.Cus_CivicNum) Between [Pos_CivicLow] And
[Pos_CivicHigh]) AND (([Cus_CivicNum] Mod 2)=[Pos_CivicLow] Mod 2));

With the CivicRange field populated in both tables you can then use it as
the sole field in both tables to base future queries on. I hope this
helped.
Good luck Shawn!

Elwin

 

[Shawn Johnson]

You're a life saver!

It worked great.

Shawn J

The data type of the 3 Civic Number fields must be numeric for the query
to
work. Other than that I see no reason why your revised version isn't
working. It runs just fine in the sample database I created to test your
solution on. I'll email the mdb to the address listed in your profile.
Hopefully that will help.

Hi, Thanks for your response. From what I understand I will need to add
a
new field in the Postal table with the civic range (maybe seperating the
numbers with a -) because as it stands now there in no such field. As
mentioned there are seperate From and To fields.

From your suggestion I created a new field in the customer database
called
AD_Civic_Range. This is the field that will be updated with the
appropriate
range from the Postal table.

I changed your sample query but it does not work. The new range field in
the Customer remains blank. Is there anything in this query that you see
as
the problem?


UPDATE Type_1_NB_Data INNER JOIN Aliant_Customer ON
(Type_1_NB_Data.[Street
Type Code] = Aliant_Customer.ST_TYP) AND (Type_1_NB_Data.[Street Name] =
Aliant_Customer.ST_NAME) AND (Type_1_NB_Data.[Postal Code] =
Aliant_Customer.DEL_POST_CD) SET Aliant_Customer.AD_Civic_Range =
[Type_1_NB_Data]![Civic_Range]
WHERE (((Aliant_Customer.CIV_NO) Between [Street Address From Number] And
[Street Address To Number]) AND (([CIV_NO] Mod 2)=[Street Address From
Number] Mod 2));

I had to make some assumptions about your table designs since you
weren't
specific, but you should get the idea. I'm using an update query to
retrieve
the unique identifier of the civic range from your postal codes table
and
writing it to your customers table.

tblCustomer: PostalCode, Cus_StreetName, Cus_StreetType, Cus_CivicNum,
CivicRange

tblPostals: CivicRange, PostalCode, Pos_StreetName, Pos_StreetType,
Pos_CivicLow, Pos_CivicHigh

The following query uses joins to link the two tables on PostalCode,
StreetName, and StreetType. It uses the WHERE clause to retrieve the
correct
CivicRange from tblPostals and writes it to tblCustomer. The 'mod'
comparison operator makes sure that even civic numbers get compared
against
even civic ranges, and odd get compared to odd.

UPDATE tblPostals INNER JOIN tblCustomer ON (tblPostals.PostalCode =
tblCustomer.PostalCode) AND (tblPostals.Pos_StreetName =
tblCustomer.Cus_StreetName) AND (tblPostals.Pos_StreetType =
tblCustomer.Cus_StreetType) SET tblCustomer.CivicRange =
[tblPostals]![CivicRange]
WHERE (((tblCustomer.Cus_CivicNum) Between [Pos_CivicLow] And
[Pos_CivicHigh]) AND (([Cus_CivicNum] Mod 2)=[Pos_CivicLow] Mod 2));

With the CivicRange field populated in both tables you can then use it
as
the sole field in both tables to base future queries on. I hope this
helped.
Good luck Shawn!

Elwin