very interesting query

L

lee

Zone Start End city
Zip Zip
2 500 599 jones
7 600 899 jones
3 1000 2499 jones
4 2500 2699 jones
5 500 599 carol
8 600 999 carol
5 1000 12999 carol
4 1300013299 carol
5 1330013999 carol
2 500 599 carol1
2 600 999 carol1
7 1000 6799 carol1

city qty zip ideal
jones 1200 700 carol1
carol 349 540 jones/carol

I have two tables like above in the database. the first table has
fields zone, start zip, end zip and city.
The second table has fields city, qty , zip.

I want a query which can generate the last column 'ideal' in the
second table. This column should look up the corresponding zip and
see what range it falls between start zip and end zip in the first
table. It should search the entire table 1 for that zip, look out for
the lowest zone no and then extract the corresponding city name from
the first table into the last column of the second table.

For example zip 700 in second table falls between 600-899,
600-999(twice) zip range in the first table. But the lowest zone
among these is 2 and the corresponding city is carol1 as noted above.
If we end up two cities with equal and lowest zone, we need to put
both as in second row of second table

Can some one pls help me extracting the last column of the second
table- SQL query- Thanks i will really appreciate it. it is urgent
 
L

lee

You can create a function to determine the ideal value. This function
could look like:

Function GetIdeal(Zipcode As Integer) As Variant
Dim rs As Recordset
Dim LowZone As Byte
Dim tempIdeal As String

LowZone = Nz(DMin("Zone", "T1", "StartZIP<=" & Zipcode & "AND EndZip>="
& Zipcode))
Set rs = CurrentDb.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" &
Zipcode & "AND EndZip>=" & Zipcode & _
    " AND Zone=" & LowZone)

While Not rs.EOF
    tempIdeal = tempIdeal & "/" & rs!city
    rs.MoveNext
Wend

If rs.RecordCount > 0 Then
    GetIdeal = Mid(tempIdeal, 2)
Else
    GetIdeal = Null
End If

rs.Close
End Function

The function you can use in a query to update or show the ideal value.

--
Groeten,

Peterhttp://access.xps350.com

--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---

thanks but how to use the function in the access query? can you please
give me the sql query? should the function go as module?
 
X

XPS350

thanks but how to use the function in the access query? can you please
give me the sql query? should the function go as module?

In queries you can use the function like:

SELECT Zip, GetIdeal(Zip) AS Ideal FROM T2

or

UPDATE T2 SET Ideal=GetIdeal(Zip)

Yes, you must store the funcion in a module.



Groeten,

Peter
http://access.xps350.com
 
L

lee

In queries you can use the function like:

SELECT Zip, GetIdeal(Zip) AS Ideal FROM T2

or

UPDATE T2 SET Ideal=GetIdeal(Zip)

Yes, you must store the funcion in a module.

Groeten,

Peterhttp://access.xps350.com

Thanks but i am getting a syntax error in the module when i run the
query- i am getting an error on this line

