🚀 go-pugleaf

vk@sbox.tu-graz.ac.at (Karl Voit) writes:

> Hi!

> The manuals are very confusing to me and so I have to ask some
> Access2.0-freaks to help me on these problems:

That's SuperFreak to you, buck-o!

> (btw, I am speaking german and my Access2.0 too :-)
>  So please ignore the mistakes I made in the translation.)



> I've got an endless-form for my telephone-database:

> room:  number of the room (1-4)
> date:  date of the call
> cost:  cost of the telephone-call
> sum:   number of received impulses so far (1 pulse received -> +0.8 costs)
> text:  -
> sheet: number of the sheet, on which the call was noticed


> Problem 1:
> I want to calculate the cost by multiplying the difference of the sum by 0.8
> e.g.: cost = ( sum - sum.old ) * 0.8
> So, how do I refer to the sum.old-field?
> Can I just enter the calculation or do I have to use a querry or so?

You can include calculations or even function calls in your queries or
as the ControlSource of your form and report controls. What is sum.old
and where does it come from?

> Problem 2:
> Sheet should be the number of the sheet one line above, so I just have
> to change it, when a new sheet is written.
> That should be the same problem like question 1 or is this easier?

When should the sheet number be incremented? I suppose you can give it a
default value of DMax("sheet", "calls") in the form.

> Problem 3:
> In a querry, I've got this:

> In (SELECT [Zi1] FROM [Konto] As Tmp GROUP BY [Zi1] HAVING 
> Count(*)>1) And Yes

> ...and I want to change "Zi1" to "Zi2" and "Zi3" or "Zi4" from time to time,
> so I wrote a function to return Zi1, ... , Zi4. It's called GetZi() and 
> returns Zi1 or Zi2, or ...
> BUT: In the querry, I can't write it like this:

> In (SELECT [=GetZi()] FROM [Konto] As Tmp GROUP BY [=GetZi()] HAVING 
> Count(*)>1) And Yes

> ...without getting errors. It seems to me that Access dosn't want any
> function-calls in a querry.
> Is this impossible to do so?

You'd probably want four queries, one for each possible Zi#, so that
Access can optimize the queries to use indexes. Otherwise, you could use
a different function that takes the Zi# fields as parameters and returns
the field you want, but using this in a WHERE, GROUP BY, or ORDER BY
clause will probably be really slow:

Function GetZi4(ByVal Zi1 As Variant, ByVal Zi2 As Variant,
  ByVal Zi3 As Variant, ByVal Zi4 As Variant) As Variant

    Select Case WhichZi
    Case 1: GetZi4 = Zi1
    Case 2: GetZi4 = Zi2
    Case 3: GetZi4 = Zi3
    Case 4: GetZi4 = Zi4
    Case Else: Stop
    End Select

End Function

SELECT GetZi4(Zi1, Zi2, Zi3, Zi4)
FROM [Konto] As Tmp
GROUP BY GetZi4(Zi1, Zi2, Zi3, Zi4)
HAVING Count(*) > 0

> Problem 4:
> I've got another endless-form:

> [...] some things that doesn't matter here
> Sum: cost of a product
> Zi1: Yes/No, if the room 1 is concerned
> Zi2: Yes/No
> Zi3: Yes/No
> Zi4: Yes/No

> I want to calculate the costs of room 1, 2, 3 or 4 and therefore, I have
> to make a querry to get the number of rooms concerned of that line like:
> Sum Zi1 Zi2 Zi3 Zi4  -> NrOfYes  -> CostForEachParty
> 21  Y   Y   N   Y       3           (21/3=) 7
> 30  N   Y   Y   N       2           (30/2=) 15

Function SafeDivide(ByVal X As Double, ByVal Y As Double, ByVal Z As Variant) As Variant
    If Abs(Y) > 0.0001 Then
        SafeDivide = X / Y
    Else
        SafeDivide = Z
    End If
End Function

select count(*) as [Sum], Zi1, Zi2, Zi3, Zi4,
  SafeDivide(count(*), iif(Zi1 <> False, 1, 0) +
  iif(Zi2 <> False, 1, 0) + iif(Zi3 <> False, 1, 0) +
  iif(Zi4 <> False, 1, 0), Null) as CostForEachParty
from Konto
group by Zi1, Zi2, Zi3, Zi4

If this was for school work, I demand credit. :-)

[snip]

-- 
Joe Foster <mailto:joe*AT*bftsi0.gate.net> Spam is irrelevant. Assimilate this:
<ftp://ftp.microsoft.com/softlib/index.txt> Microsoft's master patch list
<ftp://ftp.microsoft.com/softlib/mslfiles/> MS I got yer patch right here!
<http://www.microsoft.com/kb/default.asp>   MS Knowledge Base
<http://www.dejanews.com/home_ps.shtml>     Usenet search and archival engine
<http://www.altavista.digital.com>          WWW *and* Usenet search engine
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!