1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Create Access database

Discussion in 'Business Applications' started by Zack Barresse, Jan 25, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Zack Barresse

    Zack Barresse Thread Starter

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I'm looking to start creating some of my files in Access. Everything I have for my work is done in Excel, but I think some of it should be in Access. I do not know Access very well, but would like to learn. I would like to start with something fairly easy. I am using Access 2003.

    One of my job duties is to input data for (water) meter readings. These readings are read approximately weekly and I need to generate a report monthly. Sometimes the data will come in sporadically and possibly multiple times per week (maybe once per day at times).

    The report that I would need to see is - and it would be nice if I could choose the month to report on as well - assuming we will use the current month, every meter read as of today or the last day of the month, and find the difference between the readings from the first day of the month. So Jan 31 reading minus Jan 1 reading. I could make the date the unique key as there will always only be one reading per day. I would need the totals for each meter, who the meter belonged to and what duration (month) the information was retreived from. I'm not worried about how it looks, as long as it's there. I have this in Excel, but it's cumbersome and I think it's the wrong program.

    There are other things I use these values for. Some of the metered readings are used to find irrigation flows for farm circles. Those are currently kept in another spreadsheet (there are lots of these spreadsheets). These daily gallon figures are then used in other calculations where each day has several other possible inputs, these inputs are hours put into different cells (locations). These figures are used with another daily number to obtain a final daily loading figure.

    These do work in Excel, so I'm not in a rush to get this, its currently in operation. My line of thinking was that it would be better suited for an access database. I would love some additional input on this.

    Can somebody please help me setup a database that will assist in this area? I can manage the table, but that's about it. I realize this is a large project overall; if able to keep this in small bits, maybe it will be easier. :confused:
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    firefytr, it would be a pleasure to help a fellow poster, I will pm my email address and we can discuss exactly what your requirements are. A good starting place though is to go in to Access and open a blank Database. From the main menu select File>Get Extrnal Data>Import
    then navigate to one of your Excel spreadsheets, the water reading one would be a good place to start. Click on the worksheet and then follow the Import Wizards instructions to create a "New Table". Access does it all for you..
    You could try a few different spreadsheets and it will give you an idea of how the spreadsheet data needs to be stored for a smooth import in to Access.
    If you have headings they must be at the top of the excel sheet on just one line, Access will create the table field names for you from them.
     
  3. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Hey Zack, just a few things:

    I know you're cognizant of this, I just want to reiterate a point. Don't think of Excel tables and Access tables in the same vein. I've noticed posting in other forums that people with a lot of Excel knowledge tend to misuse relational database structure because Excel doesn't inforce normalization practice. OBP also knows this, and his suggestion is a good way to get some practice with tables and the wizard, but keep in mind that most likely your standing application will not port to Access as-is. Like I said, I know you realize that, just stressing it.

    I wouldn't recommend this. I don't think a date is unique enough to make it a primary key. It is possible that a date datum could be a candidate key, and in conjunction with a meter ID it could be a composite primary key; determining keys is crucial to a successful design. More reading here (from AccessVBA); a paper by the author of that FAQ.

    I'd strongly recommend the Access 2000 Developer's Handbook, vols. 1-2, by Getz, Litwin, and Gilbert, published by SYBEX. I know you have ACC2K3, but ADH is the quintessential discussion of just about everything you'll want or need to know about Access. You can get the 2500 p. two-volume set for about US $100 at Amazon.com. I'll also recommend The Access Web and AccessVBA.com.

    And of course, if you have any questions and I'm able to help, I'll do what I can.

    GL

    chris.
     
  4. Zack Barresse

    Zack Barresse Thread Starter

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Thank you very much guys, I really appreciate the help here. I'm running out of the office in a sec (only had a quick minute to post), just didn't want to leave you hanging. :)
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Just ran across this thread - actually sounds rather interesting (okay, I live in the Midwest, anything is interesting) - I wondered if you got this all built and are using it now? I would guess that it is a lot easier, in the long run, than using a bunch of spreadsheets. Love both programs, but this really sounds like a database task.
     
  6. Zack Barresse

    Zack Barresse Thread Starter

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hey slurpee55,

    Thanks for your post. :)

    I did get this off the ground and have the database started. Right now I have all the water meter data in there and have the last two years of data input into a table. I also have a form for entering the data and some queries to append/check the information.

    The problem with this is, the scope of it goes way beyond just meters. There are chemical values to track, hydraulic loading, crop rotation/yield values, etc. It all needs to be tracked on a weekly basis, we do it daily. I'd love to get it all in Access, and I'll be working on doing this over the course of this year.

    I think this project is too large for any person to volunteer their time for on any free forum. But piece-mealing it together might be an option. I suggested to my employer that they hire somebody to create it, but I don't think they're going for it. :(

    It's going to be a work in progress. So I may be back with other issues. :)
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I can believe it - coming from farm country and having been involved lately in trying to develop a lake for a water source, I have just gotten into the fringes of all the things that they monitor re. water - usage, chemicals, etc. Sheesh....
    Good luck!

    Loche
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/437286

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice