Help a newbie with some frustrating reports...PLEASE????

[Stacy]

Hi - I'm working on generating some reports for work, and my deadline
is fast approaching.

I have two tables - tblIssue and tblIssueClose. Both tables contain
identical fields. However, when a user enters information in the
"Close Form" table, it's the only way that the "I_CLOSED" field is
marked from yes to no and when the form is submitted, two queries run,
one which scans the tblIssue table, selects any records that have the
I_CLOSED field marked yes and appends those records to the
tblIssueClose table, and one that deletes all records from the tblIssue
table if they have I_CLOSED marked yes (to simplify, if the Close Form
form is filled out and submitted, it marks the issue closed and moves
it from the active tblIssue table to the archival tblIssueClose table).

When I generate my summary report, though, the report needs to do the
following:

* 1. Generate a report for each specific project (P_ID in either of the
tables)
* 2. Provide a total count of ALL issues, whether open or closed, that
exist for that project. (This is causing me trouble becuase the two
tables aren't linked and there's no logical way TO link them, because
the records aren't really related...)

CLOSED ISSUES
3. Display a count of how many closed issues were accepted.
4. Show a subcount of the accepted closed issues, categorized by the
team member who originated the issue (John entered 2 issues, Becky
entered 0 issues, Sally entered 1...)
5. Display a count of how many closed issues were rejected.
6. Show a subcount of the rejected closed issues, categorized by the
team member who originated the issue (Jack entered 7, Vera entered 0,
Sam entered 1, Buck entered 0...)

OPEN ISSUES
* 7. Display a count of how many open issues have been open for less
than 2 weeks.
8. Display a count of how many open issues have been open for 2-4
weeks.
9. Display a count of how many open issues have been open for more than
4 weeks.
10. Display a count of how many open issues have a critical impact on
the project.
11. Display a count of how many open issues have a high impact on the
project.
12. Display a count of how many open issues have a moderate impact on
the project.
13. Display a count of how many open issues have a low impact on the
project.
14. Show a subcount of the open issues, categorized by the team member
who was assigned to complete the issue (Danny was assigned 3 issues for
the project, Mary was assigned 1 issue for this project, Hal was
assigned 53 issues for this project...)
* 15. Display a count of how many open issues are within 5 days of
their due dates.
* 16. Display a count of how many open issues are past their due dates.

The ones w/ asterisks are the ones I'm having difficulty figuring out
how to do - most of the rest of them should be easy to do once i figure
those out. Especially #2...

If anyone can offer some guidance, it's greatly appreciated!!! It's
entirely possible that all of my problems here result from two things :
the fact that I haven't used SQL in 5 years or Access EVER and the fact
that maybe I shouldn't have a separate archive table (but I was trying
to make it more productive by lessoning the weight on the table). If
that's the case, I need to know that. If the total count can be done
w/ the two tables remaining separate...YAY!!

Anyway...thank you!!

 

[Keith Wilby]

Hi - I'm working on generating some reports for work, and my deadline
is fast approaching.

I have two tables - tblIssue and tblIssueClose. Both tables contain
identical fields. However, when a user enters information in the
"Close Form" table, it's the only way that the "I_CLOSED" field is
marked from yes to no and when the form is submitted, two queries run,
one which scans the tblIssue table, selects any records that have the
I_CLOSED field marked yes and appends those records to the
tblIssueClose table, and one that deletes all records from the tblIssue
table if they have I_CLOSED marked yes (to simplify, if the Close Form
form is filled out and submitted, it marks the issue closed and moves
it from the active tblIssue table to the archival tblIssueClose table).

I've only very quickly read this far as I'm a bit pushed just now but here's
something to consider - use one table with a "closed" flag and use queries
filtered on that flag as a basis for your reports. It'll save all that
faffing around deleting and appending and is how most (if not all) pros
would do it.

Regards,
Keith.
www.keithwilby.com

 

[Stacy]

I would *love* to do it that way, it'd make it so much
easier...however, if I do that, then my Close Issue form will be able
to scroll through every record in the table, including ones that are
already closed (even if I do a filtered combo box to search for
records, the scroll/cursors will still pull every record). I know
there's an application that disables the scroll on the mouse, but I
tried loading it and it won't work for me...

 

[Keith Wilby]

I would *love* to do it that way, it'd make it so much
easier...however, if I do that, then my Close Issue form will be able
to scroll through every record in the table, including ones that are
already closed (even if I do a filtered combo box to search for
records, the scroll/cursors will still pull every record). I know
there's an application that disables the scroll on the mouse, but I
tried loading it and it won't work for me...

You would base your "closed issue" form on a query "Select * from tblMyTable
Where [Closed] = -1" so that all you would see is closed items. Similarly
for open items " ... Where [Closed] = 0". They are not filters, they are
WHERE clauses, so the user cannot show what isn't there.

Regards,
Keith.

 

[Stacy]

That works perfectly until someone uses a scroll mouse. However, I did
a back-end workaround where the fields aren't visible until the user
manually clicks in the Issue Number box and changes the number - so
since scrolling won't work until that's done, I'm hoping it'll
discourage them from using the scroll.

Thanks again for your help!!

 

[Keith Wilby]

That works perfectly until someone uses a scroll mouse. However, I did
a back-end workaround where the fields aren't visible until the user
manually clicks in the Issue Number box and changes the number - so
since scrolling won't work until that's done, I'm hoping it'll
discourage them from using the scroll.

Thanks again for your help!!

You've lost me there ... using a scroll mouse isn't going to change your
recordset. Do you mean that users can see the "Closed" control on the form?
You could always hide it from them.

Keith.