Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2006
    Posts
    10

    Exclamation Unanswered: Statistical data in Access ?

    Hello everyone, I was just wondering, the specs for my new project (in Access 2003) is to create a tracking program for military applicants in their initial processing. What I need to be able to do is calculate the number of errors generated by each recruiting station, and turn that statistical data into a report that can be generated by month, station, or zone. Currently, I have pop up forms that will allow the user to choose a stat page by each recruiting station, but I don't see a way to get the fields to calculate the data i need. There is a lot of it I can't release due to privacy and sensativity regulations, but the gist of it is :
    1-I dont see any way to get Access to generate statistics. Is there a way to do this programmaticaly?
    2-if anyone knows how to disable the mouse wheel so that it will not scroll through records in Access 2003, that would be helpful as well
    3- It would be great if there was a way to generate a custom error message if say, the user searched for a member by SSN, and the SSN was not found. Right not it keeps giving me a macro halt screen, then doesnt want to close out the query (I used a query for the search by SSN option)

    I really appreciate any help or advice anyone has.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1) Yes and yes. You will have to give a little more information though. I doubt the mathematics is classified right?
    2) Loads of techniques on the web:http://www.google.co.uk/search?hl=en...l+access&meta=
    3) I'm no good with macros but it sounds like you are doing more than opening a query. Do you just mean you want to check that a query will return a result BEFORE you run it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    10

    What I am missing...

    Okay, what I am trying to do is calculate percentages of discrepancies in applicant records and where they are coming from. Basically, each district is broken down into zones, and each zone is broken down into stations. From the total number of discrepancies in that table, I need to be able to calculate how many discrepancies are being generated for each district, zone and station, respectively.

    Also, while searching for a ssn, I used a Query. What I want it to do is return a msg box if the specific ssn searched for was not found. It would be nice to give an option, such as Add New Applicant, or Search Again within the msg box (would this be another form?)

    I would also like to be able to break down the discrepancy TYPES by district, zone, and station as well. Does that help?

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    okay, here's some info you you:

    calculate percentages of discrepancies in applicant records and where they are coming from. Basically, each district is broken down into zones, and each zone is broken down into stations. From the total number of discrepancies in that table, I need to be able to calculate how many discrepancies are being generated for each district, zone and station, respectively.
    One approach is to do this using a "totals" query and group by the items you mention. You have functions such as Count, Sum, Average, etc. avaliable.

    You can also use a report, which operates a lot like a totals query when you create groups. The wizards can walk you through the basic set and you tweak from there. the nice thing about a report is that it let's you do math on larger grouups and smaller groups at the same time. For example, if I have a table of phone numbers and I want to count the number of phone numbers ending in a 7 by Area Code and by Exchange, I can use a report that groups by Area Code, and then groups by Exchange WITHIN the Area Code group.

    You have a bunch of functions available, which you can expose by clicking the elipses (the 3 dots) on the right side of the control source property and choosing expression builder. If you need something not in Access, you can make a custom function in code, and even utilize the function library of Excel if you want to setup automation.

    Also, while searching for a ssn, I used a Query. What I want it to do is return a msg box if the specific ssn searched for was not found. It would be nice to give an option, such as Add New Applicant, or Search Again within the msg box (would this be another form?)
    And you get a macro execution halted for what reason? In any case, convert your macros to code and start using code - you'll start doing more faster.

    Searching for a value and then adding it if it wasn't found is more than a few lines of code. I suggest using a form to accomplish that. In fact, there's a bunch of stuff in the help files about using a Combo box with "Limit to List" and adding values n ot found.

    Chew on this, try a few things out and then chack back with additional questions.

    Good luck!

    tc
    Last edited by tcace; 06-29-06 at 22:41.

  5. #5
    Join Date
    Feb 2006
    Posts
    10

    Code library?

    Is there a place online where I can learn codes? I am not familiar with coding, thats why I have been using macros, etc. If there were a library, or cheat book somewhere, I could probably figure it out.

    Thanks

    AzChick

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out Access and VB help.

    The DoCmd object in VBA is basically the object that provides VB access to the macros you have been using. If you type DoCmd. then a drop down list will appear of all the available commands. You will recognise many of these from your macros. Intellisense (a guide that tells you the various parameters) will help you.

    For VB you could try (not visited in a long time):
    http://www.garybeene.com/vb/tutor.htm
    DoCmd is Access specific - it is not a part of the core VB language so you won't find it here but it is a very easy library to use.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Also, while in any module window, press F2. This pulls up the "Object Browser" which does just that - it let's you browse all of the objects (and methods, functions, and constants) of any loaded libraries (which will be Access and VBA by default).

    You can search, and then click help for more info.

    have fun,
    tc

  8. #8
    Join Date
    Feb 2006
    Posts
    10

    Trying to find a way...

    I have searched through the help files before posting here, and either I entered the wrong keywords, or I missed it, but I am having trouble getting this form to work the way I want. I have a form for searching, as per an earlier suggestion, in Access 2003, I want to click a command button and set the onclick function to search for a value, in this case, a SSN, then return an option choice to either add a new record with the entered SSN, or search again if the SSN is NOT found. When the searched SSN is found, I want another form to open to that particular record. Sounds like a lot, but I can't get it to work using macros, and I know that VB is faster. Any suggestions?

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Now you've asked a few entirely new questions.

    You're going to have to learn a few snippets of programming to do this.

    You'll eventually want to learn how to use "RECORDSETS", but for now, you can search for values using "domain aggregate" functions, specifically "DLOOKUP" and "DCOUNT" will help you with what you asked for.

    To add new values without using recordsets, I suppose you could simply use "DoCmd.GoToRecord acDataForm, Me.Name, acNewRec" and place the new value in the appropiate field. Use "Me.Refresh" to ensure the data gets written back before you move on.

    To open a different form and show a specific record, you'll need to write a filter in psuedo SQL and add it to the "DoCmd.OpenForm" command.

    Each item in blue is Access code and there will be plenty of info in the help files, www.MSDN.microsoft.com and this forum for each.

    God luck,
    tc

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tcace
    ...."DoCmd.GoToRecord acDataForm, Me.Name, acNewRec"......."DoCmd.OpenForm"....
    And if you were to create a new macro you will see these two options with the same arguements. Anything you can do in a macro you can do with DoCmd (AFAIK). Of course you can do much much more too but it does mean that converting from macros to code is actually not that daunting.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •