Results 1 to 10 of 10

Thread: Client YTD

  1. #1
    Join Date
    Apr 2006
    Posts
    7

    Unanswered: Client YTD

    I need to make a report that will list my client's monthly revenue and
    the year to date. I want it to basically look like this

    Jan Feb March etc. YTD
    Client1 $XXXX.XX % $XX.XX % $XXXX.XX % $XX.XX % $XX.XX %


    I have all of the data entered in as billing periods which there are
    two per month, and it is usually the 1st and 15th of every month. I
    am not sure how to get it to pull the data for each month seperately.
    Do I need separate queries for each one?

    I tried having a "MainQuery" which I already have for other reports. This pulls the Client's data. Then I did a query for January with only the "Client Name", "Date" (which is grouped as "January 2007") and the "Sum of Billed Amount" (which is everything that has been billed so far in January 2007. I then created the same query for February 2007.

    That all works great, however, when I try to make a report, with January and February it says there isnt a link. I do have a relationship set up linking the Main Query with the Monthly ones, by the Client.

    Lastly I need to do the YTD and the percentage per client of total income.

    Thanks for any help!!!!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is your table structure - include the relationships between tables please.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Oh my ... I wrote this type of report in about an hour ... Smart way to do this is to make a table with slots(columns) for each group of data you want for each month and your YTD ... Then run queries in vba to fill those buckets. Make your report do the summary stuff ...

    My biggest problem was that I was able to fit only 1 quarter's worth of info on a 11X17 sheet ... So, I had to daisy chain 4 reports together to print out the complete report.
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    I would suggest going from your Client to the Sales file and structuring your query along the lines Group on Client and Name; Sum YTD and Months

    YTD = [Billed Amount]
    Jan: iif(Month([Date])=1,[Billed Amount],0)
    Feb: iif(Month([Date])=2,[Billed Amount],0) - and so on

    The qualification of the Date may need to be changed to suit. You may also need to qualify the date for each Year. If there is nothing billed in the month zero is always returned. So might say: "aah you need a CrossTab Query" however the beauty of doing this if there are no sales values 0 is a lot easier to deal with than a blank. Now you should have a fully extrapolated (reuseable) query.

  5. #5
    Join Date
    Apr 2006
    Posts
    7
    I'm not familiar enough with VBA to be able to run queries through it. If you can give me a clue on the frame of the code I could see if I can work with that.

    Below are my tables and queries with the links.

    TblTimeEntryBilling:
    Invoice #
    Invoice Date
    Employee
    Job # - Linked to TblJob#
    EndBillingDate
    Description
    BilledAmount

    TblJob#:
    Job #
    Client
    JobDescription

    MainQuery:
    Client
    Job # -Linked to TblJob#
    Job Description
    Invoice # -Linked to TblTimeEntryBilling
    Reimbursed Expenses
    Total Invoiced Amount
    Date

    Monthly Queries (still trying to create):
    Invoice # -Linked to TblTimeEntryBilling
    Client
    Date By Month
    Sum of Billed Amount

    Thanks!

  6. #6
    Join Date
    Sep 2006
    Posts
    265
    Don't use a table, you're on the right track with your query with totals. I may have the wrong fields but this is to give you the gist of what to do:

    1) Qualify Reporting Year - Yearly: Year([Invoice Date])
    2) In the Query (Totals) Where Yearly = 2007
    3) Month is simply Month([Invoice Date])
    4) Same as before Jan: if(Month([Invoice Date])=1,[BilledAmount],0) etc.

    Simon

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Isn't this the prime purpos of a crosstab query ?

    I woud suggest something like

    TRANSFORM Sum(TblTimeEntryBilling.BilledAmount) AS [Monthly Billing]
    SELECT TblJob.Client,
    FROM TblJob INNER JOIN TblTimeEntryBilling ON TblJob.Job# = TblTimeEntryBilling.Job#
    WHERE Year(TblTimeEntryBilling.[Invoice Date])= [Enter Year Here]
    GROUP BY TblJob.Client
    PIVOT Format(TblTimeEntryBilling.[Invoice Date] ,”mmm”) In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

    This is of course untried, but is it a reasonable solution ?

    MTB

  8. #8
    Join Date
    Sep 2006
    Posts
    265
    The crosstabs data collection is limited only to what it can find. Columns with no information simply aren't generated. It is better yeild the whole dataset i.e. the columns, then design your report once and simply wait the for the values. Here's an example, you now want quarterly results:
    2QTY = (Apr+May+Jun) but it is only May - there is no field for Jun.

    I have tried crosstabs on many ocassions and found that replacing them with plain logic is not only easier but so much faster.

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If you use the IN clause at the end ie

    In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug "," Sep","Oct","Nov","Dec");

    Then all the columns lists are returned, even if no data exists.

    At least it does on my holidays DB, it return all th coulmns, even when no holidays have been booked after Feb.

    Also, if you omit a month from then no coulmn is returned for that month, even if data exist for that month.


    MTB

  10. #10
    Join Date
    Sep 2006
    Posts
    265
    Crosstabs are too slow and cumbersome. You them are your leisure.

Posting Permissions

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