Query question??

  • Thread starter Thread starter Christos via AccessMonster.com
  • Start date Start date
C

Christos via AccessMonster.com

I have a table with multiple rows.
1 2 3
4 5 6
7 8 9
I want your help to create a query which will appear as result summaries the
number of the line + the previous

Can you help me please???

Christos
 
You provided 3 sample records. I don't think it would take too long to
provide a sample of how you would expect your results to display. Keep in
mind there is no such thing as "previous" unless you can specify an order
value.
 
Dear Mr Hookom

The exact table looks
1/1/2006 1 2 3
2/1/2006 4 5 6
3/1/2006 7 8 9
4/1/2006 1 2 3
5/1/2006 4 5 6
6/1/2006 7 8 9

The query shall give me the result as
1/1/2006 1 2 2 div 1
2/1/2006 4+1 5+2 (5+2 div 4+1)
3/1/2006 7+4+1 8+5+2 (8+5+2 div 7+4+1)
etc
4/1/2006 1 2 3
5/1/2006 4 5 6
6/1/2006 7 8 9

I hope you can help me
Christos P.
 
If I understand correctly, try something like:
SELECT tblLooks.Field1, tblLooks.Field2,
tblLooks.Field3, tblLooks.Field4,
(SELECT Sum(Field3)
FROM tblLooks L
WHERE L.Field1<=tblLooks.Field1) /
(SELECT Sum(Field2)
FROM tblLooks L
WHERE L.Field1<=tblLooks.Field1) AS SumField3_d_SumField2
FROM tblLooks;
 
The exact table looks
Date Profit Insert Temp
15/2/2006 1 2 3
16/2/2006 4 5 6
17/2/2006 7 8 9

Your query you send me has results
Date Profit Insert SumField3_d_SumField2
15/2/2006 1 2 2
16/2/2006 4 5 1,25
17/2/2006 7 8 1,14285714285714

Instead of this I want the second Profit and Insert to be the sum of 1+4 and
2+5, the
third Profit and Insert will be the1+4+third Profit and 2+5+third Insert so
the query will look
Date Profit Insert SumField3_d_SumField2
15/2/2006 1 2 2
16/2/2006 5 7 1,4
17/2/2006 12 15 1,25

Can you help me with this???
 
I tried my same exact SQL with your field names and got a display of:

Date Profit Insert SumField3_d_SumField2
1/1/2006 1 2 2
1/2/2006 4 5 1.4
1/3/2006 7 8 1.25
1/4/2006 1 2 1.30769230769231
1/5/2006 4 5 1.29411764705882
1/6/2006 7 8 1.25

This is the SQL with your field names.
SELECT tblLooks.[Date], tblLooks.[Profit],
tblLooks.[Insert],
(SELECT Sum([Insert])
FROM tblLooks L
WHERE L.[Date]<=tblLooks.[Date]) /
(SELECT Sum(Profit)
FROM tblLooks L
WHERE L.[Date]<=tblLooks.[Date]) AS SumField3_d_SumField2
FROM tblLooks;
 
Mr Hookom

First of all a big thank you for your advices.
I would like to ask you something in the same query but more complex....
The table now looks

Date Profit1 Insert1 Profit2 Insert2 SumField3_d_SumField2
1/1/2006 1 2 1 2
1/2/2006 4 5 4 5
1/3/2006 7 8 7 8
1/4/2006 9 10 9 10
1/5/2006 11 12 11 12
1/6/2006 13 14 13 14

The query results will be something like (according with the above table)

Date Day_Sum_Prof Day_Sum_Ins Per_Cent Prof_1
Ins_1 % Prof_2 Ins_2 %
1/1/2006 1+1 2+2 (2+2) /
(1+1) 1 2 2/1 according the
1/2/2006 4+4 5+5 (5+5)
/ (4+4) 1+4 2+5 (2+5)/(1+4) above table
1/3/2006 7+7 8+8 (8+8)
/ (7+7) 1+4+7 2+5+8 ........ and the
1/4/2006 9+9 10+10 (10+10) / (9+9)
previous
1/5/2006 11+11 12+12 (12+12) / (11+11)
columns
1/6/2006 13+13 14+14 (14+14) / (13+13)

Is it possible to help me????

Thank you in advance
Christos



Duane said:
I tried my same exact SQL with your field names and got a display of:

Date Profit Insert SumField3_d_SumField2
1/1/2006 1 2 2
1/2/2006 4 5 1.4
1/3/2006 7 8 1.25
1/4/2006 1 2 1.30769230769231
1/5/2006 4 5 1.29411764705882
1/6/2006 7 8 1.25

This is the SQL with your field names.
SELECT tblLooks.[Date], tblLooks.[Profit],
tblLooks.[Insert],
(SELECT Sum([Insert])
FROM tblLooks L
WHERE L.[Date]<=tblLooks.[Date]) /
(SELECT Sum(Profit)
FROM tblLooks L
WHERE L.[Date]<=tblLooks.[Date]) AS SumField3_d_SumField2
FROM tblLooks;
The exact table looks
Date Profit Insert Temp
Click to expand...
[quoted text clipped - 20 lines]
Can you help me with this???
Click to expand...
Click to expand...
 
It's near impossible to determine what you want. I normally don't spend any
time working on solutions for tables that look like they should first be
normalized prior to doing anything else.

--
Duane Hookom
MS Access MVP
--

Christos via AccessMonster.com said:
Mr Hookom

First of all a big thank you for your advices.
I would like to ask you something in the same query but more complex....
The table now looks

Date Profit1 Insert1 Profit2 Insert2 SumField3_d_SumField2
1/1/2006 1 2 1 2
1/2/2006 4 5 4 5
1/3/2006 7 8 7 8
1/4/2006 9 10 9 10
1/5/2006 11 12 11 12
1/6/2006 13 14 13 14

The query results will be something like (according with the above table)

Date Day_Sum_Prof Day_Sum_Ins Per_Cent Prof_1
Ins_1 % Prof_2 Ins_2 %
1/1/2006 1+1 2+2 (2+2)
/
(1+1) 1 2 2/1 according the
1/2/2006 4+4 5+5 (5+5)
/ (4+4) 1+4 2+5 (2+5)/(1+4) above table
1/3/2006 7+7 8+8 (8+8)
/ (7+7) 1+4+7 2+5+8 ........ and the
1/4/2006 9+9 10+10 (10+10) /
(9+9)
previous
1/5/2006 11+11 12+12 (12+12) /
(11+11)
columns
1/6/2006 13+13 14+14 (14+14) /
(13+13)

Is it possible to help me????

Thank you in advance
Christos



Duane said:
I tried my same exact SQL with your field names and got a display of:

Date Profit Insert SumField3_d_SumField2
1/1/2006 1 2 2
1/2/2006 4 5 1.4
1/3/2006 7 8 1.25
1/4/2006 1 2 1.30769230769231
1/5/2006 4 5 1.29411764705882
1/6/2006 7 8 1.25

This is the SQL with your field names.
SELECT tblLooks.[Date], tblLooks.[Profit],
tblLooks.[Insert],
(SELECT Sum([Insert])
FROM tblLooks L
WHERE L.[Date]<=tblLooks.[Date]) /
(SELECT Sum(Profit)
FROM tblLooks L
WHERE L.[Date]<=tblLooks.[Date]) AS SumField3_d_SumField2
FROM tblLooks;
The exact table looks
Date Profit Insert Temp
Click to expand...
[quoted text clipped - 20 lines]
Can you help me with this???
Click to expand...
Click to expand...
Click to expand...
 
Back
Top