More questions for Tom Ellison

S

Susan

Hello,

I'm hoping that Tom Ellison still is about on this page. I've just been
reading his posts for "help with calculations in a query" 11/04/2005 - Which
I have a similar problem with and have previous found help with on this site.

My problem is reading metered gas on consecutive times (not dates but
intervals).

This solution that I previous had involved this:
mLDif10:
Switch([time_slice]=0,[10],[time_slice]=1,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=0")),[time_slice]=2,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=1")),[time_slice]=3,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=2")),[time_slice]=4,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=3")),[time_slice]=5,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=4")),[time_slice]=6,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=5")),[time_slice]=7,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=6")),[time_slice]=8,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=7")),[time_slice]=9,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=8")),[time_slice]=10,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=9")),[time_slice]=11,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=10")),[time_slice]=12,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=11")),[time_slice]>13,-1)

Basically what I have is a union query taking data from txt files (one for
each "flask" i.e. [10] in the above example) into one query. I then use the
crosstab to seperate the individual "flasks"....and then I run the above, one
forumla for each flask.

The problems are it's very slow (about 30-45 min), and I can only do the
first 13 "time slices" because of feild size limitations.

Having read Tom's response to "help with calculations in a query" I'm hoping
there is a better solution to my problem. In my case "flask" is like the name
of each water meter and "mL_gas" is like the meter reading.

I can't quite understand the solution posted by Tom. By the sounds of it my
data is correctly normalised (I have many (36) rows of data for one
"time_slice" or date-equivalent).

I don’t understand what he means by “Filter it with so one table has the
reading date equal to Forms![Meter Usage]!Date and the other to the same
date minus 1â€.

This is what I wrote for my data:

SELECT Qry_TimeVrsO2.Time_slice AS Time_slice, Qry_TimeVrsO2.Flask,
Qry_TimeVrsO2.[mL gas], [Qry_TimeVrsO2_1].[time_slice]-1 AS Time_sliceb4
FROM Qry_TimeVrsO2 AS Qry_TimeVrsO2_1 INNER JOIN Qry_TimeVrsO2 ON
Qry_TimeVrsO2_1.Flask = Qry_TimeVrsO2.Flask
ORDER BY Qry_TimeVrsO2.Time_slice, Qry_TimeVrsO2.Flask;

Which doesn’t really work as I get multiple results for each Time_Slice:


Time_slice Flask mL gas Time_sliceb4
0 1 183.9 2
0 1 183.9 1
0 1 183.9 -1
0 1 183.9 0
0 1 183.9 3

Tom goes on to use another query to make the subtraction with:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#
AND MU1.MeterID = MU0.MeterID


I have yet tried this as I want to get the first query working right first.
But my main question is what does MU0 and MU1 relate to? Will this only give
the result for the 11/25/05? … I need it to work for all “times†(I have 29
time slices)

Any help greatly appreciated

Thanks

Susan
 
A

Allen Browne

If Tom is not able to reply, this example might help:
http://allenbrowne.com/subquery-01.html#AnotherRecord

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Susan said:
Hello,

I'm hoping that Tom Ellison still is about on this page. I've just been
reading his posts for "help with calculations in a query" 11/04/2005 -
Which
I have a similar problem with and have previous found help with on this
site.

My problem is reading metered gas on consecutive times (not dates but
intervals).

This solution that I previous had involved this:
mLDif10:
Switch([time_slice]=0,[10],[time_slice]=1,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=0")),[time_slice]=2,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=1")),[time_slice]=3,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=2")),[time_slice]=4,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=3")),[time_slice]=5,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=4")),[time_slice]=6,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=5")),[time_slice]=7,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=6")),[time_slice]=8,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=7")),[time_slice]=9,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=8")),[time_slice]=10,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=9")),[time_slice]=11,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=10")),[time_slice]=12,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=11")),[time_slice]>13,-1)

