Solved

Group Header on Report: odd and even house numbers

Posted on 2008-11-03
7
490 Views
Last Modified: 2013-11-28
Hello,

I have a report of streets in a city. I have a group header for each street. The group header needs another group heading which will organize the street with odd house numbers first and after the odd numbers I need a new grouping for even house numbers.

How can this be done? The datasource is an sql statement on a table in sql server.

Thanks
0
Comment
Question by:gilalig
7 Comments
 
LVL 17

Expert Comment

by:aflockhart
ID: 22868083
Are the numbers all genuinely numeric  ?

If so, you can calculate a 'parity' value for each one, and group your report using that parity value:  ( odd numbers have Parity=1, evens have parity =0)

select housenumber , housenumber- floor(housenumber/2)*2 AS Parity from addresses


But having done similar things in the past, there are probably other complications waiting to get you ... houses like 10B or 12-3  ... no easy way to deal with these.

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22869570
You can also do it in a Crystal formula

If {HouseNumberField} mod 2 = 1 then
    'ODD'
else
    'EVEN'

YOu can then group the report on the formula

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22869573
If you have housenumbers that are not purely umeric then the formula can be modified to handle them

mlmcc
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22871644
gilalig,

What platform do you need this in please?

You have listed both the Access Reports and Crystal Reports, zones to this question.

JeffCoachman
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 22881498
gilalig,

Here it is in Access:

JeffCoachman

Access-EEQ23871009GroupByOddAndE.mdb
0
 

Author Closing Comment

by:gilalig
ID: 31512725
I used a view in SQLServer so the Mod is not available but rather [house] % 2. Thanks for the example!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22900172
gilalig,

OK,

If this was an Access question then can I ask why you added the Crystal Reports Zone to the question?

I believe that the first two Experts presumed that this was specifically a Crystal Reports Question.

JeffCoachman
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question