Results 1 to 12 of 12
  1. #1
    RB85 is offline Novice
    Windows 11 Access 2016
    Join Date
    May 2023
    Posts
    25

    Help Getting Started?!

    Hi All,

    I used Access many years ago but have almost completely forgotten how to use it! I'm using Excel at the moment for a database and to display line plots of trends over time but the file is becoming too big to manage. Would someone be willing to help me get started or direct me to a good tutorial that moves at a fast pace?



    My data consists of monitoring instrumentation at numerous locations that takes readings every 12 or 24 hours. Each monitoring location has multiple sensors that have individual calibration values. Readings from each sensor are converted to a pressure reading using the calibration values. I then want to display the change in pressure at each sensor with time on a line graph. A form would be useful to enter new readings into each week for each sensor.

    If anyone has some tips on the basic set up and tutorials for this kind of database it would be appreciated!

    Thanks,

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you post some screen shots of your spreadsheets?

    What are the calibration formulas? Do the sensors all use the same formula?

  3. #3
    RB85 is offline Novice
    Windows 11 Access 2016
    Join Date
    May 2023
    Posts
    25
    Screenshot of one location is attached. All sensors use the same formulas just changing the calibration constants for the respective sensor.

    Click image for larger version. 

Name:	Spreadsheet1.jpg 
Views:	28 
Size:	133.6 KB 
ID:	50271Click image for larger version. 

Name:	Spreadsheet2.jpg 
Views:	28 
Size:	117.2 KB 
ID:	50272

  4. #4
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    It would be easier to show you an example if you attached the excel file so that you can use it to report the data in Access.

  5. #5
    RB85 is offline Novice
    Windows 11 Access 2016
    Join Date
    May 2023
    Posts
    25
    Example for one location attached.
    Attached Files Attached Files

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    What data types would be these values?
    A = -2.0652810E-04
    B= 1.0303170E-01
    C= 1.5717660E+03
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #7
    RB85 is offline Novice
    Windows 11 Access 2016
    Join Date
    May 2023
    Posts
    25
    Quote Originally Posted by mike60smart View Post
    Hi
    What data types would be these values?
    A = -2.0652810E-04
    B= 1.0303170E-01
    C= 1.5717660E+03
    Scientific Number.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    On fly!

    tblLocations: LocationID, ...;
    TblSensors: SensorID, SerialNo, SensorElevation, A, B, C, Installed;
    tblSensorLocations: SensorLocationID, SensorID, LocationID;
    tblSensorRecordings: SensorRecordingID, SensorID, RecordingDatetime, Frequency, Temperature.

    You need a saved query , which reads info from all those tables, and calculates values of PressurekPA, PressuremH20, and Elevation for every sensor recording.

    In case there is a need for users to access the DB directly, then preferably split the app to front-end (FE) and back-end (BE) - with BE stored on network resourse with access rights set for users.

    For displaying graphs, I'd prefer Excel app, which reads data from BE using SQL query (simply because graphs in Excel look out better and are more manageable). The query must be set to be refreshed on opening, and must read data from saved query in Access DB. Read date are stored on hidden (or very hidden) sheet of Excel app. The excel app does have also hidden sheets, where the list of locations, and list of sensors are read from Access DB by ODBC queries. You have to define the list of locations as dynamic Named Range, which you then can use as source of Data Validation List on report sheet (which is used to limit the data read from DN to this location). In case you want on report sheet to display graph for single sensor, then you need another hidden sheet with a table, where the list of sensors in this location is created, and which must also be defined as dynamic Named Range (and on export sheet you use a Data Validation list, which then limits the query to this sensor data - or you use the location ID selected on report sheet as filter condition for sensors query. If you want on report sheet several graphs which display the info for all sensors in this location, you may skip the previous step (but this design may be problematic, when the number of sensors differs for different locations).

    On report sheet, you probably need also fields, where you can limit the time period of data read from DB (start and end dates, or day, or month, or whatever). All these setup fields on Report sheet are used as sources for conditions in measurement data query, and the query must be refreshed whenever any of those values are changed.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Arvil, I'm curious what your thoughts are behind the many-to-many relationship between sensors and locations? Is it to allow for moving sensors?

    Maybe instead of an installed field in the sensor table OP could do something like date_in_service and date_out_of_service attached to the junction table.
    Last edited by kd2017; 05-26-2023 at 08:37 AM. Reason: clarity

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    a) Having all location info declared in a separate table allows to use combobox when linking location to sensor. This prevents the risk making typos;
    b) OP may want to add some additional info for locations, like building name, room code, device name, etc. (even when not now, then possibly in future). When this happens, and the the location is simply described in sensor table, OP has to redesign the DB, as having any info except FK inserted only once is a general rule for databases;
    c) and of-course, having validity period saved in junction table is a possibility (or having a status field there instead, in case sensors never will be moved to another location). I didn't cover it in my post, to avoid it being too complex

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Thanks for the info Arvil!


    OP, is this actual data? How are readings collected/ what's your data entry process? I ask because I notice that for each row the date/time for each sensor is the same.

    At the risk of asking a silly question, do the calibration constants ever need to change or be recalibrated over time? Are the sensors ever moved to different locations or elevations?

  12. #12
    RB85 is offline Novice
    Windows 11 Access 2016
    Join Date
    May 2023
    Posts
    25
    Many thanks Arvil for the tips! I'll try to start building the database.

    kd, it is actual data from pore pressures sensors installed below ground to identify groundwater conditions. They automatically take readings at set times and that data is downloaded and copied into the spreadsheet. The calibrations do not change but there are occasional errors when installing and these might not be picked up for a while so need to be editable. Sensors are never moved from their locations as they are installed deep underground.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Where to get started
    By cowmoogun in forum Programming
    Replies: 12
    Last Post: 01-03-2013, 07:44 PM
  2. Please help get me started
    By jwalter007 in forum Database Design
    Replies: 4
    Last Post: 08-25-2012, 04:32 AM
  3. Need a little guidance to get started.
    By cashflo in forum Access
    Replies: 3
    Last Post: 01-19-2012, 12:10 PM
  4. just getting started
    By sbrooks1763 in forum Database Design
    Replies: 7
    Last Post: 11-28-2011, 03:58 PM
  5. how to get started
    By scottsoo9 in forum Access
    Replies: 1
    Last Post: 11-18-2009, 04:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums