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.