Help with Access Reports

  • Thread starter Thread starter Adodds
  • Start date Start date
A

Adodds

Trying to design a report and want to group records together based on
age range. Is this possible. Fairly new to Access and would
appreciate any help someone could provide.

Aj.
 
Aj,

Yes, this can be done, and it will probably be fairly straightforward.
You need to use the Sorting and Grouping facility in the report design.
See under the View menu. The grouping will be done based on an
expression that you enter into the Sorting and Grouping dialog, or on a
field, if necessary a calculated field, that is included in the query
that the report is based on.

If you need more specific help with this, you will need to let us know
some more details, including examples, of your data, how you want it
grouped, how you would know which age range a given record belongs to
(i.e. on the basis of which existing data), and how you want it to look.
 
I currently have an expression that returns a persons Age based on
their birthdate. I am trying to group these individual Age records,
i.e. Less than 1; 1-5yr, 6-10yr. etc.; to be able to apply Discounts
based on each age range. I am probably making this more difficult than
what it actually is.

I have used the grouping and sorting feature before, but do not see
where you can set the specific criteria that I need. Is this
something that should actually occur in the query itself??

AJ - R.Black Conceptual Design
 
AJ,

You could, in the query that the report is based on, make a calculated
field for the discount break. Here's one approach...
Discount: Switch([Age]<1,0,[Age] Between 1 And 5,.025,[Age] Between 6
And 10,.05,.... etc)
.... and then you could use this field for the grouping in your report.
Does that do what you want?
 
If your ranges are all in groups of 5, you can use a little integer arithmetic
to calculate groups.

(Age+4)\5

0 --> 0
1 to 5 --> 1
6 to 10 --> 2
11 to 15 --> 3
etc.




Steve said:
AJ,

You could, in the query that the report is based on, make a calculated
field for the discount break. Here's one approach...
Discount: Switch([Age]<1,0,[Age] Between 1 And 5,.025,[Age] Between 6
And 10,.05,.... etc)
... and then you could use this field for the grouping in your report.
Does that do what you want?

--
Steve Schapel, Microsoft Access MVP
I currently have an expression that returns a persons Age based on
their birthdate. I am trying to group these individual Age records,
i.e. Less than 1; 1-5yr, 6-10yr. etc.; to be able to apply Discounts
based on each age range. I am probably making this more difficult than
what it actually is.

I have used the grouping and sorting feature before, but do not see
where you can set the specific criteria that I need. Is this
something that should actually occur in the query itself??

AJ - R.Black Conceptual Design
Click to expand...
Click to expand...
 
There are a couple other methods that should be considered.
- create a table of age ranges with the appropriate discount
tblAgeDiscounts
MinAge
MaxAge
Discount
This method allows you to use data rather than complex expressions that
might change
- create a user defined function that accepts the age value and returns the
discount.
This method keeps your "business rules" calculation in a single module
rather than in a query.

I much prefer and recommend the data method.

--
Duane Hookom
MS Access MVP


Steve Schapel said:
AJ,

You could, in the query that the report is based on, make a calculated
field for the discount break. Here's one approach...
Discount: Switch([Age]<1,0,[Age] Between 1 And 5,.025,[Age] Between 6
And 10,.05,.... etc)
... and then you could use this field for the grouping in your report.
Does that do what you want?

--
Steve Schapel, Microsoft Access MVP
I currently have an expression that returns a persons Age based on
their birthdate. I am trying to group these individual Age records,
i.e. Less than 1; 1-5yr, 6-10yr. etc.; to be able to apply Discounts
based on each age range. I am probably making this more difficult than
what it actually is.

I have used the grouping and sorting feature before, but do not see
where you can set the specific criteria that I need. Is this
something that should actually occur in the query itself??

AJ - R.Black Conceptual Design
Click to expand...
Click to expand...
 
Back
Top