How to calcuate fields of different records (1 Viewer)

[Jacino]

In the same table, how can we make arithmetic operations on fields of the same column in different records provided that the records are with the same date and person number.

Your help is really appreicated. I need the easiest solution please

 

[ColinEssex]

Could you explain a little more about what you are trying to achieve?

Col

 

[Jacino]

I have a table with four columns: col1, col2, col3 and col4
There are X records in this table ( X is not determined yet), each record with filed1,field2,field3 and field4. The field3 is in (700,500,580).

Now I need to do the following operation:

For the 3 records of the same field1 and field2
(i.e. field1 in recordX1= field1 in recordX2=field1 in recordX3
and field2 in recordX1= field2 in recordX2=field2 in recordX3)
we need to calculate the following:

Calculation = (field4 in record where field3=700)-
[ (field4 in record where field3=500) + (field4 in record where field4=580)

I hope that I have cleared the situation. Is it O.k.

 

[Cosmos75]

Just create a query that selects the data and use an Iff statement.

 

[Jon K]

Assuming Field3, which contains the 700, 500, 580, is a numeric field. You can sum the three records with a group by query:

SELECT Field1, Field2, sum(iif(Field3=500,-Field4,Field4)) as Calculation
FROM yourTable
GROUP BY Field1, Field2


If Field3 is actually a text field, you need to put the 500 in quotes i.e.
sum(iif(Field3='500',-Field4,Field4))

The sum(iif(...)) tells Access if Field3 is 500, - Field4, otherwise + Field4 for each record in the group.

You can try this query. Click New in the Queries tab. Click OK on Design View. Click Close on Show Table. From Access' View menu, click SQL View. Copy and paste the above SELECT query to the SQL View window. Replace with the correct field names and table name. Click on the Run button (the one with the red exclamation mark on it) to run the query.

Hope this gives you the calculation you desired.

 

[Jacino]

Fine!

This is really Genial!. By French, c'est vraiment génial!.
For the benefit of the others, this is your idea in a finalized way:


SELECT field1, field2, Sum(IIf([field3]=500 Or [field3]=580,-[field4],IIf([field3]=700,[field4],0))) AS Calculation
FROM mytable
GROUP BY field1, field2
HAVING (((Sum(IIf([field3]=500 Or [field3]=580,-[field4],IIf([field3]=700,[field4],0))))>0))
ORDER BY field1;



Thank you very much.