LowZone = Nz(DMin("Zone", "T1", "StartZIP<=" & Zipcode & "AND
EndZip>="
Can you please help?

Thanks
 
X

XPS350

Thanks but i am getting a syntax error in the module when i run the
query-  i am getting an error on this line

 LowZone = Nz(DMin("Zone", "T1", "StartZIP<=" & Zipcode & "AND
EndZip>="


Can you please help?

Thanks

Do you have the whole command on one line?

You also have to replace T1 withe the your table name.

Peter
http://access.xps350.com
 
J

John W. Vinson

Thanks but i am getting a syntax error in the module when i run the
query- i am getting an error on this line

LowZone = Nz(DMin("Zone", "T1", "StartZIP<=" & Zipcode & "AND
EndZip>=" & Zipcode))

Assuming that Zipcoee is a Text field (which it certainly should be, you'll
never do math with a zipcode and they might contain a zip+4) you need the
syntactically required quotemarks; in any case you need a blank before the
word AND. As written the criterion will expand to something like

StartZip<=49100AND EndZip>=49100

If StartZip and EndZip are of Text datatype, try

LowZone = Nz(DMin("Zone", "T1", "StartZIP <='" & Zipcode & "' AND
EndZip >='" & Zipcode & "'")

to give

StartZip <= '49100' AND EndZip >= '49100'

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
L

lee

Assuming that Zipcoee is a Text field (which it certainly should be, you'll
never do math with a zipcode and they might contain a zip+4) you need the
syntactically required quotemarks; in any case you need a blank before the
word AND.  As written the criterion will expand to something like

StartZip<=49100AND EndZip>=49100

If StartZip and EndZip are of Text datatype, try

LowZone = Nz(DMin("Zone", "T1", "StartZIP <='" & Zipcode & "' AND
EndZip >='" & Zipcode & "'")

to give

StartZip <= '49100' AND EndZip >= '49100'

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com


Function GetIdeal(Zipcode As Integer) As Variant
Dim rs As Recordset
Dim LowZone As Byte
Dim tempIdeal As String

LowZone = Nz(DMin("Zone", "T1", "StartZIP <='" & Zipcode & "' AND
EndZip >='" & Zipcode & "'"))


Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" &
Zipcode & "AND EndZip>=" & Zipcode & _
" AND Zone=" & LowZone)

While Not rs.EOF
tempIdeal = tempIdeal & "/" & rs!city
rs.MoveNext
End

If rs.RecordCount > 0 Then
GetIdeal = Mid(tempIdeal, 2)
Else
GetIdeal = Null
End If

rs.Close
End Function


I am using the above module- i am getting The Compile error?
 
J

John W. Vinson

Function GetIdeal(Zipcode As Integer) As Variant
Dim rs As Recordset
Dim LowZone As Byte
Dim tempIdeal As String

LowZone = Nz(DMin("Zone", "T1", "StartZIP <='" & Zipcode & "' AND
EndZip >='" & Zipcode & "'"))


Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" &
Zipcode & "AND EndZip>=" & Zipcode & _
" AND Zone=" & LowZone)

While Not rs.EOF
tempIdeal = tempIdeal & "/" & rs!city
rs.MoveNext
End

If rs.RecordCount > 0 Then
GetIdeal = Mid(tempIdeal, 2)
Else
GetIdeal = Null
End If

rs.Close
End Function


I am using the above module- i am getting The Compile error?

Sorry... I answered the question you posted last, rather than going back
through the long thread.

You fixed the syntax error in the LowZone DLookup but ignored the *exact same
error* on the next line. You need to do the same fix.

You also did not tell me the datatype of the Zipcode field. It's important,
and it does make a difference; if StartZip and EndZip are Number fields you
should not use quotemark delimiters, if they are Text then you must.

For Text fields try

Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<='" &
Zipcode & "' AND EndZip>='" & Zipcode & _
"' AND Zone=" & LowZone)

For Number fieldtype try

Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" &
Zipcode & " AND EndZip>=" & Zipcode & _
" AND Zone=" & LowZone)

Note the blank before the word AND - that's part of the problem!

Do note that if you have Number fields that 59 is in fact *NOT* between 50000
and 60000. That's why I'm assuming you're using text.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
L

lee

Sorry... I answered the question you posted last, rather than going back
through the long thread.

You fixed the syntax error in the LowZone DLookup but ignored the *exact same
error* on the next line. You need to do the same fix.

You also did not tell me the datatype of the Zipcode field. It's important,
and it does make a difference; if StartZip and EndZip are Number fields you
should not use quotemark delimiters, if they are Text then you must.

For Text fields try

Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<='" &
Zipcode & "' AND EndZip>='" & Zipcode & _
    "' AND Zone=" & LowZone)

For Number fieldtype try

Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" &
Zipcode & " AND EndZip>=" & Zipcode & _
    " AND Zone=" & LowZone)

Note the blank before the word AND - that's part of the problem!

Do note that if you have Number fields that 59 is in fact *NOT* between 50000
and 60000. That's why I'm assuming you're using text.

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

I am sorry I am confused- and donot have knowledge of VB. Can you
please post the entire module once again - my fields are numbers.
 
J

John W. Vinson

I am sorry I am confused- and donot have knowledge of VB. Can you
please post the entire module once again - my fields are numbers.

Try

Function GetIdeal(Zipcode As Integer) As Variant
Dim rs As Recordset
Dim LowZone As Byte
Dim tempIdeal As String

LowZone = Nz(DMin("Zone", "T1", _
"StartZIP <= " & Zipcode & " AND EndZip >=" & Zipcode))

Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" _
& Zipcode & " AND EndZip>= " & Zipcode & " AND Zone=" & LowZone)

While Not rs.EOF
tempIdeal = tempIdeal & "/" & rs!city
rs.MoveNext
End

If rs.RecordCount > 0 Then
GetIdeal = Mid(tempIdeal, 2)
Else
GetIdeal = Null
End If

However, the *ENTIRE PREMISE* of your structure contains a logical error!, if
I'm understanding correctly! Are these in fact United States Postal Service
five-digit Zip codes? If so, and given that the fields are numeric, you need
to go back to basic elementary arithmatic: the number 51240 is in fact NOT
between 500 and 599! It's 50639 places beyond the end of that range!

Do you mean for "between 500 and 599" to encompass the range 50000 to 59999?
If so, *say so* - use five digit numbers in your Zip field and five digit
numbers in your ranges.

If that's not what you mean, please step back just a bit, clear your mind of
your knowledge and preconceptions about your *own private idiosynctratic
business rules*, and explain the real-world situation: what numbers you're
dealing with, and what you mean by "between".
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
L

lee

Try

Function GetIdeal(Zipcode As Integer) As Variant
Dim rs As Recordset
Dim LowZone As Byte
Dim tempIdeal As String

LowZone = Nz(DMin("Zone", "T1", _
 "StartZIP <= "  & Zipcode & " AND EndZip >=" & Zipcode))

Set rs = db1.OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" _
  & Zipcode & " AND EndZip>= " & Zipcode & " AND Zone=" & LowZone)

While Not rs.EOF
    tempIdeal = tempIdeal & "/" & rs!city
    rs.MoveNext
End

If rs.RecordCount > 0 Then
    GetIdeal = Mid(tempIdeal, 2)
Else
    GetIdeal = Null
End If

However, the *ENTIRE PREMISE* of your structure contains a logical error!, if
I'm understanding correctly! Are these in fact United States Postal Service
five-digit Zip codes? If so, and given that the fields are numeric, you need
to go back to basic elementary arithmatic: the number 51240 is in fact NOT
between 500 and 599! It's 50639 places beyond the end of that range!

Do you mean for "between 500 and 599" to encompass the range 50000 to 59999?
If so, *say so* - use five digit numbers in your Zip field and five digit
numbers in your ranges.

If that's not what you mean, please step back just a bit, clear your mindof
your knowledge and preconceptions about your *own private idiosynctratic
business rules*, and explain the real-world situation: what numbers you're
dealing with, and what you mean by "between".
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

hi John

I am looking at the first three digits of five digit zip code . So my
table has the three digits stored in the zip code columns. So in the
first table 1 have ranges like zip code 500- 525 means the zip code in
table 2 might fall within this range. for example 514 would fall into
this range. please let me know if you need additional infor- i can
share it offline if you can give a non spam email. mine is
(e-mail address removed).
 
J

John W. Vinson

I am looking at the first three digits of five digit zip code . So my
table has the three digits stored in the zip code columns. So in the
first table 1 have ranges like zip code 500- 525 means the zip code in
table 2 might fall within this range. for example 514 would fall into
this range. please let me know if you need additional infor- i can
share it offline if you can give a non spam email. mine is

You're still confusing me, Lee.

Your posted example has:

2 500 599 jones
7 600 899 jones
3 1000 2499 jones
4 2500 2699 jones
5 500 599 carol
8 600 999 carol
5 1000 12999 carol
4 1300013299 carol
5 1330013999 carol
2 500 599 carol1
2 600 999 carol1
7 1000 6799 carol1


1000 2499 are not three digit numbers.
13000 13299 are not three digit numbers.
1000 6799 are not three digit numbers.

I would - *VERY STRONGLY* - suggest treating your zipcodes as TEXT, not as
numbers; this will let you do things like use the Left() function to extract
the first three *CHARACTERS* of the five-character text string. If the Zip
field in the data you are searching is the five digit zip (you *still haven't
said*) then your task will become vastly easier if you match the search to the
data, consistantly using five character codes. That is, if your range table
had

2 50000 59999 jones
7 60000 89999 jones
3 10000 24999 jones
4 25000 26999 jones

then you need *NO CODE AT ALL*, just a simple Join.

Would it be possible to recast your table in this way?

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
L

lee

You're still confusing me, Lee.

Your posted example has:

2       500     599     jones
7       600     899     jones
3       1000    2499 jones
4       2500    2699    jones
5       500     599     carol
8       600     999     carol
5       1000    12999 carol
4       1300013299 carol
5       1330013999 carol
2       500     599      carol1
2       600     999      carol1
7       1000    6799  carol1

1000 2499 are not three digit numbers.
13000 13299 are not three digit numbers.
1000 6799 are not three digit numbers.

I would - *VERY STRONGLY* - suggest treating your zipcodes as TEXT, not as
numbers; this will let you do things like use the Left() function to extract
the first three *CHARACTERS* of the five-character text string. If the Zip
field in the data you are searching is the five digit zip (you *still haven't
said*) then your task will become vastly easier if you match the search to the
data, consistantly using five character codes. That is, if your range table
had

2     50000 59999 jones
7     60000 89999 jones
3     10000 24999 jones
4     25000 26999 jones

then you need *NO CODE AT ALL*, just a simple Join.

Would it be possible to recast your table in this way?

--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Forums/en-US/accessdev/ http://social.answers.microsoft.com/Forums/en-US/addbuz/ 
and see alsohttp://www.utteraccess.com

both my tables have three digit zip codes only- i cannot change the
tables. sorry for posting the wrong data in the first place. all zip
codes including the range are in three digits. I can make this field
with zips as text no problem. So please advise what should be my code
- i am getting a compile error on getideal(zip) function

is there any other SQL query that will work without any code?

Again the three digit zip in table 2 on every row has to search the
range of zips where it falls into and then return the corresponding
city from table1. If the zip in table 2 falls in more than one zip
range in table 1, then it has to look at the least zone no and then
return that corresponding city in table 1. i need to get all rows in
table 2 with an addition column saying- ideal city. Please advise.

for example 500 zip in table 2 may fall into two different ranges in
table 1- (450-700 and 475-800). Say suppose 450-700 has the least zone
no and there fore i need the query to return the city corresponding to
450-700.

Thanks in advance
 
J

John Spencer

Pardon me. The function has a problem in that Db1 is not declared and set.

You should be able to rewrite the function as below. Assumption is that your
table with zip codes is named T1

Function GetIdeal(Zipcode As Integer) As Variant
Dim rs As DAO.Recordset
Dim LowZone As Integer
Dim tempIdeal As String

LowZone = Nz(DMin("Zone", "T1", _
"StartZIP <= " & Zipcode & " AND EndZip >=" & Zipcode))

Set rs = CurrentDb().OpenRecordset("SELECT * FROM T1 WHERE StartZIP<=" _
& Zipcode & " AND EndZip>= " & Zipcode & " AND Zone=" & LowZone)

While Not rs.EOF
tempIdeal = tempIdeal & "/" & rs!city
rs.MoveNext
End

If rs.RecordCount > 0 Then
GetIdeal = Mid(tempIdeal, 2)
Else
GetIdeal = Null
End If



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top