Results 1 to 11 of 11
  1. #1
    Masterfinn is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    7

    MSACCESS Query with calculations?


    Good day, I am a beginner with MSACCESS and would like some advice on creating a query/report. I have a table in my DB that i would like to run a query on. The table has a few columns but i would like to focus on only 2, which contain dates.

    I want to run a query/report that will show me the following:

    First i would like the query/report to prompt the user for a 'start date' and and 'end date'
    Then i would like the query/report to somehow use these dates and calulate the number of items in the table that are between these dates and produce that value on the screen.
    - I was able to get the input dates prompt setup in the query but it returns a list of all the records that it finds between these two dates(which is ok i guess cause i can just look on the screen to see how many records it returend to get the total), but is it possible to have it just produce the one number that shows the total?

    So far i have only created a basic query for this with the information below: I havent started on the report yet.

    Field: Order#
    Table: orders_tbl

    Field: OpenDate
    Table: orders_tbl
    Sort: Ascending

    Field: CloseDate
    Table: orders_tbl
    Criteria:Between [Start Date (MM/DD/YYYY)] And [End Date (MM/DD/YYYY)]

    *The information i have listed in the criteria above is what prompts the user to enter the dates that the query will go through the table and show me the list of all the orders that were closed between those dates. But like i mentioned earlier i would like it to just show me a total number value and not a list of all the orders. I have tried to use the count and dcount with no luck

    Also to expand on this query, is it possible to produce a total number of entries that have a specific number of days between them?
    - for example if the record has a startdate of A and and enddate of B and if there is 10 or less days inbetween. I would like the query/report to give me another total number value of all the records that fall in that range. Again I have played around with doing some datediffs, counts and grouping but i havent had any luck

    Please let me know if this is possible or not with a basic query/report.

    I believe in order to do what i need to, i have to some how get these user input dates into 'variables' that i can use and calculate against but thats were i am getting confused on how to do this within MSACCESS. Probably need to do a module or access page of some sort, but I would just like some suggestions on were to get started.

    Just to give you some background on this. We currently have this database with a web front end to input the data and report on the data using HTML, JSCRIPT and VBSCRIPT. It works great, but unfortuantly we are not able to keep the server environment and need an MSACCESS only solution. We have already created an access form to enter and manipulate the data directly but we are trying to create the same types of reports we had before but its not quite working correctly.

    Any sugestions would be greatly appreaciated

    Thanks
    MF

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is untested, but I think it will work:

    SELECT Count(*) As HowMany
    FROM TableName
    WHERE CloseDate Between [Start Date (MM/DD/YYYY)] And [End Date (MM/DD/YYYY)] AND DateDiff("d", StartDate, CloseDate) < 11

    If it does, it should give you a head start on the others?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Masterfinn is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    7
    Thanks for the quick reply

    yes that appears to work for the count however is there a way i can run seperate it out in the same query?

    for example:
    SELECT Count(*) As HowMany
    FROM TableName
    WHERE CloseDate Between [Start Date (MM/DD/YYYY)] And [End Date (MM/DD/YYYY)]


    Which will provide me with the total of entries between the two dates, but then is it possible to add another entry in the query to produce the total numbers of items under 10 days using this:

    AND DateDiff("d", StartDate, CloseDate) < 11

    basically i want the query to show me two numbers. One that shows the total count of all the orders between the dates. And another number that will show from those orders how many were closed 10 days or sooner from when they were opened.

    Thanks
    MF

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, but it would be a subquery. I'd more likely do it on a form or report with a DCount(), depending on the actual final goal. The subquery method would look like:

    Code:
    SELECT count(*) As HowMany, 
         (SELECT count(*) As HowMany FROM tblReservations
         WHERE tblReservations.CallDateTime Between #2/20/2010# And
         #2/23/2010# and datediff("d", CallDateTime, ReqDateTime)>10) AS HowMany10
    FROM tblReservations
    WHERE tblReservations.CallDateTime Between #2/20/2010# And #2/23/2010#
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    Since there are no records being displayed...I would recommend creating a dynamic Form using Unbound fields.

    Here is a sample ScreenShot of a Dynamic Form:

    Attachment 705

    In the AfterUpdate Event of each date field:

    1. Evaluation if both dates have been entered by the user.

    a) If this condition is False, then do nothing until the user updates both dates.

    b) If this condition is True, then dynamically update the results in the last two fields per the sample Form.

    This requires you to create two Queries:

    a) First Query gives you the Total Orders

    b) Second Query gives you the Orders Less < 11 Days

    As pbaldy mentions...you would need to use the DCount Function an apply it towards each of the above Queries to give you the results you seek.

    -RC

  6. #6
    Masterfinn is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    7
    Thanks so much for the info. I believe you are correct and that doing a form would be the best way to do this. I will go though it with your suggestions and see If i can make it work. I will post back once i have an update.


    Thanks again,

    MF

  7. #7
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    The following are the Query screenshots:

    qryTotalOrders:

    Attachment 706

    qryOrdersLess11Days:

    Attachment 707

    Hope this helps.

    -RC

  8. #8
    Masterfinn is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    7
    Thanks for the screen shots !!! excellent help!!

    however i guess im not quite clear on how to open the record set and use the query properly within the dynmaic form.
    Here is a snippet from my code:

    Dim myDB As Database
    Dim myRS As recordset
    Dim SQL As String

    SQL = "Select * from orders_tbl where ..............

    'is this the correct way to enter this statement? and how would i reference the query?

    Set myDB = CurrentDb
    Set myRS = myDB.OpenRecordset(SQL)

    'do my stuff

    myRS.Close
    myDB.Close


    *** Sorry about this, but I am used to vbscript (asp pages) and not so much with access/vb code, and the syntax is a bit different... But im learning ..

  9. #9
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    Once you have your Queries and Form built:

    1. Ensure you have the proper formatting in your Form's Text Boxes per the following screenshot:

    Attachment 708

    2. Build a simple Macro per the following screenshot:

    Attachment 709

    a) The First SetValue Macro Action should have an Item and Expression in the Action Arguments:
    - Item: [ContolNameTHIRDTextBox]
    - Expression:

    Code:
    DCount("[Order#]","qryTotalOrders")
    b) The Second SetValue Macro Action should have an Item and Expression in the Action Arguments:
    - Item: [ContolNameFOURTHTextBox]
    - Expression:

    Code:
    DCount("[Order#]","qryOrdersLess11Days")
    c) Save, Name, and Close Macro

    3. Insert the Macro's Name into the AfterUpdate Event in your Form (BOTH First and Second Text Boxes)

    Let us know if you need further assistance.

    -RC

  10. #10
    Masterfinn is offline Novice
    Windows 7 Access 2003
    Join Date
    Feb 2010
    Posts
    7
    Awesome thanks!!! I went through creating the query's and macros and it works!!!! Im so excited. Now I'm just giong to work on getting my form to look a little more pretty.. haha but as for the calculations and generating the correct totals, it is working like a charm

    Thanks again for all the help!!

  11. #11
    MAF4Fam6's Avatar
    MAF4Fam6 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Fruit Heights, Utah USA
    Posts
    140
    You're very welcome!
    Glad we could help.

    -RC

    If your issue is Resolved!...please mark the thread as Solved.
    Click here to learn how!
    Thanks...

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

Similar Threads

  1. Replies: 19
    Last Post: 08-07-2010, 05:38 PM
  2. Compare msaccess data
    By dref in forum Queries
    Replies: 10
    Last Post: 05-14-2010, 05:35 AM
  3. msaccess fault error
    By msmoore in forum Access
    Replies: 4
    Last Post: 02-09-2010, 03:31 AM
  4. Calculations in Access
    By dominick in forum Access
    Replies: 0
    Last Post: 07-28-2009, 07:39 AM
  5. MSACCESS Query
    By saa18 in forum Access
    Replies: 0
    Last Post: 11-14-2008, 05:12 AM

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