Help with a query... hard to explain

greenfalcon

Registered User.
Local time
Today, 04:57
Joined
Aug 22, 2006
Messages
34
Hi all!, i would really appriciate it if you could help me out. I have this complex query... in one table i have dates and costs associated that show when fuel was put into the system

such as:
1/1/2008 -- $200
1/20/2008 - $100
1/25/2008 - $150

I then have another table that has data when fuel was taken out of the system
such as:
1/2/2008 -- ($50)
1/3/2008 -- ($20)
1/4/2008 -- ($10)

what i need is a query that is able to show a running tab on the fuel tank and how much fuel is in it...

so for example if it could spit out a result such as
1/1/2008 -- $200
1/2/2008 -- $150
1/3/2008 -- $130
1/4/2008 -- $120

If this is convoluted i can upload a little database..

Thanks! (Here is the current query i am using but its not working right.

Code:
SELECT DatePart("yyyy",[purchase_Date]) AS AYear, DatePart("m",[Purchase_Date]) AS AMonth, DatePart("d",[Purchase_Date]) AS ADay, DSum("gallons_purchased","purchase_fuel_tbl","DatePart('d', [purchase_Date])<=" & [ADay] & " And DatePart('m',
          [purchase_Date])<=" & [AMonth] & " And  DatePart('yyyy',
          [purchase_Date])<=" & [AYear] & "") AS RunTot, fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons, [RunTot]-[fuel_use_tbl].[gallons] AS cur_Value
FROM (tank_tbl RIGHT JOIN purchase_fuel_tbl ON tank_tbl.tank_ID = purchase_fuel_tbl.tank_ID) LEFT JOIN fuel_use_tbl ON tank_tbl.tank_ID = fuel_use_tbl.tank_ID
GROUP BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]), fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons
ORDER BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]);
 
A sample database would help.
 
Here is the sample database... (Its actually a Rar file)

you can see what im trying to do...

internal_fuel_summary_3 is the query i am trying to get working...

cur_Value is the field which should be taking the running total and subtracting the used gallons and that should equal the current value...

Its a little confusing but pretty much the (RunTot) field is when fuel is added to the system and (gallons and fuel_date) is when fuel is taken out... so i would like the cur_Value to increase when fuel is put in and decrease when fuel is taken out.. you can also see that all the fuel_dates are not in the right spots...
 

Attachments

Last edited:
Windows tells me the zip file is empty. Perhaps because I don't have rar software? Can you post a standard zip file?
 
I've got company coming in a few minutes, but for a start I think you need to have a common source. See if this gets you anywhere. If not, I'll finish up later when I can:

SELECT fuel_use_tbl.[fuel_date], -fuel_use_tbl.[gallons]
FROM fuel_use_tbl
UNION ALL
SELECT purchase_date, gallons_purchased
FROM purchase_fuel_tbl
ORDER BY fuel_date
 
Thanks, ill check this out! I will get back to you after i check it out. Have a good time with you.
 
Ive got it working by making another query and referencing that one... However, since i am trying to create a running sum i seem to be having some issues. I want to be able to create a query which shows everytime a debit or credit is being made. Currently i can only get running sums for months or years and then it re-sets... do you know how to change this... here is the new code i am using, as well as using yours.

Code:
SELECT DatePart("yyyy",[fuel_date]) AS AYear, DatePart("m",[fuel_date]) AS AMonth, DatePart("d",[fuel_date]) AS ADay, internal_fuel_summary_query_4.fuel_date, internal_fuel_summary_query_4.Expr1001, DSum("Expr1001","internal_fuel_summary_query_4","DatePart('d', [fuel_date])<=" & [ADay] & " And DatePart('m',
          [fuel_date])<=" & [AMonth] & " And  DatePart('yyyy',
          [fuel_date])<=" & [AYear] & "") AS RunTot
FROM internal_fuel_summary_query_4
GROUP BY DatePart("yyyy",[fuel_date]), DatePart("m",[fuel_date]), DatePart("d",[fuel_date]), internal_fuel_summary_query_4.fuel_date, internal_fuel_summary_query_4.Expr1001
ORDER BY DatePart("yyyy",[fuel_date]), DatePart("m",[fuel_date]), DatePart("d",[fuel_date]);
 
you wont easily get a running total in a query (possibly not at all)

--------
you can get it on a report easily, or on a form with a bit of work.
ther easiest way might be to save the query into a table, and evaluate the running totals in that table
 
It's fairly simple in a query. Try this based on the one I posted earlier:

SELECT qryPaulBase.fuel_date, Sum(qryPaulBase.Gallons) AS SumOfGallons, (SELECT Sum(Gallons) FROM qryPaulBase AS Alias WHERE Alias.fuel_date <= qryPaulBase.fuel_date) AS RunTotal
FROM qryPaulBase
GROUP BY qryPaulBase.fuel_date;
 
Wow that was amazing pbaldy... i worked on this thing for 8 hours yesterday and couldnt figure it out and you do it in only a few lines of code... I am going to have to put this code in a vault... a vault wrapped in gold. I don't think many people have an easy time with this concept... i find spatterings of it all over the internet but never a good solution..

And this thing is fast! I had this concept (almost working, but it relied on vba... static variables and i was getting wierd behavior... i had it working but as soon as you would run the query and click anywhere on the query it would re-run the code and not from the start of the query which would screw up all the numbers.

not only is your way extreamly clean but it is lightning fast!.. I am going to have to study this code because i have never seen anything like it before... It could make for som really cool code!
 
here is one last question... how do i modify your query in order to create a running sum by month?

Ill try to figure it out myself and will let you know if it works...

thanks
 
See if this gets what you want:

SELECT Format([fuel_date],"yyyymm") AS MonthYear, Sum(qryPaulBase.Gallons) AS SumOfGallons, (SELECT Sum(Gallons) FROM qryPaulBase AS Alias WHERE Format([Alias.fuel_date],"yyyymm") <= Format([qryPaulBase.fuel_date],"yyyymm")) AS RunTotal
FROM qryPaulBase
GROUP BY Format([fuel_date],"yyyymm");
 
No problem; glad it helped you out.
 

Users who are viewing this thread

Back
Top Bottom