Query question??

  • Thread starter Christos via AccessMonster.com
  • 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
 
D

Duane Hookom

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.
 
C

Christos via AccessMonster.com

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.
 
D

Duane Hookom

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;
 
C

Christos via AccessMonster.com

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???
 
D

Duane Hookom

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;
 
C

Christos via AccessMonster.com

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...
 
D

Duane Hookom

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...
 
Top