Problem inserting fields into subreport...

[Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+]

I have a report that has a subreport in it. The subreport has two
fields that store a value that tracks the status of map changes. We
have digital maps and paper maps and need to track the status of a
change as to whether or not it has been done on the paper maps and/or
the digital maps. I want the report to show the verbose meaning of
these values (stored in a separate table). Let me set this up a bit...

Table: MapChange
Field: ChangeID
Field: Description

Table: ChangeDetail
Field: DetailID
Field: ChangeID (links this detail to MapChange.ChangID - the
MapChange can have many ChangeDetails)
Field: Description
Field: PaperStatusID
Field: DigitalStatusID

Table: DetailStatus
Field: StatusID
Field: Description

Now, the idea is that we enter a general map change request into
MapChange. Then, the mapping person would enter the specific changes
made to the map(s) in ChangeDetail. The mapper may change the paper
map, but not the digital map, vice versa, or neither. Later they can
run this report to determine where there are mapping changes that
still need to be completed. The PaperStatusID and DigitalStatusID
relate to the DetailStatus.StatusID field. I want the Description of
DetailStatus to show on the report instead of Paper/DigitalStatusID.

The problem is that I can't add the description in a way that it
shows for both of these fields. When in design mode of the report, I
go to Add Existing field, choose Description from DetailStatus, it
brings up a dialog to setup the fields to join on. I choose the
StatusID field and the PaperStatusID field, and then it adds the
Description field fine. This one works fine and shows the proper text
based on the PaperStatusID. However, this sets up something like a new
field in the Add Existing Field list. I then can't do this process
agiain the setup a similar link for StatusID and DigitalStatusID. How
do I get this to work?

Thanks in advance and be patient, I'm still new to working with
Access. BTW, this is Access 2007. Thanks!

 

[Jeff Boyce]

Andrew

It sounds like you are adding the "ID" field, so I'm guessing your report
(and subreport) are pointed directly at the underlying tables.

Instead, create a query that returns everything you want (in your case,
you'd join the "main" table to the "lookup" tables that contain the "verbose
description" and include those fields.

Now point your report (and subreport) to the queries instead of the tables.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+" <[email protected]>
wrote in message

 

[Andrew Meador]

I don't know how to create a querry that would generate the
contents of this report. I can pull the fields from the main MapChange
table, but I don't know how to also pull the fields from the other two
tables and relate everything properly for the subreport part of this.

The report shows the general map change request and then shows the
unknow number of ChangeDetails related to the MapChange immeditatly
following this. Also, I am not adding the ID field, I am adding the
Description field of DetailStatus and linking this where PaperStatusID
equals StatusID and trying to do the same thing where DigitalStatusID
equals StatusID. However, once I setup the relationship for the first
field, it won't let me do the same setup again for the
DigitalStatusID. When I try to add Description (from DetailStatus)
again to the subreport it just puts it right in and doesn't let me do
it based on DigitalStatudID being equal to StatusID. In the
SQLServerExpress database that this is all working from, there are two
relationthips setup between DetailStatus and ChangeDetail, once for
each of these links, but it's like Access doesn't see both
relationshships.

 

[Jeff Boyce]

Andrew

Create a new query in design view. Add the main table. Add the table with
the descriptions. Join the two tables on their common field (?the ID
field).

Select the fields from each table you wish to have in the report. This
would include the description field.

Save the query.

Open your report in design view. Open the Properties window for that
report. Where it now says/points to the table, change it to point to your
new query. Now your report will "have" the fields you want to show.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Andrew Meador]

Unless I'm missing something, this would not work for the subreport.
This problem is specifically related to a subreport that is contained
within the main report. How do I do a single querry that also
generates the subreport? The only way I have been able to do that is
SQL coding using cursors directly in SQL Server Express or Visual
Studio coding.

The flow of my report is:

General Map Change Request 1
Specific Map Detail 1
Specific Map Detail 2
General Map Change Request 2
Specific Map Detail 3
Specific Map Detail 4
Specific Map Detail 5
General Map Change Request 3
Specific Map Detail 6
General Map Change Request 4
Specific Map Detail 7
Specific Map Detail 8
Specific Map Detail 9
Specific Map Detail 10
..
..

The problem I am having is getting these two look-ups accomplished
in the Specific Map Detail section.
Here's a short example:

MapChangeID: 001 Description: Add New Parcel Date Entered:
10/10/1008 (General Map Change Request)
Digital Maps Changed: Done Paper Map Changed: Not Done
Description: Added boundary lines for new parcel (Map Change Details 3
lines)
Digital Maps Changed: Done Paper Map Changed: Done Description:
Changed remaining acreage of parent parcel
Digital Maps Changed: Not Done Paper Map Changed: Done
Description: Removed dwelling mark from parent parcel (torn down just
prior to sale)
MapChangeID: 002 Description: Remove Dwelling Mark Date Entered:
10/11/2008 (General Map Change Request)
Digital Maps Changed: Done Paper Map Changed: Not Done
Description: Removed dwelling mark
....

The problem is getting the Done, Not Done, etc... entries to show as
text (the numeric value is stored in the table and a lookup needs to
be done in another table to get the text equivelant). However, since
this part of the report is a SUBreport, I don't know how to do this
strictly as a query.

I hope this helps clarify my problem better.

 

[Jeff Boyce]

Why are you limiting yourself to a single query?

If you had two separate reports, you'd use two separate queries, right?

Well, a "main report" is one report, and a "sub report" that you embed
within the main report is just another report.

Use two.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Andrew Meador]

But that's my point, I don't know how to do the second querry. The
subreport querry would have to be based on the current iteration of
the main report querry. I know how to make the subreport be base don a
querry verses a table(s), but I don't know how to write this querry.
Like I've said, I have done this using cursors in code, but I don't
know how to do this in Access. I'm not real good at SQL yet. I don't
think I know how to do this querry in more than one way. One I don't
know how to make it be based on the current record/iteration of the
main report querry and two I don't know how to do joins (I guess
joins) for the two fields that need to be looked up from the table
with the verbal desciptions in it. Multiple joins in the querry? Based
on the suedo sample I gave you above, what would that querry look
like?

 

[Jeff Boyce]

Andrew

I may not have a complete enough picture, but here's how I would approach
it...

It sounds like there's a "main" table and a "detail" table. Some of the
"detail" data is just IDs, "looked up" from yet another table.

I would create one query that returns "main table" records.

I would create a second query that joins the "detail" table to its "look-up"
table, so as to get the descriptive text. In this second query, I would
also include the foreign key field from the "detail" table that points back
to which "main" table record "owns" the particular "detail" record.

I would create a main form based on the query against the main table.

I would create another form based on the (second) query against the detail
table.

I would open the main form in design view and use the Toolbar button to add
in a subform, and would use that second form. In the process, I would tell
Access which field links the two (the ID from the main table should be a
foreign key field value in the detail table).

With this design, when the record showing up in the main form changes, the
subform changes to display THAT (new) main form record's detail records.

Does this help? Have you looked into use Access HELP on creating queries?

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/