🚀 go-pugleaf

Joe "We are Borg" Foster (joe@bftsi0.UUCP) wrote:
: vk@sbox.tu-graz.ac.at (Karl Voit) writes:
: 
: 
: > 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?

Sum.old is the SAME field as Sum only ONE line above.
I want to enter the cost-field like
= (sum-content - sum-contentFromOneLineAbove) * 0.8 

: 
: > 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.

I have to type lists and each list has got a number. So I have to enter 20
values from list 1, 33 values from list 2, 27 values from list 3, ...
And I just don't want to enter the list number EVERY time. Just when I start
to type a new list.

: 
: > 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

I'll try this...

: 
: > 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
: 

Oh, yeah! I think this will work fine!
TNX a lot!

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

I can imagine, that those problems are very easy ones when you really often 
WORK with Access, but I HAVE to work with it and I unfortunately don't have 
time to learn the whole thing... :-)

: 
: [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!
           ******************************************

:-)


-- 


---
Karl VOIT alias vk@sbox.tu-graz.ac.at                     
         Student @ University of Technology of GRAZ (Austria)     
                                            (No, we have no Kangaroos!)     
Further information on http://www.sbox.tu-graz.ac.at/home/vk/

>> 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?

sum.old is the sum-field from one line above.

sum  cost
..   ...
4    3.2
6    1.6   =(6-4)*0.8

The only thing is - I think - how to refer to the line above.
I searched through the whole manual (paper & online) without getting
more than that Strg+"'" (Apostroph) gets the value of one line above.
But I can't imagine, how to use that in this case.




other Problem:

�> 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)
�    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


I call a macro when I go to the field (On Enter) perPerson (=CostsPerParty)
which executes this:

[perPerson] = SaveDiv([Sum], IIf([Zi1] = True, 1, 0) +
                             IIf([Zi2] = True, 1, 0) +
                             IIf([Zi3] = True, 1, 0) +
                             IIf([Zi4] = True, 1, 0), 0)

But when I go to that field in the form, there happenes nothing!
Not even 0 or an error.

So I tried it with the function-call diretcly: same effect.

Then I changed the macro-code to:

Me![perPerson]=33
or
[perPerson]=33
or
[perPerson]=[Sum]

...just to try it but: nothings happens EXCEPT:
When I use Me![perPerson], I get the errormessage:

*Reference not allowed* on field: 'Me'.
(Msg translated by me!)

Then I changed the OnEnter-event to Access Basic Code:
Me![perPerson]=33
-> same errormessage

And perPerson=33 doesn't do anything.
(btw, [perPerson]=33 is not allowed.)

Then I changed to OnGotFocus: same thing.



What's my mistake??????

I've got the feeling, that this is just one little thing that an outstanding
person can help me easily.


TNX again!


---
Karl VOIT alias vk@sbox.tu-graz.ac.at                     
         Student @ University of Technology of GRAZ (Austria)     
                                            (No, we have no Kangaroos!)     
Further information on http://www.sbox.tu-graz.ac.at/home/vk/