dlookup in access

  • Thread starter Thread starter ToreTraja
  • Start date Start date
T

ToreTraja

I have a table from which I want to select an inteval of rows. I then want to
summarize one field from the selected rows. The selection criteria can
identify each of the rows.
For each selection of rows there will be a different amount of rows.
How do I create a query that selects the rows that will create the answer I
am looking for.
How shall I enter the selection criteria for the first and the last row to
get all rows in between?
 
It totally depends on your selection criteria and how you will summarize the
resulting rows (which you did not tell us).
Rows have no inherent sequence; you have to specify how you want to select
them with a WHERE clause in your query.
E.g.
SELECT MyField
FROM ...
WHERE MyField >= 10 AND MyField <= 100

For the summarization you will probably need to use GROUP BY and HAVING
clauses.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Thanks,

Ok, I explain better what I want to do.
below I show a small part of the table. I want to calculate the sum of Col6
for a selection of rows.
To identify the start and end row I know Col2, Col4 and Col5,
e.g.
Start: FIJ, S, 06:20. = ID 13
End: SKH, N, 06:43 = ID 24
which would give the answer 13,793

What I want to do is to fill an Excel sheet with these three columns for
start and stop and have one or more queries that give me the result.

ID Col1 Col2 Col3 Col4 Col5 Col6
13 201 FIJ 2 S 06:20 1,391
14 201 ALB 4 S 06:22 1,466
15 201 HLU 2 S 06:24 1,158
16 201 NOR 0 S 06:25 0,689
17 201 NOR 0 N 06:30 0
18 201 HLU 1 N 06:31 0,689
19 201 ALB 1 N 06:33 1,152
20 201 FIJ 1 N 06:35 1,458
21 201 MAS 1 N 06:37 1,393
22 201 VÃ…G 1 N 06:39 1,893
23 201 VÃ…B 1 N 06:41 1,628
24 201 SKH 1 N 06:43 0,876


"Dorian" skrev:
 
SELECT SUM(col6)
FROM somewhere
WHERE id
BETWEEN
( SELECT id FROM somewhere
WHERE col2='FIJ' AND col4='S' AND col5=#06:20#)
AND
( SELECT id FROM somewhere
WHERE col2='SKH' AND col4='N' AND col5=#06:43#)




Note that I assumed col5 is a date_time field with only a time value
(hour:minute).
I assumed your table name is: somewhere.



Vanderghast, Access MVP
 
Thanks. //T

"vanderghast" skrev:
SELECT SUM(col6)
FROM somewhere
WHERE id
BETWEEN
( SELECT id FROM somewhere
WHERE col2='FIJ' AND col4='S' AND col5=#06:20#)
AND
( SELECT id FROM somewhere
WHERE col2='SKH' AND col4='N' AND col5=#06:43#)




Note that I assumed col5 is a date_time field with only a time value
(hour:minute).
I assumed your table name is: somewhere.



Vanderghast, Access MVP
Click to expand...
 
Back
Top