• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

database question

database question.
i want to calculate a trend values in a field
i have field which shows total count of numbers. from that i want to show the trend in a separate field. by present date.
how can i do that
i want the query for both access and mssql
can some body help  me in this.
  • 3
1 Solution
Please provide some data
romeiovasuAuthor Commented:
for example
i have count of 100 numbers and i want to forcast for this month
what i am doing in or access manually is
trend = (100/day of the month)*no of days in a month
trend = (100/6)*30 = 500
Rey Obrero (Capricorn1)Commented:
in access sql

select  (100/Day(Date()) * DaysInMonth(Date())) as trend
from tableName

you need this function (place in a module)

Function DaysInMonth(MyDate)

   ' This function takes a date as an argument and returns
   ' the total number of days in the month.

   Dim NextMonth, EndOfMonth

   NextMonth = DateAdd("m", 1, MyDate)
   EndOfMonth = NextMonth - DatePart("d", NextMonth)
   DaysInMonth = DatePart("d", EndOfMonth)

End Function
romeiovasuAuthor Commented:
can you tell me how to use in a sql query
i got it for access i used in a simple way
DateDiff("d",Date()-(Day(Date()- 1)), DateSerial(Year(Date()),(Month(Date())+1),1))

now i am using sql 2005 i want to get in sql query
romeiovasuAuthor Commented:
ok got it thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now