Creating Complex Report

[smil3y]

I am having great difficulty in sorting out how to build a Crystal report that requires data from 2 tables in Access 2000 database. The scenario follows.

Table 1 contains level 1 maintenance. Table 2 contains level 2 maintenance. All items have a level maintenance entry but not all level 1 items have a level 2 maintenance entry.

Table 1 contains the fields: Level1ID (AutoNumber), Level1_Equipment, level1_Dated, Level1_DateDue.
Table 2 contains the fields: Level2ID (AutoNumber), Level2_Equipment, Level2_Dated, Level2_DateDue and Level1ID.
In Table 2 the Level1ID relates the level2 item to the level1 parent equipment.

For example Table 1 might contain:

Level1ID Level1_Equipment Level1_dated level1_DateDue
1 Truck 11/11/2003 11/12/2003
2 Bus 10/10/2003 10/11/2003
3 Car 9/9/2003 9/10/2003

Table 2 would contain:

Level2ID Level2_Equipment Level2_Dated Level2_DateDue Level1ID
1 Alternator 11/11/2003 11/11/2004 1
2 Starter Motor 11/11/2003 11/11/2005 1
3 Fan Belt 11/11/2003 11/11/2004 1
4 Alternator 10/10/2003 10/10/2004 2
5 Fan Belt 10/10/2003 10/10/2005 2

I am trying to create a report that displays information as follows:

Grouped by level1_Equipment
ie Truck level1_DateDue
then grouped by level2_Equipment
Alternator Level2_DateDue
Starter Motor level2_DateDue
Fan Belt level2_DateDue

Bus Level1_dateDue

Alternator level2_DateDue
FanBelt level2_DateDue

Car level1_DateDue

I need to have the 3 Level1 groups shown even if there is no level2 entries.
I have been able through using subreports and creating new sections get almost to this point but it is causing major headache.
Is someone able to put me on the right track please.

 

[Madawc]

The trouble is that Level 2 is sometimes missing, right? So instead of grouping using the field, use a formula field which checks and says "No Level 2" if nothing is found. Something like
If isnull(Level2_Equipment) then "No Level 2"
else Level2_Equipment

Madawc Williams
East Anglia, Great Britain

 

[smil3y]

What I did was create 2 queries - 1 to get all the level2 sub equipments plus level1 equipments that are the parents of the sub equipment. Then another query to get all level 1 entries.

My best effort was to create a group and use the level2 query and group on the level1 parent equipments in this query. I then created a new section under the Group 1a header and in there I created a subreport that contained all the level1 parent equipments and associated dates.

I then created another group to group on just the level2 equipments and used the level2 query to provide the dates etc for the data.

This all worked fine except that Level1 equipment that does not have any level 2 sub equipment does not get listed.

This all makes sense to me - hope you can understand.

Can someone tell me how I need to group (and create sections if required) so that I can create a group that gets all the level1 parent equipments and then lists level2 sub equipments (if there are any) under the level1 parent equipment.

 

[smil3y]

I have currently created a report and am using SQL to obtain the level 1 parent equipment and display it. I have each of the Level1 parent equipments grouped. This all looks fine.

I also have an SQL query that obtains all the Level2 equipments - works fine in its own report. But how do I insert it under the Level1 parent equipments based on the level1ID allocated to each Level2 subitem. Can anyone help me please.