Basically what I have is a union query taking data from txt files (one for
each "flask" i.e. [10] in the above example) into one query. I then use
the
crosstab to seperate the individual "flasks"....and then I run the above,
one
forumla for each flask.

The problems are it's very slow (about 30-45 min), and I can only do the
first 13 "time slices" because of feild size limitations.

Having read Tom's response to "help with calculations in a query" I'm
hoping
there is a better solution to my problem. In my case "flask" is like the
name
of each water meter and "mL_gas" is like the meter reading.

I can't quite understand the solution posted by Tom. By the sounds of it
my
data is correctly normalised (I have many (36) rows of data for one
"time_slice" or date-equivalent).

I don’t understand what he means by “Filter it with so one table has the
reading date equal to Forms![Meter Usage]!Date and the other to the same
date minus 1â€.

This is what I wrote for my data:

SELECT Qry_TimeVrsO2.Time_slice AS Time_slice, Qry_TimeVrsO2.Flask,
Qry_TimeVrsO2.[mL gas], [Qry_TimeVrsO2_1].[time_slice]-1 AS Time_sliceb4
FROM Qry_TimeVrsO2 AS Qry_TimeVrsO2_1 INNER JOIN Qry_TimeVrsO2 ON
Qry_TimeVrsO2_1.Flask = Qry_TimeVrsO2.Flask
ORDER BY Qry_TimeVrsO2.Time_slice, Qry_TimeVrsO2.Flask;

Which doesn’t really work as I get multiple results for each Time_Slice:


Time_slice Flask mL gas Time_sliceb4
0 1 183.9 2
0 1 183.9 1
0 1 183.9 -1
0 1 183.9 0
0 1 183.9 3

Tom goes on to use another query to make the subtraction with:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#
AND MU1.MeterID = MU0.MeterID


I have yet tried this as I want to get the first query working right
first.
But my main question is what does MU0 and MU1 relate to? Will this only
give
the result for the 11/25/05? … I need it to work for all “times†(I have
29
time slices)

Any help greatly appreciated

Thanks

Susan
Click to expand...
 
D

Dale Fye

Can you give us a sample of a couple of records from your table (not really
sure whether to use the table or your co2trap query). Also give us a sample
of what you want your output to look like for the same set of data.

--
Email address is not valid.
Please reply to newsgroup only.


Susan said:
Hello,

I'm hoping that Tom Ellison still is about on this page. I've just been
reading his posts for "help with calculations in a query" 11/04/2005 - Which
I have a similar problem with and have previous found help with on this site.

My problem is reading metered gas on consecutive times (not dates but
intervals).

This solution that I previous had involved this:
mLDif10:
Switch([time_slice]=0,[10],[time_slice]=1,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=0")),[time_slice]=2,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=1")),[time_slice]=3,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=2")),[time_slice]=4,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=3")),[time_slice]=5,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=4")),[time_slice]=6,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=5")),[time_slice]=7,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=6")),[time_slice]=8,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=7")),[time_slice]=9,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=8")),[time_slice]=10,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=9")),[time_slice]=11,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=10")),[time_slice]=12,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=11")),[time_slice]>13,-1)

Basically what I have is a union query taking data from txt files (one for
each "flask" i.e. [10] in the above example) into one query. I then use the
crosstab to seperate the individual "flasks"....and then I run the above, one
forumla for each flask.

The problems are it's very slow (about 30-45 min), and I can only do the
first 13 "time slices" because of feild size limitations.

Having read Tom's response to "help with calculations in a query" I'm hoping
there is a better solution to my problem. In my case "flask" is like the name
of each water meter and "mL_gas" is like the meter reading.

I can't quite understand the solution posted by Tom. By the sounds of it my
data is correctly normalised (I have many (36) rows of data for one
"time_slice" or date-equivalent).

I don’t understand what he means by “Filter it with so one table has the
reading date equal to Forms![Meter Usage]!Date and the other to the same
date minus 1â€.

This is what I wrote for my data:

SELECT Qry_TimeVrsO2.Time_slice AS Time_slice, Qry_TimeVrsO2.Flask,
Qry_TimeVrsO2.[mL gas], [Qry_TimeVrsO2_1].[time_slice]-1 AS Time_sliceb4
FROM Qry_TimeVrsO2 AS Qry_TimeVrsO2_1 INNER JOIN Qry_TimeVrsO2 ON
Qry_TimeVrsO2_1.Flask = Qry_TimeVrsO2.Flask
ORDER BY Qry_TimeVrsO2.Time_slice, Qry_TimeVrsO2.Flask;

Which doesn’t really work as I get multiple results for each Time_Slice:


Time_slice Flask mL gas Time_sliceb4
0 1 183.9 2
0 1 183.9 1
0 1 183.9 -1
0 1 183.9 0
0 1 183.9 3

Tom goes on to use another query to make the subtraction with:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#
AND MU1.MeterID = MU0.MeterID


I have yet tried this as I want to get the first query working right first.
But my main question is what does MU0 and MU1 relate to? Will this only give
the result for the 11/25/05? … I need it to work for all “times†(I have 29
time slices)

Any help greatly appreciated

Thanks

Susan
Click to expand...
 
S

Susan

Hello Dale,

The tables are links to txt files and look like this:

Hr mM/Kg-Hr mM/Kg mL gas mg/Kg-Hr mg/Kg Comments
0 0 0 0 0 0 060315 TREAT5
0.75 0 0 0 0 0
1.5 0.08 0.063 0.1 2.6 2
2.25 0.08 0.125 0.3 2.6 4
3 0.08 0.188 0.4 2.6 6
..... (many more records)
208.5 0.25 77.322 183.9 8 2474.3 co2 TRAP CHANGED
......

I also have a table that has the details for the samples (CO2 traps). This
has a lookup value which I use to select the right CO2 trap. It looks like
this:

User_codeKEY Date_collected Flask Sample_hr Time_slice Type
T0 ref 1 15/03/2006 1 208.5 0 r
T0 ref 11 23/03/2006 11 190.4 0 r
T0 ref 15 15/03/2006 15 208.5 0 r
T0 ref 16 15/03/2006 16 210 0 r

I use a union query to bring all results from the text files together:

Hr Flask mL gas
0 3 0
0 20 0
0 11 0
0 15 0
0 16 0
0 1 0
0 17 0

There are many more records (the experiments continue for months)

What I currently do then is a crosstab query which looks like this:

Time_slice 1 11 15 16 19
0 183.9 32.8 144.4 155.5 114.8
1 275.5 65.2 206.4 224.3 166.9
2 443.8 94.2 306.8 331.5 249.5

This is the cummulative amount of gas in each flask at each trap change.
What I want is the difference between each ie something that looks like:

Time_slice 1 11 15 16 19
0 183.9 32.8 144.4 155.5 114.8
1 91.6 32.4 62 68.8 52.1
2 168.3 29 100.4 107.2 82.6


Remember this is a small amount of the data - there is actually up to 36
flasks and can have more then 40 time slices

I think do a union query to bring the results back into one list which looks
like this:

Diff Flask time_slice
168.3 1 2
91.6 1 1
183.9 1 0
94.5 2 4
143.4 2 3


The utlimate thing that I'm trying to acheive is....to compare the mL gas
(oxygen) with the amount of CO2 in the trap. The resulting table looks like
this:

Time_slice User_codeKEY Flask Sample_hr CO2Report ByWeight Diff
0 T0 test 3 3 209.25 2.612 3.48 183.1
1 T1 ref 1 1 380.04 0.051 0.36 91.6
1 T1 test 1 1 380.04 2.789 2.75 91.6
1 T1 ref 2 2 380.31 0.038 0.41 90








Dale Fye said:
Can you give us a sample of a couple of records from your table (not really
sure whether to use the table or your co2trap query). Also give us a sample
of what you want your output to look like for the same set of data.

