473,322 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Building an Activity report

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.

Jan 12 '06 #1
1 1882
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.googlegr oups.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.

Jan 12 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: skn | last post by:
Hello, Is there any option to suppress the JVM activity report that gets displayed, when you execute Java APIs from within Python using JPype. E.g., JVM activity report : classes...
1
by: Sandy | last post by:
We have a web report that times out after 20 minutes if there has been no activity on the report. The timeout is set at the application level and I am unable to get this adjusted. I would like to...
13
by: i. Wiin | last post by:
I've got a daily based crystal report that doesn't show 0's for days where no activity occurred. How do I get 0 values to show up in the report?
3
by: wsox66 | last post by:
I am new to Access and need some help building a report. I have looked through previous posts on reports but none of them seem to answer my question completely. I am using Access 2003 and I have...
6
by: wugon.net | last post by:
Hi , Anyone know how to monitor db2 trigger activity ? We suffer some trigger issue , and we try to monitor trigger's behavior use event monitor and db2audit, but both tools can not get...
4
by: stpark22 | last post by:
Hello, I'm building a tracking tool using Access 2003, where my users will be entering orders that includes quantity of products sold, and cost per unit. I'm in the process of building several...
1
by: mirandacascade | last post by:
Apologies in advance...I'm sure this is a trivial question... Access 97. Situation is this: 1. VBA code sets the recordsource property of a report object; the recordsource is a querydef which...
1
by: cdhaynes | last post by:
Hi, I am using unbound text boxes in the header of a form to allow the user to search on a number of fields. I am using VBA to build a query string based on these and it has been fine when comparing...
4
by: Michel Esber | last post by:
Environment: DB2 v8 LUW FP 15 running on Linux. For some reason that I can´t explain, a simple insert statement on a table may run very quickly, or may take forever (20-30mins) to finish....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.