Number Report Record

[koolwaters]

Hi!

I have a form that is used to log issues that a client has. This form contains a subform which links an issue with the actions associated with that particular issue. One issue can have many actions hence the subform. I have a button on the main form which is used to print the current issue and any related actions. The report contains a textbox with an overall running sum to number each action of that particular issue. This works perfectly.

I also have a button on the subform to print the selected action on the subform. I use the criteria stCriteria = "[IssueActionID]=" & Me.IssueActionID to preview the report for that particular action. This works with the exception of my running sum.

Lets say IssueID 1 has 5 associated Actions. Depending on when the associated Action was created, the ID field is not always consecutive. For example ActionID 1 may have related IssueActionID 1, 2, 3, 4, 7 and IssueID 2 may have IssueActionID 5,6.

Let's say I want to print the the last action for IssueID 1, when I print the report, I want to see 5 for the numbering, as that is the 5th action of that item. With the running sum, what I am getting is 1 for the numbering.

Any suggestions?

Thanks in advance for any assistance.

 

[Micron]

Without seeing and knowing more about your underlying schema, I'd say getting the Max(action id) WHERE the Issue ID = [what's on the main form] would be the way to go.
Sorry if that's vague, but it's based on the info I was able to glean from what you have.

 

[koolwaters]

I do not want the highest Action ID based on the Issue.

I have a table called tblIssue and a table called tblAction. There is a one-to-many relationship between these two tables using the IssueID. (Think of it as logging calls to customer. A call is linked to a customer and each time a call is made to a customer a sequential log is kept of each call made.)

Let's say a client calls in with an Issue. It is the first issue being created in the database and is assigned Issue ID 1. This issue has to be actioned by a member of staff and each action is assigned an ID. The issue has been actioned twice and is numbered Action ID 1, Action ID 2. A second client then calls in with an issue, which is assigned Issue ID 2. Issue ID 2 has 4 related actions- Action ID 3, Action ID 4, Action ID 5, Action ID 6. A staff member then performs an action on the first issue, and this action is assigned Action ID 7.

Issue ID 1 has three related actions- Action ID 1, Action ID 2, Action ID 7
Issue ID 2 has four related actions- Action ID 3, Action ID 4, Action ID 5, Action ID 6

I have a report with the Issue as the main report and the Actions as a subreport. I number each action using the running sum property. This works perfectly when printing the Issue and all its related Actions. What I want, is to be also able to print just one action of an issue and that is where the problem arises. For example, if I want to print Action ID 7, I want my numbering to display 3. Even though it is the seventh action in the Action table, it is the third action of Issue ID 1. If I want to print Action ID 2, I want my numbering to display 2, etc.

I hope this gives you some clarity as I am still unable to get it to work.

 

[Micron]

I've never tried to get the value of a running sum textbox from any particular record of a report. I doubt you can, since it's a value that's dependent on prior records, but I'm not positive. I guess you could only try. What I think might work is along the same lines of what I already suggested, but get the count instead:
Count([Action ID]) WHERE [Issue ID] = 1 AND [Action ID] <= 7
So for Issue ID 2, the count would be 3 if the Action ID of the record to be printed is 5. 5 is the 3rd action for issue 2.
What I don't know is how you'd implement this since you haven't said how you're isolating one record from a report. I presume you want to open a report with the single record, which means you'd have a query to load one record. You may be able to add a calculated field to this query that gets the count from your joined tables as well as put the above mentioned criteria on that field.
Hope that helps to at least point you in the right direction.

An afterthought: maybe a textbox on the report (can be made hidden if need be) that = the running sum textbox. It might be a static value for each record based on the calculated value of the running sum.