--
Email address is not valid.
Please reply to newsgroup only.


Susan said:
Hello,

I'm hoping that Tom Ellison still is about on this page. I've just been
reading his posts for "help with calculations in a query" 11/04/2005 - Which
I have a similar problem with and have previous found help with on this site.

My problem is reading metered gas on consecutive times (not dates but
intervals).

This solution that I previous had involved this:
mLDif10:
Click to expand...
Switch([time_slice]=0,[10],[time_slice]=1,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=0")),[time_slice]=2,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=1")),[time_slice]=3,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=2")),[time_slice]=4,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=3")),[time_slice]=5,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=4")),[time_slice]=6,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=5")),[time_slice]=7,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=6")),[time_slice]=8,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=7")),[time_slice]=9,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=8")),[time_slice]=10,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=9")),[time_slice]=11,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=10")),[time_slice]=12,([10]-DLookUp("[10]","qry_crosstab_o2_co2trap","[time_slice]=11")),[time_slice]>13,-1)
Basically what I have is a union query taking data from txt files (one for
each "flask" i.e. [10] in the above example) into one query. I then use the
crosstab to seperate the individual "flasks"....and then I run the above, one
forumla for each flask.

The problems are it's very slow (about 30-45 min), and I can only do the
first 13 "time slices" because of feild size limitations.

Having read Tom's response to "help with calculations in a query" I'm hoping
there is a better solution to my problem. In my case "flask" is like the name
of each water meter and "mL_gas" is like the meter reading.

I can't quite understand the solution posted by Tom. By the sounds of it my
data is correctly normalised (I have many (36) rows of data for one
"time_slice" or date-equivalent).

I don’t understand what he means by “Filter it with so one table has the
reading date equal to Forms![Meter Usage]!Date and the other to the same
date minus 1â€.

This is what I wrote for my data:

SELECT Qry_TimeVrsO2.Time_slice AS Time_slice, Qry_TimeVrsO2.Flask,
Qry_TimeVrsO2.[mL gas], [Qry_TimeVrsO2_1].[time_slice]-1 AS Time_sliceb4
FROM Qry_TimeVrsO2 AS Qry_TimeVrsO2_1 INNER JOIN Qry_TimeVrsO2 ON
Qry_TimeVrsO2_1.Flask = Qry_TimeVrsO2.Flask
ORDER BY Qry_TimeVrsO2.Time_slice, Qry_TimeVrsO2.Flask;

Which doesn’t really work as I get multiple results for each Time_Slice:


Time_slice Flask mL gas Time_sliceb4
0 1 183.9 2
0 1 183.9 1
0 1 183.9 -1
0 1 183.9 0
0 1 183.9 3

Tom goes on to use another query to make the subtraction with:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#
AND MU1.MeterID = MU0.MeterID


I have yet tried this as I want to get the first query working right first.
But my main question is what does MU0 and MU1 relate to? Will this only give
the result for the 11/25/05? … I need it to work for all “times†(I have 29
time slices)

Any help greatly appreciated

Thanks

Susan
Click to expand...
Click to expand...
 
S

Susan

Sorry for split post - pressed wrong key and sent first one before I had
finished.

so with the last table the formating is a bit screwed (flask names are under
key), you need to move each across on column.

The CO2_report value is the measured amount of CO2 in each trap, By weight
is the weight change in the traps with incubation (co2 will be converted to
CO3 in the trap and there is a weight increase - the more weight the more
CO3) and the Diff is the result from all the above queries, i.e it's the
amount of O2 used during each time slice.

Flask is text.

like I said in first post - the solution works, but I can only do the first
12-13 time slices and it also takes a long time....to get this reply together
took me over 2 hours

Thanks again

Susan
 
S

Susan

Hello Allen,

Thanks for the link,

I've had a look and tried to adapt your code for my tables. I get an "syntax
error in FROM clause" error

Here is my code:

