Calcualations on fields 1

[jalbao]

I have a table that as a similar schema to the following:

tblData - table
lTestDataID - autonumber (pk)
lTestData - float
dtAdded - datetime
lTestID - integer (foreign key of another table)

I need to run mathematical operations on the lTestData field where the dtAdded values match.

For example, I have the following records in my tblData table:

lTestDataID   lTestData   dtAdded              lTestID
23            34.5        5/15/2004 9:15 AM     78
24            19.356      6/19/2004 7:25 AM     78
25            78.2        5/15/2004 9:15 AM     88
26            79.214      6/19/2004 7:25 AM     88

Now let's say I have an expression that states:
((x + 34) * y)
I would like to run that expression on all the records that have a matching dtAdded value. The query that I'm trying to figure out would return two results:

Result table
5356.7
4226.542184

Can anyone point me in the right direction on this - this one has me puzzled.

 

[dhookom]

How does "((x + 34) * y)" match your sample data records? What are x, y, and 34?

Are you aware of totals queries?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

Something like this ?
SELECT A.dtAdded, A.lTestData As x, B.lTestData As y, (A.lTestData + 34) * B.lTestData As Result
FROM tblData A INNER JOIN tblData B
ON (A.dtAdded = B.dtAdded) AND (A.lTestDataID < B.lTestDataID)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jalbao]

Thanks PHV - I can work with that.