Advanced Calculations? 1

[chrisd9]

I was forwarded to this forum, so I figured I should repost my original quaestion:

I am developing a database for a process for which we take samples at multiple times. So I have multiple processes (test1, test2, etc), with multiple samples for each test (-01,-02,-03)

I have a table of the form:

TEST ID........DATASET ID...........TIME.........OTHER DATA
test1.............test1-01...............4
test1.............test1-02...............5
test1.............test1-03...............8
test2.............test2-01...............2
test2.............test2-02...............4
test2.............test2-03...............9


My initial "raw data" table is of this form, (with multiple "other data" fields containing data of the samples taken at the particular time)

With all this raw data I need to calculate values. However, some of these values depend on the change in time between samples taken. For example, I need an automated way of calculating the time elapsed between dataset "test1-01" and dataset "test1-02" (4.6 hours).

I need to then be able to pull this value and use it in other calculations (a seperate query perhaps?).

The problem is I am having to recreate many calculations which were previously done in a spreadsheet. I have already found a way to mimic "absolute references" with multiple queries for the calculation in access. However, I am stuck on how to recreate this calculation.

THANKS IN ADVANCE!

 

[Craig0201]

OK......

Here's how i'd go about it.....

I'd create a query like this.....

SELECT
*,
CInt(RIGHT([DATASET ID])) AS TestNumber
FROM
YourTable

Call it qryTestNumber

Then i'd have a second query.

SELECT
a.[DATASET ID],
b.[TIME] - b.[TIME] AS Elapsed
FROM
qryTestNumber AS a
INNER JOIN
qryTestNumber AS b
ON
a.[TEST ID] = b.[TEST ID]
WHERE
b.TestNumber = a.TestNumber + 1

This query will join the rows in pairs with each test being joined to the next test in the sequence. You might need to change this to an outer join if you need to know anything about the final test.

Craig

 

[chrisd9]

not to be ignorant...

where do I enter the "select information"?

thanks for your help

 

[Craig0201]

Chris,

Not a problem.

Build a new query and switch to the SQL view. You probably see a blue set square. Click on that and you'll see an option for SQL view.

Craig

 

[Craig0201]

Oops.

SELECT
*,
CInt(RIGHT([DATASET ID],2)) AS TestNumber
FROM
YourTable

Craig

 

[Craig0201]

Double oops.

SELECT
a.[DATASET ID],
b.[TIME] - a.[TIME] AS Elapsed
FROM
qryTestNumber AS a
INNER JOIN
qryTestNumber AS b
ON
a.[TEST ID] = b.[TEST ID]
WHERE
b.TestNumber = a.TestNumber + 1

Craig

 

[chrisd9]

Craig,

Thanks for your help...

Upon entering the code for the first query, and attempting to run it, I get the message "Wrong number of arguments used with function in query expression 'CInt(RIGHT([DataSetNumber]))'.

My Code as entered into the sql dialogue is:

SELECT
*,
CInt(RIGHT([DataSetNumber])) AS TestNumber
FROM
RawData




Where "DataSetNumber" is the actual name of my DataSet ID field, and my table is "RawData".

Thanks again for your help!

 

[chrisd9]

WOOPS!

sorry i responded without seeing your modified code, will try that now.

 

[Craig0201]

Chris,

Corrected myself above twice. Please use these instead.

Craig

 

[chrisd9]

Craig,

This seems to be working well!

Thank you very much for your help... I may be asking questions later in the day as I ass to this base.

Thanks Again!

Chris

 

[chrisd9]

add that is, haha

 

[chrisd9]

Craig...

already back with another question...

I need to bump the elapsed time values up one dataset id, for example, the difference between dataset 2 and 1 is now associated with dataset 1. I need it to be associated with dataset 2.

Furthermore, I need the elapsed time for dataset 1 to simply be the first time entry (time elapsed since t=0)

any thoughts? thanks

 

[Craig0201]

qryTestNumber becomes

SELECT
YourTable.*,
CInt(Right([DATASET ID],2)) AS TestNumber,
IIf(CInt(Right([DATASET ID],2))>1,CInt(Right([DATASET ID],2))-1,1) AS AssociatedTestNumber
FROM
YourTable;

Other query becomes

SELECT
b.[DATASET ID],
.[TIME]-[a].[Time] AS Elapsed
FROM
qryTestNumber AS a
INNER JOIN
qryTestNumber AS b
ON
(a.[TEST ID] = b.[TEST ID]) AND (a.TestNumber = b.AssociatedTestNumber);

Craig