Question Locked

Creating a Text box that calculates a sum.

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I've created a database, 1 table has VesselIDPK, date, tank name (e.g. 1, 12 Mar 2007, Pacific Princess), tankTypeFK and the other has TankTypeIDPK, type (e.g. 1, Small, Large, Medium). They are related so it shows for each VesselIDPK this is the type of Tanks.

I've created a report that pulls out the information and lists it by VesselIDPK, date, tankname, type. Each month is on a different page. It works but i want to put a calculation (via text box) that says for this month - we had 4 Small Vessels, 12 Medium, 13 large.

How can I write a code that will allow me to total that information? Or is there some easier way to state it in the report.

Urgent, need help and thanks.
Dyna

3 replies

by tony hopkinson

Select tangtypedesc, Count(*) from
tankTypes,Vessels
where tankTypes.typecode = Vessels.typecode
and ... put month stuff in here

If you want it all on one line of output you are down to a cursor, or something horribly hardcoded ie you's have to rewrite it if you added an extra tanktype.

It gets even messier if you want to do
1 Small Vessel instead of 1 Small Vessels

In SQL server
Declare @SmallVessels int
Select @SmallVessels = Count() From Vessels where tanktype =1 and month...
the Mediu, and Large
Select Case @SmallVessels
When 1 Then "1 Small Vessel"
Else Convert(VarChar(6),@SmallVessels) + " Small Vessels, "
end +
Case @MediumVessels
When 1 Then "1 Medium Vessel"
Else Convert(VarChar(6),@MediumVessels) + " medium Vessels, "

...
You get the idea.

This discussion is closed to new replies.