Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Macros / October 2005

Tip: Looking for answers? Try searching our database.

logical queries in Access?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Alaska Hydro - 14 Oct 2005 21:03 GMT
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
KARL DEWEY - 15 Oct 2005 00:19 GMT
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.  

> 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
[quoted text clipped - 29 lines]
>         7.4    99.13
> RBPIN    7.91    99.1
Alaska Hydro - 24 Oct 2005 20:11 GMT
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?

> 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.
[quoted text clipped - 35 lines]
> >         7.4    99.13
> > RBPIN    7.91    99.1
KARL DEWEY - 24 Oct 2005 20:50 GMT
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.

> 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
[quoted text clipped - 48 lines]
> > >         7.4    99.13
> > > RBPIN    7.91    99.1
Alaska Hydro - 26 Oct 2005 19:38 GMT
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.

> 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
[quoted text clipped - 54 lines]
> > > >         7.4    99.13
> > > > RBPIN    7.91    99.1
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.