Creating temporary status for report grouping

[Natasha Garcia]

Hi all

Please excuse me if I'm a little wafty in my request, I am very new to Access with a very demanding boss and while I know what I need to achieve, I'm not entirely sure how to do it.

I have a change database and I'm trying to sebsection the results of a query in a report by using the following criteria ...

IF( (“PCN Designation” is VARIATION) AND (“Team to Proceed with Change” is NO) THEN status = VARIATION – PENDING APPROVAL – NOT PROCEEDING

IF( (“PCN Designation” is VARIATION) AND (“Team to Proceed with Change” is YES) AND (“Approval from Client Manager” is NULL) THEN status = VARIATION – PENDING APPROVAL – PROCEEDING

IF( (“PCN Designation” is VARIATION) AND (“Team to Proceed with Change” is YES) AND (“Approval from Client Manager” is NOT NULL) THEN status = VARIATION – APPROVED – PROCEEDING

... the things in " " are fields in various tables.

I'm not too sure where to go from here though. I don't want this status to be a permanently recorded field, just a calculation that the report makes to group the results.

Any ideas?!?

Incidentally, I'm running Access 97

Thanks
Tash

 

[Scottyk537]

Do you have a sample of the database you can POST?

 

[Natasha Garcia]

This is a cut down version of the d/b with 5 records in it.

The report is called PDN Status by Discipline

The query feeding it is PDNPerStatusPerDiscipline

I want to group the variations by the three areas listed above but there are already plenty of statuses in the d/b and I don't want to add another.

I just want to be able to run the report and it calculate from the criteria I mentioned in my OP which of the 3 areas the variations fall into and group them under those sections in the report

Does that make any sense?

Thanks in advance for any help you may be able to give me.

 

[Natasha Garcia]

Does nobody have any ideas? Or am I just not making sense with what I'm asking?

Tash

 

[GregG]

Does nobody have any ideas? Or am I just not making sense with what I'm asking?

Tash

Hi Natasha,

Did you figure this out? If not, I think this will work. Edit the query "PDNPerStatusPerDiscipline" ... move to the field "PCN Designation" and set it's criteria field to "Variation" (based on your specs, you only want Variation records. Then cut and paste the following into a new field called "NewStatus", sort on the field and check the Show checkbox to include it in the query.

--- cut and paste ---
NewStatus: IIf([Team to Proceed with Change]=No,"VARIATION – PENDING APPROVAL – NOT PROCEEDING",IIf(IsNull([Approval From Client Manager]),"VARIATION – PENDING APPROVAL – PROCEEDING","VARIATION – APPROVED – PROCEEDING"))
---------------------

Then in your report, set a group header on the field "NewStatus"




To make it a little easier to read, I shortened the status descriptions so the 3 choices are: No-Null, Yes-Null or Yes-NotNull

NewStatus: IIf([Team to Proceed with Change]=No,"No-Null",IIf(IsNull([Approval From Client Manager]),"Yes-Null","Yes-NotNull"))

So,
if [Team to Proceed with Change] = No then
NewStatus = "No-Null"
elseif [Approval From Client Manager] is null then
NewStatus = "Yes-Null"
else
NewStatus = "Yes-NotNull"
end if

Make sense?