logical queries in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I'm trying to summarize some numeric data based on its location
relative to a label field. I have a set of surveys. For each survey there
are three relevant columns of data (LABEL, DISTANCE, and DEPTH). Not all the
values have labels, and each survey has a different number of entries.
What I'd like to do is be able to sum the depths BETWEEN two labels so a
script would have to look for the start label (LBF) - then go to the next
cell (LBF + 1) and begin summing depth values until the value one cell before
the stop label (RBF - 1). For any given survey this can be done easily with
an expression. But I have been unable to get the start-stop pieces to work.
A sample is below. thanks - e

LABEL DIST_LB DEPTH
LBPIN 0 99.32
0.5 99.24
LBF 0.95 99.08
BLB 0.95 98.63 --- Start summing here
LEW 1.15 98.62
1.55 98.57
1.95 98.57
2.35 98.61
2.75 98.58
3.15 98.55
3.55 98.55
3.95 98.45
T 4.35 98.41
4.75 98.42
BRB/REW 5.15 98.61
RBF 5.4 98.88 --- Stop summing here
5.9 98.91
6.4 98.95
6.9 99.02
7.4 99.13
RBPIN 7.91 99.1
 
From your comments and the data display it seems like this is an Excel file
you are talking about. If so this is Access database newsgroup.

Also you have commented as to start & stop summing but the label appears to
change three times in between.
 
Yes - this is currently an Excel spreadsheet that is being put into a
database. I'd like my Access database to be able to store the raw data
(which it does) and perform needed calc's on command. The question is - how
to write the calcs.

The changes in the label field are the heart of the problem. If the label
field were binary (include/exclude) I could just tell it to sum all the
included values. Instead I have a start label (LBF) and a stop label (RBF)
and some other labels, including blanks, in between.

Thanks for helping me clarify the question Karl. Is this the wrong group
for this topic?
 
Access needs all records to be labeled to perform the calculations correctly.
It needs to check the label of each recod to include the value in the sum or
to exclude the value.

I think you best use Excel to do this one.
 
It seems like I should be able to use something like the Seek method to set
temporary variables for the start and end row - then have a loop that sums
the values in between. This would eliminate the need for the program to have
to search for each record based on a label... I think.
 
Back
Top