Prev: Running Sum (opposite)
Next: 2010: Essential Diffs?
From: Roger on 9 Apr 2010 13:20
On Apr 9, 10:07 am, "Poul Erik" <> wrote:
> Salad wrote:
> > You use ";" instead of "," for separating the fields.  I doubt it
> > would run in Access. Ex: IIf([transType]='S';
>
> > Is this an Update query?  If so, select from the menu and select
> > Query/Select.  IOW, change it temporarily from and Update to Select.
> > Then run the query.  Is there a value in TotalAktier?  If there is
> > then there's a problem.  Maybe you selected the wrong field to
> > uodate.  If there isn't, then you do have a problem with your
> > expression.  Remober to change the query back to Update query.
>
> I'm using semicolon because comma don’t work in access 2007 at least
> not in a query. But I have tried both.
>
> Yes there is a field called TotalAktier in both the tblItemTransaction
> table and in the query qryKøbSalg
>
> It is not a update query I use. (I don't know how to use an update
> query, I'm still a novice in access)
>
> Below there is a list of the column in the table "tblItemTransaction"
>
> itemId | transType | transDate| Kurs | transQty | transCost |
> TotalAktier
>
> And a list of my query  (qryKøbSalg)
>
> itemId | description | transType | transDate | Kurs | transQty |
> TotalAktier| transCost
>
> In the query i fill in the date in itemId, description,
> transType,transDate, Kurs, transQty and transCost. TotalAktier
> calculate automatically based on Kurs*transQty. No problem with this in
> the query.
>
> The problem is, that all data should be sent to the tblItemTransaction
> table from that query, and the data from all fields except from the
> TotalAktier did that.
>
> I write in the keys directly in the query into the table view.
>
> I'm wonder if I in this  syntax below should tell where the result
> should be placed which shoud be in the table tblItemTransaction and in
> the field TotalAktier, but I don't know how and where.
>
> TotalAktier:
> (IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P';-[transQty]*[
> Kurs])))
> --

all the fields in your query come from the corresponding field in the
table
so they are 'updateable'
but 'totalAktier' is a calculated field, thus not updateable
it is not a good idea to store totals in a table, it is better to
calculate the total using a query, as you've done

if you really want to store the total, you need to
a) have your query refer to the table 'totalAktier' field
b) use a form to edit the data
c) have the form's beforeUpdate event, calculate 'totalAktier'

of course, anyone updating data in the table, without using the form
will have to calculate the total, or it will be incorrect... thus one
reason why totals should not be stored in a table
From: Salad on 9 Apr 2010 14:05
Poul Erik wrote:
> Salad wrote:
>
>
>
>>You use ";" instead of "," for separating the fields. I doubt it
>>would run in Access. Ex: IIf([transType]='S';
>>
>>Is this an Update query? If so, select from the menu and select
>>Query/Select. IOW, change it temporarily from and Update to Select.
>>Then run the query. Is there a value in TotalAktier? If there is
>>then there's a problem. Maybe you selected the wrong field to
>>uodate. If there isn't, then you do have a problem with your
>>expression. Remober to change the query back to Update query.
>
>
>
> I'm using semicolon because comma don�t work in access 2007 at least
> not in a query. But I have tried both.
>
> Yes there is a field called TotalAktier in both the tblItemTransaction
> table and in the query qryK�bSalg
>
> It is not a update query I use. (I don't know how to use an update
> query, I'm still a novice in access)
>
> Below there is a list of the column in the table "tblItemTransaction"
>
> itemId | transType | transDate| Kurs | transQty | transCost |
> TotalAktier
>
>
> And a list of my query (qryK�bSalg)
>
> itemId | description | transType | transDate | Kurs | transQty |
> TotalAktier| transCost
>
> In the query i fill in the date in itemId, description,
> transType,transDate, Kurs, transQty and transCost. TotalAktier
> calculate automatically based on Kurs*transQty. No problem with this in
> the query.
>
> The problem is, that all data should be sent to the tblItemTransaction
> table from that query, and the data from all fields except from the
> TotalAktier did that.
>
> I write in the keys directly in the query into the table view.
>
> I'm wonder if I in this syntax below should tell where the result
> should be placed which shoud be in the table tblItemTransaction and in
> the field TotalAktier, but I don't know how and where.
>
> TotalAktier:
> (IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P';-[transQty]*[
> Kurs])))

Your statement breaks down to something like this
If transtype = "s" then
returnval = [transQty]*[Kurs]
elseif IIf([transType]='P' then
returnval = -[transQty]*[Kurs]
endif

So what happens if not S or P?

Are any records with a transtype of P or S?

I'd remove the column with your IIF() and see if there's a P/S record in
the bunch.





From: bobh on 9 Apr 2010 16:38
On Apr 8, 3:05 pm, "Poul Erik" <> wrote:
> I have made a query called qryKøbSalg in my access 2007 database with
> the following columns.
>
> itemId  ref to the table tblItemTransaction
>
> descrition      ref to the table tblItem
>
> transDate       ref to table tblItemTransaction
>
> transType       ref to table tblItemTransaction
>
> kurs    ref to table tblItemTransaction
>
> transQty        ref to table tblItemTransaction
>
> TotalAktier     do not ref to any table but I have this expressions:
> TotalAktier:
> (IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P';-[transQty]*[
> Kurs])))
>
> Omk     ref to table tblItemTransaction
>
> When I enter keys in the query all data are automatically pasted into
> the table tblItemTransaction in the following columns
>
> itemId > itemId
> transType > transType
> transDate > transTade
> kurs > kurs
> transQty > transQty
> Omk > Omk
>
> My problem is the column TotalAktier which not are pasted into the
> tblItemTransaction table.
>
> Did I miss something in the expressions?
>
> Poul
>
> --

you didn't specify a second false and you have an extra set of ()
to me it should look like

IIf([transType]='S';[transQty]*[Kurs];IIf([transType]='P';
([transQty]*[ Kurs])*-1,0))

bobh.
From: Marco Pagliero on 10 Apr 2010 06:41
On 9 Apr., 18:07, "Poul Erik" wrote:
> Yes there is a field called TotalAktier in both the tblItemTransaction
> table and in the query qryKøbSalg
> It is not a update query I use. (I don't know how to use an update
> query, I'm still a novice in access)
It should be an APPEND query.
Would you kindly give the SQL text of the query? It is one of the
Views: Query editor, menu views, SQL view or so (entwurfansicht/
ansicht/SQLansicht if it helps), copy and paste.

Try and write
TotalAktier: "TEST"
and run the query, just to see whether it pastes "TEST" or not.

Greetings
Marco P

First  |  Prev  | 
Pages: 1 2
Prev: Running Sum (opposite)
Next: 2010: Essential Diffs?