Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    DSum help (2003sp2)

    I have a table linked to our accounting DB, CURRENT_PRT_TIME. I am trying to sum the field [Amount] using the [Period_End_Date] field. Here is what I have so far:

    RunAmt: DSum("Amount", "CURRENT_PRT_TIME", "[Period_End_Date] <= [Date]")

    The relevant fields in the table are:
    Period_End_Date - (This is our week end date)
    Job - (This is number associated with a job)
    Extra - (This is how we break down the different divisions within a job. ie:01=Overhead,03=Excavation,05=Paving,07=Utilitie s,09=Subcontracts)
    Amount - (This is the labor Dollars)
    Units - (This is the Hours worked)
    Date - (This is the date the Units & Amount actually occur)

    The DSum above returns the job total for every Period_End_Date, how can I make this only total for that Period_End_Date?

    thanks,
    Jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum help (2003sp2)

    I'm afraid I don't understand. Can you try to explain clearly and exactly what you want to accomplish?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum help (2003sp2)

    I want to have a query that will give me "Running Totals" on the fields 'Amount' and 'Units' by Period_End_Date by Extra by Job.
    The attached file shows what I am wanting to have in my database. (the borders are only for display purposes).
    In this example it just so happens that the Period_EndD_Date totals are ALL 100.
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum help (2003sp2)

    What is [Date] exactly?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum help (2003sp2)

    [Date] is the actual date that the work was done on. I tried to use the [Period_End_Date] <= [Period_End_Date] and had no success, so I tried to use <= [Date] field and again with no luck. The [Period_End_Date] is the last paid day for that period, in my case it is Sunday.

    thanks,
    jackal

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum help (2003sp2)

    Still not very clear to me, but try this:

    RunAmt: DSum("Amount", "CURRENT_PRT_TIME", "[Period_End_Date] <= #" & Format([Date],"mm/dd/yyyy") & "# AND Job=" & [Job] & " AND Extra=" & [Extra])

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum help (2003sp2)

    I have found some of my problem to be I am trying to do comparisons on text fields. If the field [Job] is a text field, is it possible to convert this field using something like CInt([Job])?
    somthing like this:
    RunTot: DSum("Amount","tblTestTime","CInt([Job] <= " & CInt([Job]) & ")

    p.s. The table name has changed because i copied a sample of the orginal data to speed up run time. I fugured that I would try to get the DSum working on just the Job crieteria and then move forward from there.

    thanks,
    jackal

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum help (2003sp2)

    Why not change Job to a number field?

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum help (2003sp2)

    The table the query is based on is a Linked tabe, that comes from our accounting DB, so i do not have any control over this.

    thanks,
    jackal

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum help (2003sp2)

    OK, then try CInt, or if the values can be larger than 32,767, use CLng instead.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum help (2003sp2)

    The first option I tried was:

    RunTot: DSum("Amount","tblTestTime","" & CInt([Job]) & " <= " & CInt([Job]))

    This returns this:
    <table border=1><td>Job</td><td>SumOfAmount</td><td>RunTot</td><td>5104</td><td>143487</td><td>584110</td><td>5105</td><td>18370</td><td>584110</td><td>5106</td><td>168376</td><td>584110</td><td>5107</td><td>118586</td><td>584110</td><td>5108</td><td>118586</td><td>584110</td><td>5109</td><td>36035</td><td>584110</td><td>5110</td><td>2717</td><td>584110</td></table>
    Can you tell me what I have that is wrong & it is only pulling the total?

    thanks,
    jackal

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum help (2003sp2)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum help (2003sp2)

    Sorry for the delay in getting back. Here is a stripped down copy of my DB, with made up numbers and dollars. The query I have included is the one i use currently. I would like to expand on this query or make another one from this one that will do what i am looking to do. This query includes: Job, Extra, SumOfAmount, SumOfUnits, Period_End_Date. What I am wanting to do is have a column that contain a running total by Job, by Extra, by Period_End_Date for Amount and again for Units. I will need the total to begin again for each Job, and again for each Extra. I thought DSum() would work, but I have had no success. Any advice on how to accomplish this would be GREAT!!

    thanks,
    jackal
    Attached Files Attached Files

  14. #14
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum help (2003sp2)

    I am using the information from the DB to populate the following Excel spreadsheet.

    Extremely wide screenshot that caused horizontal scrolling moved to zip file by HansV
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DSum help (2003sp2)

    See the query qryRunningSums in the attached database.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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