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

 

[JesusFrk]

Justine,

If you combined all of the tables together into a query using the request ID as your Primary key, then all you would have to do is link all of the tables this way together, pull the fields into the query, and use Is Null as the criteria placing it on each subsequent line. For example, for requestor, put "Is Null" on the first criteria line, for date put Is Null on the second criteria line, and so on. Then put the information in a report and you have it.

Granted, this would not put in your specific text, but you could say in your field line of the query
Date Response: =Iif(isnull([Date]), "This record has no date", [Date]) This way you would still have to limit for those specific blank responses.

Hope this helps.

 

[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