As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

MS Access 2013 query questions

So, I offered to do an Access DB for someone, but I am rustier at it than anticipated. I have a one table DB with various entries, and I want to be able to summarize the total number of various entries for each field for user input arbitrary date ranges. So, say, 33 males were seen between 1/1/2014 and 6/1/2014.

Also, does Access allow you to display a "homepage" with a few buttons, so an end user doesn't even need to know what a query is can navigate a bit more easily?

Thanks in advance.

Posts

  • electricitylikesmeelectricitylikesme Registered User regular
    edited September 2014
    Oh fun Access. A combination of initial "this isn't so bad" and then killing yourself.

    Onto the questions:

    Homepages: yes. You need to make a macro called "autoexec" and have it open whatever form you want it to open. This will pop up whenever you run it.

    Access supports a pretty ok chunk of SQL. What you want is a parameterized query. Best way to put these together is in VBA code with variable substitutions to set the ranges. You can also use parameterized queries, but they're a little more limited.

    So you do something like:
    Dim rst as Recordset
    strSQL = "SELECT blah.* where blah.date = " & someDate & ";"
    
    Set rst = Currentdb.OpenRecordset(strSQL)
    
    '.. some stuff ..
    
    rst.Close
    Set rst = Nothing
    

    EDIT: You can get pretty creative with this stuff, I don't know that there's any 1 true way. But modules are the way to go for everything in Access. Also get Smart Indenter if you're dealing with someone else's code.

    electricitylikesme on
  • NaphtaliNaphtali Hazy + Flow SeaRegistered User regular
    You could also create some forms in access and set them up to display on open, guiding the user to the data they want that way.

    Steam | Nintendo ID: Naphtali | Wish List
  • schussschuss Registered User regular
    Your SQL would be more
    SELECT Count(malepeople) Where Date BETWEEN (Date 1 and Date 2)
    If you want a summary, add GROUP BY groupingcolumn

Sign In or Register to comment.