Need help to seperate odd and even numbers using queries 2

[axemen]

Hi,

I have a table containing street_no and street_name, I need to do a query and find out what type of numbers are there in a street_name. For example: Commercial St. contains both odd and even numbers, i have to create another field in the query making it to show mixed, and if Commercial St. contains only even numbers without odd numbers , i have to make it shows even, and the same for odd numbers.

Here is a layout example that i have to show on the report:

Street Order

Commercial St mixed
Bay St even
Sunset Ave odd

I tried a lot of ways but just can't seem to make it work, hope someone can help me figure out the problem, thanks!

 

[MichaelRed]

not much to go on in your post. please amplify a bit. do you ahve sample addresses for each/every street? do you want to have a dynamic generation of the oddness/evenness of the street or the ndividual addresses? Etc.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[essnrv]

What is the criteria to decide mixed, even and odd ? Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[cmmrfrds]

Create a function and pass the address to the function.

Like.

Function OddEven(addr As String) As String
Dim indx As Integer
Dim odd1 As String, even1 As String, mixed1 As String
Dim holder As String, pos As Integer

'-- test data
' addr = "123 commercial street"
' addr = "246 anderson road"
' addr = "579 marshall court"
' addr = "mary lane road"
For indx = 1 To 50 '-- assume 50 character field
holder = Mid(addr, indx, indx + 1)
If IsNumeric(holder) Then
pos = CInt(holder)
If (pos Mod 2) = 0 Then
even1 = "even"
Else
odd1 = "odd"
End If
End If
Next

If even1 = "even" Then
If odd1 = "odd" Then
OddEven = "mixed"
Else
OddEven = "even"
End If
Else
If odd1 = "odd" Then
OddEven = "odd"
Else
OddEven = "neither"
End If
End If
Debug.Print OddEven

End Function

 

[axemen]

Hi,

There are 3 fields in this table: District, Street_no and Street_name, here is an example of the data:

1,2,4,5,7,8,9,10,11,12,13,15,18 Commercial St. District 9
2,4,6,8,12,16,20,24,28,30 Bay St. District 9
1,3,5,7,9,15,19 Bay St. District 8
33,37,39,45,47,51 Church St. District 9


I have to generate a report that shows:

District 8

Street Order
------- -------
Bay St. odd

District 9

Street Order
-------------- -------
Bay St. even
Church St. odd
Commercial St. mixed


Hope this help makes the question a little clear to understand.

 

[zerosandones]

The only question I had from your post is how the street numbers were stored in your table. I've assumed that the primary key to your table is the combination of District, Street_Name and Street_No and furthermore that Street_No is numeric - I assume that you only displayed your comma delimited street numbers for ease of presentation, not that the data is actually stored that way in a text field ... so, my sample stored data is as follows:

District Street_Name Street_No
D1 S1 1
D1 S1 2
D1 S2 1
D1 S2 3
D1 S2 5
D2 S3 2
D2 S3 4

The SQL required to summarize results as you've requested is relatively simple:

SELECT tblAddress.District, tblAddress.Street_Name, Switch(Max([Street_No] Mod 2)=1,Switch(Min([Street_No] Mod 2)=0,"Both",True,"Odd&quot,True,"Even&quot AS [Order]
FROM tblAddress
GROUP BY tblAddress.District, tblAddress.Street_Name;

In it, you'll see that I use the Mod operator to divide each street number by the value 2 to categorize each record as being odd or even. I then look at each district/street combination and perform the aggregate Min and Max functions to determine whether an odd and/or even street number exists. The final logic determines whether or not only odd or even or both conditions exist.

Hope this does it for you.

00001111s

 

[axemen]

Thanks zerosandones, it works!!

 

[MichaelRed]

... and others would easily been able to provide a soloution if you would post the request without the need to ASS-U-ME the structures.

Your 'shorthand' notation was not just NOT helpful, but was quite misleading.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places