Report with condition

[SylvieB]

Hello all, I need help so badly.
I need to write a report that shows the stage of an opportunity. There are 4
stages (DG1, DG2, DG3, DG4); The report I have created has a column for each
stage. The information that needs to show under each column is customer,
order value and award date that are associated with the stage.
On the form, I created a check box for each stage. So when the opportunity
passes from one stage to another, the user just put a check mark on the box
and the info is recorded on a table with the customer name, order value and
award date. That works great.
On the report, how can I display the customer, order value and award date
that will show only under the correct stage?
Thank you all.

 

[John Spencer MVP]

First of all your structure is incorrect. You should have one record for each
stage where the record contains the stage level and the date and fields to
link back to whatever else you are currently storing in the table. With that
structure you can use a query to find the Max Date and the Max stage.

If you cannot change the design then you will need to calculate the latest
stage and get the appropriate date. If the Stages are always filled in order then

Field: WhichStage: SWITCH(DG4 is Not Null,4, DG3 is Not Null,3, DG2 is Not
Null, 2, DG1 is Not Null, 1, True, Null)

Field: WhichStage: SWITCH(DG4 is Not Null,DG4, DG3 is Not Null,DG3, DG2 is Not
Null, DG2, DG1 is Not Null, DG1, True, Null)

Or for the date you can use
Field: TheAwardDate: Nz(DG4(Nz(DG3,Nz(DG2,DG1)))

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

 

[SylvieB]

Hey John
Thank you so much for your help. I'm still learning Acces pls bear with me.
Each stage is indeed recorded in a table. My problem is when running a report
only the latest stage should display the info. I created a query but don't
know how to write the code to calculate. The codes you suggested below, do u
run it in a query under Criteria?
Thank you John

 

[John Spencer]

Add those two as calculated fields. In other words in a field "block"
enter
WhichStage: SWITCH(DG4 is Not Null,4, DG3 is Not Null,3, DG2 is Not
Null, 2, DG1 is Not Null, 1, True, Null)

That will return a number from 1 to 4 in the query which will tell you
which stage. If you would rather see text like "Stage 4", then replace
the 4 in the expression with "Stage 4" (include the quotes).

The second expression (also entered in a field block) will return the
value stored in the fields DG1 to DG4. It will return the first value
it finds in the fields in this order: DG4, Dg3, Dg2, Dg1.
TheAwardDate: Nz(DG4(Nz(DG3,Nz(DG2,DG1)))


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

 

[SylvieB]

Thank you John. It did not work for me, probably because of the structure of
my database. I went with your first suggestion instead and created only one
field with all conditions with is much easier when you run a query. I however
have one more issue, how do I show the correct data on the report?
the query sorts the correct DG phase per opportunity but i don't know how to
show the stage under the right column on the report? Thank you

 

[John Spencer MVP]

I am guessing that your report has several columns and you want to show the
date under one of the columns. The easiest way might be to use a crosstab
query as the source for the report.

Could you post the SQL of the query you are now using (View: SQL)? And a
sample of the data it is returning.

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