Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    33

    Unanswered: Query that creates a row for each day of the month

    we collect data on a monthly basis but now a query is needed to break down the monthly amount into a daily figure. I have everything working except the ability to take the user's input from a date field and have the query create a row for each day of the month from the date entered. i.e.
    01-01-13
    01-02-13
    01-03-13...
    up to
    01-31-13

    can anyone provide a solution to this problem? it would be greatly appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you tried
    'a query is needed to break down the monthly amount into a daily figure' sounds liek you want group by sub clause

    'the query create a row for each day of the month ' doesnt' sounf right. do you actuallymean CREATE a row, or do you mean return a row?

    if you need to have a day by day breakdown (and report days when there are no sales then creatre a tabel with 1..31 and do a join between that table and the sales table on Day(SalesDate) = number in 1.31 table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2010
    Posts
    33
    Quote Originally Posted by healdem View Post
    so what have you tried
    'a query is needed to break down the monthly amount into a daily figure' sounds liek you want group by sub clause

    'the query create a row for each day of the month ' doesnt' sounf right. do you actuallymean CREATE a row, or do you mean return a row?

    if you need to have a day by day breakdown (and report days when there are no sales then creatre a tabel with 1..31 and do a join between that table and the sales table on Day(SalesDate) = number in 1.31 table
    thanks for the reply! I do mean create rows - thanks for clarifying. I thought about creating a table with days and join them in the query but what if the month doesn't have 31 days? I need the query to have a row for each day of the month, for each client. I know it sounds weird but that's what I need to figure out.

    also I need a column that lists each day of the month.

  4. #4
    Join Date
    Apr 2010
    Posts
    33
    update: I created a table with 1 to 31 and have it joined in the query. I also have a column that counts the number of days in a month:
    days: Day(DateSerial(Year([forms]![clientinfo].[parisDate]),Month([forms]![clientinfo].[parisDate])+1,0))
    the criteria for the "days" column is:
    <=days: Day(DateSerial(Year([forms]![clientinfo].[parisDate]),Month([forms]![clientinfo].[parisDate])+1,0))
    and it works great!

    now the only thing I have to do is have a date column where the days go from 1 to the end of month. any ideas?!!

    btw paris is the name of a database not the city

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that is down to the JOIN AND sort order

    can we see the actual SQL (in the query browser switch to SQL view (its the icon under the menu bar on the far left [usually]))
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2010
    Posts
    33
    I think I got it. I took the column "days" in the table "days" and inserted it into a Cdate function:

    Service Date2: Format(CDate([year] & "/" & [month] & "/" & [days].[days]),"yyyymmdd")
    (the form has the user enter the month and year)

    now each row has the month and year and the day is brought in from the "days" table.

    thanks for helping me flush it out!!!

  7. #7
    Join Date
    Apr 2010
    Posts
    33
    ok one oddity I have is wanting to put a condition for the number of days where there is a field, if left blank, will use the number of days in the month but if there is something entered in that field for that client then the the days of the month only go as high as that number. So far I've got
    IIf(IsNull([client Information].[days of service per month])<=Day(DateSerial(Year([forms]![clientinfo].[parisDate]),Month([forms]![clientinfo].[parisDate])+1,0)),<=[client information].[days of service per month])
    but it's not working

Posting Permissions

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