Select query- calculate

[jammerdk]

I'm trying to create a select query that does the following

my Table looks as follow

ID(AutoNumber) Measuring Value
22 1A 20
23 2A 20
24 3A 15
25 4A 10
26 5A 15
27 1B 10
28 2B 5
29 3B 5
30 4B 10
31 5B 5


Ex. of what I want

1.

A1 : 20 B1 : 10
A2 : 20 B2 : 5
A3 : 15 B3 : 5
A4 : 10 B4 : 10
A5 : 15 B5 : 5

and Calculates

C1 : 10
C2 : 15
C3 : 10
C4 : 0
C5 : 10

Showing all 3 features in the same query

No A B C
1 : 20 10 10
2 : 20 5 15
3 : 15 5 10
4 : 10 10 0
5 : 15 5 10

and if B isn't there the results as follows

ex 2.

No A B C
1 : 20 0 0
2 : 20 0 0
3 : 15 0 0
4 : 10 0 0
5 : 15 0 0


and still Showing all 3 features in the same query

using the query for a listbox.

 

[dhookom]

I'm not sure why you couldn't provide the table name as well as the calculatio expression :-(
However, I my guess is correct, you could try a crosstab with SQL of:

TRANSFORM Sum(LooksAsFollows.Value) AS SumOfValue
SELECT Left([Measuring],1) AS [No], Sum(IIf(Right([Measuring],1)="B",-1,1)*[Value]) AS C
FROM LooksAsFollows
GROUP BY Left([Measuring],1)
PIVOT Right([Measuring],1) In ("A","B");

Duane
Hook'D on Access
MS Access MVP

 

[jammerdk]

I'll try to explain myself a little better.

I'm importing a textfile to my table "Measurement"

My Data in the table could be as follow with ID as a autonumber

[ID] [Measure] [Property] [Value]
34 Outer[1].[1] [Outside] 32.12
35 Outer[1].[2] [Outside] 33.12
36 Outer[1].[3] [Outside] 34.12
37 Outer[1].[4] [Outside] 33.12
38 Outer[1].[5] [Outside] 34.12
39 Inner[1].[1] [Inside] 31.12
40 Inner[1].[2] [Inside] 30.12
41 Inner[1].[3] [Inside] 32.12
42 Inner[1].[4] [Inside] 31.12
43 Inner[1].[5] [Inside] 34.00

Now I'm trying to create a Listbox that displays the records
In seperate columns and do some calculation.

Calculate Difference (Outside) - (Inside) = Diff

I've got this to work by creating 2 Select Queries ) (Thanks PHV)

SELECT Mid(O.Measure,3) AS No, (O.Value-I.Value)/2 AS Diff
FROM QryOuter AS O, QryInner AS I
WHERE Mid(O.Measure,3)=Mid(I.Measure,3);


My List should be looking like this then

No Outside Inside Diff
1 32.12 31.12 1.00
2 33.12 30.12 3.00
3 34.12 32.12 2.00
4 33.12 31.12 2.00
5 34.12 34.00 0.12



Now if my Data import in tabel looks like this

[ID] [Measure] [Property] [Value]
74 Outer[1].[1] [Outside] 32.12
75 Outer[1].[2] [Outside] 33.12
76 Outer[1].[3] [Outside] 34.12
77 Outer[1].[4] [Outside] 33.12
78 Outer[1].[5] [Outside] 34.12

Now My List should be looking like this

No Outside Inside Diff
1 32.12 0.00 0.00
2 33.12 0.00 0.00
3 34.12 0.00 0.00
4 33.12 0.00 0.00
5 34.12 0.00 0.00

And If I sure the Query I've created I get nothing

Cause there can't be made a calculation.

Hope this explains my problem.

 

[dhookom]

I would the create the Diff in a second query. Assuming your first query (qxtbForTekTips1) is:

TRANSFORM Sum(LooksAsFollows.Value) AS SumOfValue
SELECT Left([Measuring],1) AS [No]
FROM LooksAsFollows
GROUP BY Left([Measuring],1)
PIVOT Right([Measuring],1) In ("A","B");

Your second query would calculate column C:

SELECT qxtbForTekTips1.[No], qxtbForTekTips1.A, qxtbForTekTips1.B, [A]-Nz([B],[A]) AS C
FROM qxtbForTekTips1;

Duane
Hook'D on Access
MS Access MVP

 

[jammerdk]

Hi Duane

I've tried your suggestion with some success )

My imported table "Report" is looking like this

ID Measuring Value
35787879 Out[1][1] Emne Out 31,9935963027155
35787880 Out[1][2] Emne Out 31,9101684512284
35787881 Out[1][3] Emne Out 37,0079030076849
35787882 Out[1][4] Emne Out 32,0239531647089
35787883 Out[1][5] Emne Out 32,0517238454736
35787884 In[1][1] Emne In 21,9935963027155
35787885 In[1][2] Emne In 21,9101684512284
35787886 In[1][3] Emne In 22,0079030076849
35787887 In[1][4] Emne In 22,0239531647089
35787888 In[1][5] Emne In 22,0517238454736

My Query looks as follows

TRANSFORM Sum(Report.Value) AS SumOfValue
SELECT Mid(Report.[Measuring],Len(Rapport.[Measuring])-9,1) AS No
FROM Report
GROUP BY Mid(Report.[Measuring],Len(Report.[Mesuring])-9,1)
PIVOT Mid(Report.[Measuring],Len(Report.[Measuring])-9,1) In ("Out","In");


My output looks like this

No Out In
1
2
3
4
5

as you see it doesn't return any of my values |

My Calculation Query looks like it might be working but I'm actually cause I haven't got any values to calculate yet ;o)


Hope you can HELP!!!!

 

[dhookom]

You can't use the following for all records since Out has more characters than In.
Mid(Report.[Measuring],Len(Rapport.[Measuring])-9,1)


Duane
Hook'D on Access
MS Access MVP