Showing and unshowing various group headers

S

Sarah Schreffler

I am not quite sure where even to begin here.

I've got a table tlkpServices (that is used elsewhere in the database
for people to rate their knowledge) It holds four levels of information
(and people can put ratings against L2, L3, or L4.

The fields are:
ServiceID (Autonumber)
Service (the main text field)
ServiceGroup (The one that says what level this service is, "L1", "L2"
"L3" or "L4"
and then three fields that, for those that point back to other entries
in this field to relate a record to previous:
L1_ID
L2_ID
L3_ID

L1s are null for all of those.
L2s only have L1
L3s only have L1 and L2
L4s have all three.

All 4 levels do not exist for every L2. (Ie there are some places where
all I have is L1 and L2)

I want them in a report that looks like this

Level 1 Group
Level 2 group
Level 3 group
Level 4 group
Level 4 group
Level 4 group
Level 3 group

and etc.

(eventually, this will be combined with the ratings to list underneath
each one those who have different levels of proficiency in them)

My report query currently looks like this:
SELECT tlkpService.Service, tlkpService.ServiceGroup,
tlkpService_1.Service AS [Business Class], tlkpService_2.Service AS
[Service Area], tlkpService_3.Service AS [Detailed Service]
FROM ((tlkpService LEFT JOIN tlkpService AS tlkpService_1 ON
tlkpService.BusinessClassID = tlkpService_1.ServiceID) LEFT JOIN
tlkpService AS tlkpService_2 ON tlkpService.ServiceAreaID =
tlkpService_2.ServiceID) LEFT JOIN tlkpService AS tlkpService_3 ON
tlkpService.DetailedServiceID = tlkpService_3.ServiceID
WHERE (((tlkpService_1.Service) Is Not Null));

I have group headers set up for Level 1, Level 2, and level 3 with
<service> in the Detail section

However, because all of the text for the levels is actually IN that
service field -- for L1, say, it is not just putting the Level 1 title
in the header, but also again in the detail.

Then again for Level 2, etc.

So I wanted to, on format, tell it not to show the detail if it has
already printed the title in the header:
If Me!ServiceGroup = "L1" Then
Me.GroupHeaderBC.Visible = True
Me.Detail.Visible = False
Else
Me.GroupHeaderBC.Visible = False
Me.Detail.Visible = True
End If

But it doesn't seem to be having any effect at all on the end report
(IE it looks the same as it did before I put that in.
 
A

Allen Browne

Hi Sarah. You need to use outer joins in your query.

Open the query in design view.
In the upper pane, double-click the line joining tlkpService to
tlkpService_1.
Access opens a dialog offering 3 options.
Choose #2 (or possibly #3, depending how the query works.)
This ensures you get entries for every service in the table, even if there
are no related services in the self-join.

The criteria under tlkpService_1.Service will also have an impact.

For a quick intro to outer joins and the impact of the null criteria, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Sarah Schreffler said:
I am not quite sure where even to begin here.

I've got a table tlkpServices (that is used elsewhere in the database
for people to rate their knowledge) It holds four levels of information
(and people can put ratings against L2, L3, or L4.

The fields are:
ServiceID (Autonumber)
Service (the main text field)
ServiceGroup (The one that says what level this service is, "L1", "L2"
"L3" or "L4"
and then three fields that, for those that point back to other entries
in this field to relate a record to previous:
L1_ID
L2_ID
L3_ID

L1s are null for all of those.
L2s only have L1
L3s only have L1 and L2
L4s have all three.

All 4 levels do not exist for every L2. (Ie there are some places where
all I have is L1 and L2)

I want them in a report that looks like this

Level 1 Group
Level 2 group
Level 3 group
Level 4 group
Level 4 group
Level 4 group
Level 3 group

and etc.

(eventually, this will be combined with the ratings to list underneath
each one those who have different levels of proficiency in them)

My report query currently looks like this:
SELECT tlkpService.Service, tlkpService.ServiceGroup,
tlkpService_1.Service AS [Business Class], tlkpService_2.Service AS
[Service Area], tlkpService_3.Service AS [Detailed Service]
FROM ((tlkpService LEFT JOIN tlkpService AS tlkpService_1 ON
tlkpService.BusinessClassID = tlkpService_1.ServiceID) LEFT JOIN
tlkpService AS tlkpService_2 ON tlkpService.ServiceAreaID =
tlkpService_2.ServiceID) LEFT JOIN tlkpService AS tlkpService_3 ON
tlkpService.DetailedServiceID = tlkpService_3.ServiceID
WHERE (((tlkpService_1.Service) Is Not Null));

I have group headers set up for Level 1, Level 2, and level 3 with
<service> in the Detail section

However, because all of the text for the levels is actually IN that
service field -- for L1, say, it is not just putting the Level 1 title
in the header, but also again in the detail.

Then again for Level 2, etc.

So I wanted to, on format, tell it not to show the detail if it has
already printed the title in the header:
If Me!ServiceGroup = "L1" Then
Me.GroupHeaderBC.Visible = True
Me.Detail.Visible = False
Else
Me.GroupHeaderBC.Visible = False
Me.Detail.Visible = True
End If

But it doesn't seem to be having any effect at all on the end report
(IE it looks the same as it did before I put that in.
Click to expand...
 
Top