Inserting a number into a criteria

[rpeare]

I'm confused here. You said you were having trouble with a crosstab (possibly) but your 'days_back' form 'SEND' button is referencing a cartesian query, the components of which all have a single field of record counts and all contain 1 record. So where exactly are you getting the error message? I even ran CR_Crosstab and Final Vote Summary and neither of those errored out.

[Thompyt]

They all work fine as is. If I put in the Date_Closed criteria: Between Now() -(forms!Days_Back!Daze) AND Now() in the CR_Crosstab query that is where you will get your error. Even if you drag the CR_Crosstab into the Copy of Sunmary query you will get the same error. Note that when Copy of Summary works, there is no crosstab queriy involved, I took the crosstab query out on purpose because of that.

Another thing I am trying to do is to combine all those single field queries into a smaller package. I have been playing with:

NZ(IIF(Votes="Approved", Count(Votes,0),0) for approved and copying this column for the rest of the the possible votes (deferred/denied/...)

[rpeare]

Ok

1. On your form DAYS_BACK, change the FORMAT of the field DAZE to FIXED (0 decimals)
2. In your query CR_CROSSTAB, open up your PARAMETERS utility
3. In your PARAMETER use [forms].[days_back].[daze], make the DATA TYPE integer

CR_CROSSTAB should now work without a prompt. The problem is that without a defined data type on the form the crosstab query is defaulting to a text value which is causing the prompt because it can't resolve a text value in a mathematical function.

[Thompyt]

Works without error, but whenever I do something with the Report: Change Request Rollup I get a tow popups for Forms!Days_Back!Daze. That is if I change the macro to open the form instead of opening the Copy of Summary query.

Opening the copy of Summary query automatically from the Days_Back form works fine.

[rpeare]

I don't get a pop up when I run the report change request rollup, I do however get a warning that the field [level 3 cat II] doesn't exist in the source query for the subreport COMPLETE the source file of which is a crosstab query that does not contain any values that hit that column.

I do not see why you're doing your report this way though seems like you could sum the data you want in a single query instead of creating a cartesian join of all these small queries doing totals differently.

[Thompyt]

I do not see why you're doing your report this way though seems like you could sum the data you want in a single query instead of creating a cartesian join of all these small queries doing totals differently.

Its because I am moderately new to this and in the learning process. My next step was to minimize the queries for this and have 1 instead of 8 in the sumary.

If you have suggestions; I'm all ears.


Your warning for Cat II is wierd since there is a baseline data to stop that. I'll retry a new set up on another test Db and see what I get.

Tried it again.

[PARAMETERS Forms!Days_Back!Daze Short;
TRANSFORM Nz(Count([CR_ID])-1,0) AS CR_IDs
SELECT Chng_ReqQry.Level, Nz(Count([CR_ID])-5,0) AS [HB Total], Nz(Count([Level])-5,0) AS [Level Totals]
FROM Chng_ReqQry
WHERE (((Chng_ReqQry.Sub_No)=0) AND ((Chng_ReqQry.Date_Closed) Between Now()-Forms!Days_Back!Daze And Now()))
GROUP BY Chng_ReqQry.Level
PIVOT Chng_ReqQry.Votes;

If I do anything with the query, the popup for Forms!Days_Back!Daze shows. If I open it direct, I understand once, but twice? When I save it anywhere where it is linked, I get the same pop up.

[rpeare]

	Count	Criteria
Query	Level	Action_Complete	Change_Requested	Date_closed	Votes	Sub_No	Level
Total Approved		FALSE	<> "Do not delete"	Between Now()-([Forms]![Days_Back]![Daze]) And Now()	Approved	0
Total CRS			<> "Do not delete"	Between Now()-([Forms]![Days_Back]![Daze]) And Now()		0
Total Deferred		FALSE	<> "Do not delete"	Between Now()-([Forms]![Days_Back]![Daze]) And Now()	Defer or Deferred	0
Total Denied			<> "Do not delete"	Between Now()-([Forms]![Days_Back]![Daze]) And Now()	Deny or Denied	0
Total HB			<> "Do not delete"	Between Now()-([Forms]![Days_Back]![Daze]) And Now()		0	Software
Total Pending			<> "Do not delete"	Between Now()-([Forms]![Days_Back]![Daze]) And Now()	Pending	0
Total Withdrawn	is not null	Yes	<> "Do not delete"	Between Now()-([Forms]![Days_Back]![Daze]) And Now()	Withdraw or Withdrawn	0

Ok these are the criteria in your various subqueries
They *all* have the same criteria in Change_Requested, Date_closed and sub_no, why don't you apply those criteria in chng_reqqry? that will significanly ease the formulas in any summary query.

Secondly, you are going to have overlap (potentially) in your data unless you are have pretty strict validation checks on your data entry.

For instance TOTAL CRS is basically going to count everything, it will overlap with every other summary query you have.
Total HB could also overlap with all the other subqueries, it will count every instance where the level is 'software' regardless of where it is in the VOTES process. So let's say you had two deferred items, one that was FALSE where the level was software, one that was TRUE where the level was software, your total HB would give you a count of 2 because it is not taking into account the ACTION_COMPLETE field. It seems odd you would have potentially this much overlap between categories. is it what you intended?
all of your subqueries plus the crosstab CR_Crosstab have criteria picking out the sub_no being 0 and the daterange being identical, but your subequeries also are looking for the Change_Requested of anything but 'do not delete', should you CR_Crosstab also have the same criteria?

[Thompyt]

I use those as baseline for the count. There are 5 instances of each level
Level 1
Level 2
Level 3 Cat I
Level 3 Cat II
Software
I either subtract 5 in the count or I deliniate those by criteria of <>"Do not delete" or Sub_No <>0. This way when I do the Crosstab each level always shows up. I do have those in the Chng_ReqQry. TTL CRs is because I have to track the number of HB changes and the number of Software level CR's.

Sub_No being 0 in one takes out all the sub CR's and deals only with those. Depending on the audience level of the report, some want to see the Sub CR's, and some do not. At the crosstab, we do only want to count the "Main" CR's, not the underling CR's.

With the subqueries, this was the best way I knew how to ensure the correct count. TTL Cr's I want the count no matter if it is a software, in TTL HB I have the criteria <>"Software"

I was looking at the following to get my totals: NZ(IIF(Votes="Approved", Count(Votes),0),0) But I have to show the votes. I would like to get a single line.

[rpeare]

Votes
Approve
Approved
Deferred
Denied
Pending
Withdrawn

As a note these are your 'votes' values notice you have an 'approve' so your formula will not pick this value up because it's looking for 'approved'

I would create a table with a PK (primary key) and a vote status and store the vote status as a FK in your change request table, that way you will end up with a consistent value for each vote status

Now

Change your query Chng_ReqQry SQL to:

SELECT Format(([CR_No]+([Sub_No]*0.01)),"Fixed") AS CR_Number, IIf([Sub_No]=0,Format([CR_No],"  #"),Format([Sub_No]*0.01,"    " & "   .00")) AS CR_Numbers, [Change Request].Change_Type, [Level] & Chr(13) & Chr(10) & [Soft Level] AS Levels, [Soft Level] & " " & [Level] AS Levelz, [Unit] & Chr(13) & Chr(10) & [Section] AS Units, [HB_Version] & Chr(13) & Chr(10) & [Approx_Page] AS [HB Vers], [MTOE_Para] & Chr(13) & Chr(10) & [Bumper_Number] AS [MTOE Paras], [Change Request].[Change Requested], [Change Request].Rationale, [Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People, [Change Request].AO_Vote, [Change Request].O6_Vote, [Change Request].GO_Vote, [Change Request].Final_Vote, (IIf(Not IsNull([Final_Vote]),([Final_Vote]),(IIf(Not IsNull([GO_Vote]),([GO_Vote]),(IIf(Not IsNull([O6_Vote]),([O6_Vote]),([AO_Vote]))))))) AS Votes, (IIf(Not IsNull([Final_Vote]),([Final_Vote]),(IIf(Not IsNull([GO_Vote]),([GO_Vote]),(IIf(Not IsNull([O6_Vote]),([O6_Vote]),(Switch([AO_Vote]="Approve","Approved",[AO_Vote]="Withdraw","Withdrawn",[AO_Vote]="Deny","Denied",[AO_Vote]="Deferred","Deferred")))))))) AS Votes1, Switch([GO_Vote]="Approved","GO Approved",[O6_Vote]="Approve","CCB Approved",[O6_Vote]="Deferred","CCB Deferred",[AO_Vote]="Deferred","AO Deferred",[O6_Vote]="Denied","CCB Denied",[AO_Vote]="Approve","AO Approved",[AO_Vote]="Denied","AO Denied",[AO_Vote]="Withdraw","AO Withdrawn",[O6_Vote]="Withdraw","CCB Withdrawn",[O6_Vote]="Hold","CCB Hold",[AO_Vote]="Hold","AO Hold",[AO_Vote]="Open","Open") AS Status, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].Action_Complete, [Change Request].Date_Input, [Change Request].Date_Closed, [Change Request].Priority, [Change Request].Hr, [Change Request].NIE, [Change Request].Level, [Change Request].Date_ID, [Change Request].CR_ID, [Change Request].Sub_No, [Change Request].CR_No, [Change Request].[Soft Level], [Change Request].Unit, [Change Request].Section, [Change Request].HB_Version, [Change Request].MTOE_Para, [Change Request].Bumper_Number, [Change Request].Requestor, [Change Request].SponsorFROM [Change Request]
GROUP BY Format(([CR_No]+([Sub_No]*0.01)),"Fixed"), IIf([Sub_No]=0,Format([CR_No],"  #"),Format([Sub_No]*0.01,"    " & "   .00")), [Change Request].Change_Type, [Level] & Chr(13) & Chr(10) & [Soft Level], [Soft Level] & " " & [Level], [Unit] & Chr(13) & Chr(10) & [Section], [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].AO_Vote, [Change Request].O6_Vote, [Change Request].GO_Vote, [Change Request].Final_Vote, (IIf(Not IsNull([Final_Vote]),([Final_Vote]),(IIf(Not IsNull([GO_Vote]),([GO_Vote]),(IIf(Not IsNull([O6_Vote]),([O6_Vote]),([AO_Vote]))))))), (IIf(Not IsNull([Final_Vote]),([Final_Vote]),(IIf(Not IsNull([GO_Vote]),([GO_Vote]),(IIf(Not IsNull([O6_Vote]),([O6_Vote]),(Switch([AO_Vote]="Approve","Approved",[AO_Vote]="Withdraw","Withdrawn",[AO_Vote]="Deny","Denied",[AO_Vote]="Deferred","Deferred")))))))), Switch([GO_Vote]="Approved","GO Approved",[O6_Vote]="Approve","CCB Approved",[O6_Vote]="Deferred","CCB Deferred",[AO_Vote]="Deferred","AO Deferred",[O6_Vote]="Denied","CCB Denied",[AO_Vote]="Approve","AO Approved",[AO_Vote]="Denied","AO Denied",[AO_Vote]="Withdraw","AO Withdrawn",[O6_Vote]="Withdraw","CCB Withdrawn",[O6_Vote]="Hold","CCB Hold",[AO_Vote]="Hold","AO Hold",[AO_Vote]="Open","Open"), [Change Request].NOTES, [Change Request].Action_Items, [Change Request].Action_Complete, [Change Request].Date_Input, [Change Request].Date_Closed, [Change Request].Priority, [Change Request].Hr, [Change Request].NIE, [Change Request].Level, [Change Request].Date_ID, [Change Request].CR_ID, [Change Request].Sub_No, [Change Request].CR_No, [Change Request].[Soft Level], [Change Request].Unit, [Change Request].Section, [Change Request].HB_Version, [Change Request].MTOE_Para, [Change Request].Bumper_Number, [Change Request].Requestor, [Change Request].Sponsor, [Change Request].Approx_Page, [Change Request].Unit, [Change Request].Section, [Change Request].HB_Version, [Change Request].MTOE_Para, [Change Request].Bumper_Number, [Change Request].Requestor, [Change Request].Sponsor
HAVING ((([Change Request].Date_Closed) Between Now()-CInt([forms]![days_back]![daze]) And Now()) AND (([Change Request].Sub_No)=0));

Next, create this query:

SELECT Sum(IIf(([Action_Complete]=0 Or IsNull([Action_Complete])) And [votes] Like "Approv*",1,0)) AS TotalApproved, Count(Chng_ReqQry.cr_number) AS [Total CRS], Sum(IIf(([Action_Complete]=0 Or IsNull([Action_Complete])) And [votes] Like "Defer*",1,0)) AS [Total Deferred], Sum(IIf([votes] Like "Den*",1,0)) AS [Total Denied], Sum(IIf([level] Not Like "softw*",1,0)) AS [Total HB], Sum(IIf([votes] Like "Pend*",1,0)) AS [Total Pending], Sum(IIf([votes] Like "With*" And [Action_Complete]=-1 And Not IsNull([Level]),1,0)) AS [Total Withdrawn]
FROM Chng_ReqQry
WHERE (((Chng_ReqQry.[Change Requested])<>"DO NOT DELETE"));

This should eliminate the need for all the 'sub' queries that are just running record counts. I'm not going to address the rest of it because I still don't understand the desired results but hopefully this will make it a bit easier.

[Thompyt]

Thanks, I'll for sure use that new Query with some mild changes. I'll have to dig into the Chng_ReqQry one and see whats going on. Much appreciated!