SELECT qry__O2_CO2Trap.Flask, qry__O2_CO2Trap.Time_slice,
qry__O2_CO2Trap.[mL gas]
FROM qry__O2_CO2Trap,
(SELECT TOP 1 Dupe.[mL Gas]
FROM qry__O2_CO2Trap AS Dupe
WHERE Dupe.Flask = qry__O2_CO2Trap.Flask
AND Dupe.Time_slice < qry__O2_CO2Trap.Time_Slice
ORDER BY Dupe.Time_slice DESC, Dupe.ID)
AS PriorValue FROM qry__O2_CO2Trap;


Also, from your web page - this will only work for the last Time_slice? How
do I make it work for all times?

Thanks again

Susan
 
A

Allen Browne

You have the FROM clause twice in your main query. Try:

SELECT qry__O2_CO2Trap.Flask,
qry__O2_CO2Trap.Time_slice,
qry__O2_CO2Trap.[mL gas],
(SELECT TOP 1 Dupe.[mL Gas]
FROM qry__O2_CO2Trap AS Dupe
WHERE Dupe.Flask = qry__O2_CO2Trap.Flask
AND Dupe.Time_slice < qry__O2_CO2Trap.Time_Slice
ORDER BY Dupe.Time_slice DESC, Dupe.ID) AS PriorValue
FROM qry__O2_CO2Trap;

The subquery will give you only one value as the PriorValue, for every
record in the main query. I'm not sure what's in your qry__O2_CO2Trap, so I
can't comment on what records or how many it will give you.
 
S

Susan

Works a treat. Thankyou!!!!

The only problem is that I have two samples for each time slice , "test" and
"ref" (the ref samples are the control samples). Basically Flask is not
unique unless limit the sample type to Test or Ref. Not a great problem though

thanks again

Susan

Allen Browne said:
You have the FROM clause twice in your main query. Try:

SELECT qry__O2_CO2Trap.Flask,
qry__O2_CO2Trap.Time_slice,
qry__O2_CO2Trap.[mL gas],
(SELECT TOP 1 Dupe.[mL Gas]
FROM qry__O2_CO2Trap AS Dupe
WHERE Dupe.Flask = qry__O2_CO2Trap.Flask
AND Dupe.Time_slice < qry__O2_CO2Trap.Time_Slice
ORDER BY Dupe.Time_slice DESC, Dupe.ID) AS PriorValue
FROM qry__O2_CO2Trap;

The subquery will give you only one value as the PriorValue, for every
record in the main query. I'm not sure what's in your qry__O2_CO2Trap, so I
can't comment on what records or how many it will give you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Susan said:
Hello Allen,

Thanks for the link,

I've had a look and tried to adapt your code for my tables. I get an
"syntax
error in FROM clause" error

Here is my code:

SELECT qry__O2_CO2Trap.Flask, qry__O2_CO2Trap.Time_slice,
qry__O2_CO2Trap.[mL gas]
FROM qry__O2_CO2Trap,
(SELECT TOP 1 Dupe.[mL Gas]
FROM qry__O2_CO2Trap AS Dupe
WHERE Dupe.Flask = qry__O2_CO2Trap.Flask
AND Dupe.Time_slice < qry__O2_CO2Trap.Time_Slice
ORDER BY Dupe.Time_slice DESC, Dupe.ID)
AS PriorValue FROM qry__O2_CO2Trap;


Also, from your web page - this will only work for the last Time_slice?
How
do I make it work for all times?

Thanks again

Susan
Click to expand...
Click to expand...
 
A

Allen Browne

Good. All solved.

You can easily add criteria to limit the query to either Test or Ref.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dlookup, crosstab & calculating the difference between previous va 0
Query help (SQL) 9
sum for past tweleve months 7
DLOOKUP FOR NEXT 9
DLookup function to return previous record (Access 2003) 2
Most recent date prior to test date. 2
Grouping/Deleting Dates/Times (HELP ANYONE!) 2
Tally the figures acquired from the same date 3

Top