Building an Activity report

[Andy_Khosravi]

I'm having trouble building a query to gather the information I need
from a table. I need to be able to assemble an inventory report that
displays receipts, output, and running inventory from a table named
tblHistory.

My setup:
I am running A97. I have one table called tblIssues linked in a
one-to-many-relationship with a table called tblHistory. The tblIssues
table contains the issue header information, while the tblHistory table
lists off all the different departments an issue has been to as it
progressed towards resolution.

**tblHistory field names**
IssueID (foreign key)
HistID (primary key)
Department
DateBegin
DateEnd

When an "issue" is created on tblIssues, a record is automatically
created in tblHistory, which captures the current user department and
time stamps the DateBegin field. When they 'move' it to another
department the DateEnd field is filled in on tblHistory. A new record
is then created with the appropriate department name and DateBegin
field is time stamped on the new record. This process continues as many
times as necessary until the 'issue' is closed, at which point the
DateEnd field is filled in and no new records created.

This seems to work fine in practice, as users are able to determine
where an 'issue' originated, where it's been, and where it currently
resides. My problems start when I try to create an activity report,
which needs to show a breakdown of Received, Output, and Running
Inventory by Month within department.

The report headers would look something like this:
Department
Month
Received, Output, Running Inventory

I can create a report that shows received easily enough by querying the
department and DateBegin fields. I can also create a report that shows
the Output by querying the department and the DateEnd fields. What I
can't figure out how to do is how to put both of those figures on a
single report so that I can show inputs AND outputs within a month. The
tricky part is what data field to use to populate the 'Month' part
since I don't want this to be by DateEnd or by DateBegin, but buy
both. It seems to me there is probably an easy way to do this, and I'm
just not getting it.

Any ideas or comments would be greatly appreciated.

[Larry Linson]

You didn't define Received, Output, or Running Inventory. Do those represent
simply the number of Issues records received by the Department, completed by
the Department, and Received but not yet Completed by the Department?

You do have to understand that _you_ know your business/organization's needs
far better than we, so we can't tell you how to group by month. It could
mean "number of issues received just this month", "number of issues
completed just this month", and "number of issues received in any month but
not yet completed", but it is not _necessarily_ those definitions that will
be helpful for your report.

You need to look at the USE to which that report will be put, and, quite
possibly, confer with the users to determine what _they_ need and want from
the report.

Larry Linson
Microsoft Access MVP

<An***********@ bcbsmn.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com.. .

I'm having trouble building a query to gather the information I need
from a table. I need to be able to assemble an inventory report that
displays receipts, output, and running inventory from a table named
tblHistory.

My setup:
I am running A97. I have one table called tblIssues linked in a
one-to-many-relationship with a table called tblHistory. The tblIssues
table contains the issue header information, while the tblHistory table
lists off all the different departments an issue has been to as it
progressed towards resolution.

**tblHistory field names**
IssueID (foreign key)
HistID (primary key)
Department
DateBegin
DateEnd

When an "issue" is created on tblIssues, a record is automatically
created in tblHistory, which captures the current user department and
time stamps the DateBegin field. When they 'move' it to another
department the DateEnd field is filled in on tblHistory. A new record
is then created with the appropriate department name and DateBegin
field is time stamped on the new record. This process continues as many
times as necessary until the 'issue' is closed, at which point the
DateEnd field is filled in and no new records created.

This seems to work fine in practice, as users are able to determine
where an 'issue' originated, where it's been, and where it currently
resides. My problems start when I try to create an activity report,
which needs to show a breakdown of Received, Output, and Running
Inventory by Month within department.

The report headers would look something like this:
Department
Month
Received, Output, Running Inventory

I can create a report that shows received easily enough by querying the
department and DateBegin fields. I can also create a report that shows
the Output by querying the department and the DateEnd fields. What I
can't figure out how to do is how to put both of those figures on a
single report so that I can show inputs AND outputs within a month. The
tricky part is what data field to use to populate the 'Month' part
since I don't want this to be by DateEnd or by DateBegin, but buy
both. It seems to me there is probably an easy way to do this, and I'm
just not getting it.

Any ideas or comments would be greatly appreciated.