Report creation help

[JNariss]

Hello,

I have a database that works great. Well, it can't be that great if I'm
here right???

Anyways...here's what happens:

1. User fills out a form which creates a Request w/ a Request_ID (goes
into tblRequest)

2. Manager approves this request and assigns and Analyst (goes into
tblAnalyst)

3. Analyst does his/her job and the request has actions (actions go
into tblRequestActoins)

4. One more table: tblProblems - this is where the Analyst records any
testing problems on the request they have been assigned.

I would like to create a report that brings all these tables together
but will display somehow if one of the Request_ID's has not been in
that table.

For Example I would like the report to do something like this:

Request ID: 354
Requestor: Jim Bob
Date: 03/28/2006
Move # : THIS REQUEST DOES NOT HAVE A MOVE NUMBER
Submitted To Production By: Jenny O
Testing Notes: THIS REQUEST DOES NOT HAVE ANY TESTING NOTES


So if a request does not have a particular field in the table or a
record in a table then I want the response similar to above.

How can I do this???

Thanks,
Justine

 

[Marshall Barton]

I have a database that works great. Well, it can't be that great if I'm
here right???

Anyways...here's what happens:

1. User fills out a form which creates a Request w/ a Request_ID (goes
into tblRequest)

2. Manager approves this request and assigns and Analyst (goes into
tblAnalyst)

3. Analyst does his/her job and the request has actions (actions go
into tblRequestActoins)

4. One more table: tblProblems - this is where the Analyst records any
testing problems on the request they have been assigned.

I would like to create a report that brings all these tables together
but will display somehow if one of the Request_ID's has not been in
that table.

For Example I would like the report to do something like this:

Request ID: 354
Requestor: Jim Bob
Date: 03/28/2006
Move # : THIS REQUEST DOES NOT HAVE A MOVE NUMBER
Submitted To Production By: Jenny O
Testing Notes: THIS REQUEST DOES NOT HAVE ANY TESTING NOTES


So if a request does not have a particular field in the table or a
record in a table then I want the response similar to above.

The key to this kind of thing is to create the proper query
to retireve the needed data. The report's job is format the
data into a presentable display.

If your query does not contain the required data, it's going
to be difficult to get the report to invent the data ;-)

If the necessary records are retrieved by the query, but
contain a Null instead of the text you want to display, then
just set the report text boxes to use an expression like:

=Nz([Move #], "THIS REQUEST DOES NOT HAVE ...")

 

[JNariss]

Thanks Marsh I will look into a proper query for this - would it be
along the lines of a left outter join? and I will try the code in the
report once the query works.

-Justine

 

[Marshall Barton]

Thanks Marsh I will look into a proper query for this - would it be
along the lines of a left outter join? and I will try the code in the
report once the query works.

Outer joins sound very appropriate if you want to list all
requests when there might not be an analyst.

I think the general idea of the query might be something
along these lines:

SELECT tblRequest.Request, . . .,
tblAnalyst.Analyst, . . .,
tblRequestActions.Action, . . .
FROM tblRequest LEFT JOIN
(tblAnalyst LEFT JOIN tblRequestActions
ON tblAnalyst.AnalystID = tblRequestActions.AnalystID)
ON tblRequest.RequestID = tblAnalyst.AnalystID