Query Calculation Problem 1

[tmreic]

I have a table with the following columns below. THe table contains results from different assays. I would like to write a query calculation to determin the amount of total Sucrose in each sample(A or B). To do this i need to subtract the glucose value that is related to the same sample (A or B).
(i.e. Sucrose+Glucose Sample A - Glucose Sample A). The way i have the table i can not figure how to build the query since both values are in the same column. I tried sub queries and the IIF function but can not figure out how subtract the values of each assay.

Thanks in advace for the help,
Tmreic


SampleID | AssayType | Nanomoles
A | Sucrose+Glucose | 40
A | Glucose | 10
B | Sucrose+Glucose | 30
B | Glucose | 10

 

[DougP]

If your final result is a sheet of paper then a Report can do a lot more for you. Keeping you same query let it calcualte all tha it can. The base your report on that query.
Each text box on a report can calculate different things and you can add text boxes for new fields.

Also in your query you can do that as well.
MyValue[field1 * field2]


DougP, MCP

 

[tmreic]

Doug,

Unfortunately i am not generating a report. The query is exported to a excel document for reporting purposes, etc. The current work around is to export both assays values into excel and do the subtraction in excel. But was looking for a way have this done automatically within the query. So if you have any suggestion on how to make this work within a query please let me know.

Thanks for your help,
Tmreic

 

[PaulBricker]

Can you give us a little more info about what the actual records look like for data. You can try something like this
First query.
SELECT testSample.SampleID, Last(testSample.Nanomoles) AS LastOfNanomoles
FROM testSample, testSample AS testSample_1
WHERE (((testSample_1.SampleID)=[testSample].[SampleID]))
GROUP BY testSample.SampleID;

And this is the second query (I named above query qtestSample).

SELECT testSample.SampleID, testSample.AssayType, testSample.Nanomoles, qtestSample.LastOfNanomoles
FROM qtestSample INNER JOIN testSample ON qtestSample.SampleID = testSample.SampleID
WHERE (((testSample.Nanomoles)<>[LastOfNanomoles]));

It will depend on how the info is set up. Using the Last predicate indicates that your data is always set up like your example. Look it over an post back with problems.

Paul

 

[Golom]

If your data appears exactly as shown then try something like

Select A1.SampleID, A1.NanoMoles As [SG], A2.NanoMoles As [G],
       (A1.NanoMoles - A2.NanoMoles) As [G Only]

From   Assay A1 INNER JOIN Assay A2 ON A1.SampleID = A2.SampleID

Where      A1.AssayType = "Sucrose+Glucose" 
       AND A2.AssayType = "Glucose"

 

[tmreic]

Golomn,
Thanks for you input but I am bit confused where the A1 and A2 comes from in your sql statement. The data for both assays are in the same table. Are you using A1 and A2 as table names?

Tmreic

 

[Golom]

Look at

    From   Assay A1 INNER JOIN Assay A2

We are using the same table TWICE in the query. One instance of it is called A1 and the second is A2. The A1 instance retrieves the &quot;Sucrose+Glucose&quot; record and the A2 instance retrieves the &quot;Glucose Only&quot; record so that we can take the difference.

 

[tmreic]

Golom,
Ok, makes sense know. Will give it a try and see if it works. Thanks for you help.

Tmreic

 

[tmreic]

Golom,
I gave the sql statement a try. The output was exactly what i wanted. Thanks again for you help. i would of never thought of doing the inner join part of the sql statment. i was trying to do everything with just one instance of the table. Never thought of creating two instances like you did.

Thanks again,

Tmreic