Dynamic heading with a sort 1

[elefantmannen]

hello,
I have a report where I have sorted the information based on specific categories. What I want to do is add a group header which have the value of one of these categories so it looks like...

CATEGORY1
everything within this category

CATEGORY2
everytihing within this category

hope Im making sense!

 

[scriverb]

Let me start slowly here. The report has a Record Source which you have designated as either a table or a query which uses the table to provide a recordset for the report to print. Look in the Properties of the Report for this property(Record Source). If it is a table then we need to make create a new query using that table. In this query we will create as I indicated a new field or column using the IIF expression to give you either a value of 1 or zero(0). This new field/column will now be available in your report to be displayed or added together to create the Summed valued that you requested.

Let's start with this portion. If it is a table then post the name of the table here and I will create a query for you to use as the Record Source.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

No, it is not a table it is a query.

 

[scriverb]

Open the query in design mode. In a new column paste the following in the top row:
SvarValue: IIF([tablename].[svar] = "ja", 1, 0)


Change the red code to your table name, save the query and close it. You now have a new column with a numeric value that can be summed.

Now open your report in design mode and from the Field List create a new control with this new field. Like I said before delete the attached label, change the visible property to NO, and make it real small and narrow and tuck it up in the corner of the detail section.

Now in the Reports Group Header create a control with the Control source proeprty set to
=Sum([SvarValue])

Your report should display in the Group Header the sum of the new field which represents the number of "ja"'s in this grouping.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

Nope, I get the same error message here as in the report "Wrong syntax...".

 

[scriverb]

I take it that you mean in the new column in the query. Please post the SQL from your query and let me take a look.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

Damn, your fast!
sql:
SELECT *
FROM formular as fo, fraga as fr
WHERE fo.frageID=fr.id AND fra.kontroll='Mat' OR fr.kontroll='Ins' OR fr.kontroll='Kva';

 

[scriverb]

I see that you are not providing a join between the two tables. Should they be connected by a relationship? Aren't certain records in one table related only to certain records in the other table. Also, you have not created this new column that I was talking about.

Let's work on one problem at a time. The query and the tables. Please answer the questions above and let's make sure that you query is giving you the correct recordset.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

they are connected by the fo.frageid and fr.id, but I can make an inner join on these two fields.

Problem with the adding of a new column is, I cant even create it as it keeps telling me it is wrong syntax.

 

[scriverb]

Since I didn't know which table has the field svar I had to guess. If I guessed wrong then change the red fr to fo;

SELECT fo.*, fr.*, IIF(fr.[svar] = "ja", 1, 0) as SvarValue
FROM formular as fo INNER JOIN fraga as fr ON fo.frageid = fr.id
WHERE fra.kontroll='Mat' OR fr.kontroll='Ins' OR fr.kontroll='Kva';

Let me know if this now runs your query and gives you a recordset that you expect. There should be a new column called SvarValue that has a 1 or 0 in it depending upon the value of svar. This new field can then be used in the detail section of the report and then summed up in the footer.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[elefantmannen]

HURRAH!!!!
It works, big thanx for hanging in there!!!

I'll be back with other problems.

 

[scriverb]

Fantastic!!! Me, hang in there. I thought you were going say "get rid of this guy!!!". He doesn't know anything.

Really pleased that I could help you with this project.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???