Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. bjfriedman

    Crosstab Query/Running Totals/DSUM

    So just for an update this is what I have currently and for most purposes it works: SELECT APNDate.APN, APNDate.DateRange, DSum("Amount","Payments","[EffectiveDate]<= #" & [APNDate]![DateRange] & "# AND APN=""" & [APNDate].[APN] & """") AS RunningTotal FROM Payments RIGHT JOIN APNDate ON...
  2. bjfriedman

    Crosstab Query/Running Totals/DSUM

    Wooo HOoooo... Ok... I am soooo close now Now here is where I am at: I have dhookom's select query above and I have integrated that into a crosstab query. However information is only populating on dates where there were transactions, not on every date. So for example 6/1/07 for APN1470021030...
  3. bjfriedman

    Crosstab Query/Running Totals/DSUM

    SELECT Payments.APN, DateRange.DateRange, DSum("Amount","Payments","[EffectiveDate]<= #" & DateRange!DateRange & "#") AS RunningTotal FROM Payments RIGHT JOIN DateRange ON Payments.EffectiveDate = DateRange.DateRange GROUP BY Payments.APN, DateRange.DateRange, Payments.Amount...
  4. bjfriedman

    Crosstab Query/Running Totals/DSUM

    I have modified my DSUM Statement to include <= as that is what I had intended but it was an over sight. However I still have issues with the crosstab query not running without the error: Syntax Error in date in query expression '[Date]=##' In addition I am not sure what you mean by running a...
  5. bjfriedman

    Crosstab Query/Running Totals/DSUM

    Current SQL: TRANSFORM DSum("Amount","Payments","[EffectiveDate]= #" & [DateRange]![DateRange] & "#") AS [Running Total] SELECT Payments.APN FROM Payments RIGHT JOIN DateRange ON Payments.EffectiveDate = DateRange.DateRange GROUP BY Payments.APN, Payments.Amount, Payments.EffectiveDate ORDER BY...
  6. bjfriedman

    Crosstab Query/Running Totals/DSUM

    Dhookom, Thank you very much... I feel like I'm getting on the right track however now there is an error of: Syntax Error in date in query expression '[Date]=##' Could this be alleviated if i change Date to EffectiveDate ?
  7. bjfriedman

    Crosstab Query/Running Totals/DSUM

    Hi Lespaul, The change is on the Group by line. So basically I had changed what was initially wrong. Now I need to figure out what is causing the Data Type Mismatch in critera error. After that I'll probably have some issues with DSUM... any other hints on how to get a crosstab query to...
  8. bjfriedman

    Crosstab Query/Running Totals/DSUM

    After Include the Amount Field, and the Date Field in the query I no longer got the aggregate function error, but instead I get "Data Type Mismatch in Critera" SQL: TRANSFORM DSum(Payments!Amount,"Payments",[Date Range]![Date Range]=Payments!Date) AS [Running Total] SELECT Payments.APN FROM...
  9. bjfriedman

    Crosstab Query/Running Totals/DSUM

    I am trying to create a crosstab query where the Value Field is a sum of all of the transactions in the Payments Table prior to header date for that column. So I am looking for a running total of sorts, to use in an average daily balance calculation The Header Dates are driven by another table...

Part and Inventory Search

Back
Top