?
Solved

Group Header on Report: odd and even house numbers

Posted on 2008-11-03
7
Medium Priority
?
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 101

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 101

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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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