Results 1 to 11 of 11
  1. #1
    9944pdx is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44

    T-SQL Statement using the AVG function


    Using 3rd party software; the software requires a SQL statement. Looking to find AVG count for records between two date columns for the current year. Using a datecreate and current_timestamp.

    Thanks folks.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    See if the W3Schools site can help starting with: https://www.w3schools.com/sql/sql_count_avg_sum.asp
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    9944pdx is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    That is my first go to place, but it didn't work for me. I am having issues keeps giving me an error; "looking for an argument"!~

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Are you using SQL Server i.e. does it have to be T-SQL?
    Can you post your SQL statement and indicate what part gives the error
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    Click image for larger version. 

Name:	dear xxxhouse_1-11-2018.gif 
Views:	16 
Size:	11.3 KB 
ID:	32072
    See attached.

  6. #6
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    It doesn't have to be TSQL.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Surely it should be AVG(YourFieldName) not AVG().
    That is the missing argument
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    I should be able to achieve results for a count of records between date field columns, correct?

    AVG (datediff(week,[columndate_1, columndate _2)

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have you tried it? Did it work?
    If not what did happen?

    Suggest you post some example data
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    9944pdx is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2011
    Location
    PDX
    Posts
    44
    First thanks for your help.
    Second, the 3rd party software is limited if you use their setup.
    Third, you can use the 3rd party setup without their template(s) and it worked.
    Now, for the bonus question , the below statement works. But how do I get the avg per week for only 1 year?
    SELECT AVG(DATEDIFF(week,DATE_CREATED, CURRENT_TIMESTAMP ))
    FROM RI_RECORD
    WHERE R_STATUS = 'A'
    AND INSP_STATUS = 'W'

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    See if my answer in this thread is of any help:
    https://www.accessforums.net/showthread.php?t=70092
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Adding a Count function to current select statement
    By johnson8809 in forum Queries
    Replies: 2
    Last Post: 02-21-2015, 07:32 PM
  2. Dlookup with a function in the where statement
    By nigelbloomy in forum Queries
    Replies: 3
    Last Post: 07-31-2013, 10:07 AM
  3. Run a Function witha Sub statement
    By lugnutmonkey in forum Programming
    Replies: 5
    Last Post: 03-05-2013, 04:15 PM
  4. DCOUNT function with IN statement
    By dwilson in forum Access
    Replies: 2
    Last Post: 08-12-2011, 09:54 AM
  5. using the and function in a dlookup statement
    By englisap in forum Programming
    Replies: 10
    Last Post: 01-10-2011, 09:53 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