Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    15

    Unanswered: Need help constructing this query in MS Access

    Table A- Company ID, Balance, Invoice Date

    Table B- Company ID, Revenue, Month(different format than invoice date), Year

    Table C- Month(same format as table B), # of days in that month

    What i need:
    -Grouped by company ID,
    -Expression: Balance/(Revenue/Number of days in the selected months)

    My issue- - if i link months in table B and Table C, if a company shows up in only twice in a selected 3 month range, then it will only return 60 days in the formula. Where I need it to return all 3 months of days even if it isnt invoiced in one or two of those months. Also, if it is invoiced 3 or 4 times in one month, that month will be counted multiple times when counting the number of days

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    Without fully understanding your system, I would store dates as a date, not month, year and use calculations to extract month and year. If you are entering invoices by date wouldn't you then be posting Revenue based on the invoice and on a specific date? I would use only a single table:

    Table A- Company ID, Balance, Invoice Date, Revenue, Payment Date

    and then use a query to summarize by month.

  3. #3
    Join Date
    Dec 2012
    Posts
    15
    Quote Originally Posted by billmeye View Post
    Without fully understanding your system, I would store dates as a date, not month, year and use calculations to extract month and year. If you are entering invoices by date wouldn't you then be posting Revenue based on the invoice and on a specific date? I would use only a single table:

    Table A- Company ID, Balance, Invoice Date, Revenue, Payment Date

    and then use a query to summarize by month.
    these are reports I receive from people outside my office, so I cant really manipulate them like that

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    OK, in your table:

    Table B- Company ID, Revenue, Month(different format than invoice date), Year

    Are the Month and Year integers or dates?

    if i link months in table B and Table C, if a company shows up in only twice in a selected 3 month range, then it will only return 60 days in the formula
    If Month is stored as an integer than create your query so the Join is performed with Table C as the From side and Table B as a Left Join (show all from Table C and only those from Table B that match) and make sure you are using the Month field from Table C in your query, not from Table B. This should ensure you include all months in your results regardless of if they show up in Table B.

    Also, if it is invoiced 3 or 4 times in one month, that month will be counted multiple times when counting the number of days
    Number of days should again be calculated not on Table B but on Table C and Table B values should be summed, not grouped by.

Posting Permissions

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