Running 12-Month Query Calculation
Feb 21, 2008
Hi all,
I have a problem creating a query calculating a running total based on the last 12 months grouped by product.
The table "Data" (attached):
Product no. / Period / Gross Rev / Clients
810 / 01-01-2006 / 1.000 / 10
810 / 01-02-2006 / 1.000 / 10
...
810 / 01-01-2007 / 500 / 10
990 / 01-01-2006 / 1.000 / 10
990 / 01-02-2006 / 1.000 / 10
From this table I want to create a running total so that the running gross rev of product 810 MAR07 is based on APR06-MAR07 data. The same applies to the number of clients. Is this possible?
I have tried using the Dsum function but I have only managed to calculate a total running (not based on the last 12 months). Can somebody help me here?
:confused:
View Replies
ADVERTISEMENT
Dec 18, 2013
I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
Do any of you know a way I can make this work?
View 2 Replies
View Related
Jun 21, 2006
Good Morning,
I have a table where I record period of time:
Example:
ID1 from 25-apr-06 to 06-may-06 Holiday
ID1 from 06-may-06 to 02-jun-06 Work
How do I automatically calculate how many days ID1 was on Holiday and how many days was workign in may?
Any help appreciated
Enrico
View 3 Replies
View Related
Jul 12, 2005
I am building an application for a small clinic. In the interests of good database design, I don't want to be storing numbers that are derived by calculation.
A patient with insurance will have a deductable that will be entered into a field on their first (ms access) transaction form.
A calculation will put the remaining deductable into another field. No problem.
However, the NEXT visit has to look back to find previous transaction records, again perform the initial calculation and then perform another for this transaction, and again the next time and the next until the deductable reaches zero.
Additionally, since I'm not storing these values, if I pull up an old transaction form, that form has to re-perform the calculation ONLY on transactions from that date and earlier. It must not reflect visits that took place later.
So, this field must query tblTransactions, filtered by the name of the patient (a combo box called tblTransactions.Patient_Name that looks at tblContacts), the transaction date/time (stored in two date/time fields, cleverly named tblTransactions.Date and tblTransactions.Time), and the total deductable value stored in a field called tblTransactions.Deductable.
As someone who has never coded anything more complicated than DOS batch files, I'm at somewhat of a loss as to how to go about this.
Any assistance, code-wise and which Property value to put it in would be MOST appreciated.
View 1 Replies
View Related
Oct 12, 2013
I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.
The incoming revenue is a field of running sum of revenue for each product.
Desired output:
I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.
I am attaching the database, with sample data.
View 1 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
May 17, 2005
please help me
i have a date field called open & i need to generate a query to count all entries by month, so far i can get all cases each day, but i'm unable to get the query to count them just based on the month & year..
any ideas please
View 2 Replies
View Related
Mar 14, 2005
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
Many thanks in advance.
Peter
View 2 Replies
View Related
Mar 19, 2012
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
View 10 Replies
View Related
Sep 16, 2005
Hi!
Sorry I am to bother you. But I was wondering whether it is possible to create a running sum in a query (from each previous record just a simple add up)?
I know how to do in report (it is described in the help function) but for a query I cannot find anything.
Thx and rgds,
Lobhaan
View 2 Replies
View Related
Feb 16, 2006
have a table, Loans.
Fields:
ID (key)
Loan Number
Principal Balance.
I have another table, Transaction, that's based off the Loan Number field on the loans table.
Fields:
LoanNumber
Payment Type (2 choices, payment and advance)
Amount
I have a user form where users can specify the payment type, and amount, based on a certain loan number.
I'm attempting to do this:
User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.
If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.
Here's what I have so far.
The userform updates the transactions table with the information correctly.
My next step is to update the principal balance.
I'm thinking I could use some sort of update query, but I don't know where to begin.
HELP!!
If there is a better (normalized) way to accomplish this, I'm definitely open to ideas. Please just provide a basic example so I can understand. :o :cool:
View 1 Replies
View Related
Jul 29, 2006
Well Shut down my computer and call me a newbie.
I'm trying to run an append query with two parameters and a like "value*" in the where clause. When I run it in ADO it returns no values.
When I run it in DAO is works just fine.
WHen I delete the like condition it works just fine.
When I change the like condition to and abolute condition, e.g. "Cancel" it works just fine.
BUT WHEN I PUT Like "Can*" back it is returns no records.
Now I've been working with Access for more years than I will admit, but I have never seen anything like this.
I assume Like is valid SQL
SO SOMEONE PUT ME OUT OF MY MISERY. Am I barking up the wrong dog, or is "Like 'XXX*" not usable when executing queries in ADO.
SAVE ME !!!!! SAVE ME!!!!
View 10 Replies
View Related
Jan 7, 2008
Hi
I have a query that shows part numbers with a total qty ordered for a specified time period. This first part of the query is fine and is made into a table.
I am then trying to use this table to create a running sum with the list sorted in descending order, and ultimately break the list where the sum reaches 80% of the total qty across the whole list. I have tried the following formula and the table has been indexed in the descending order it would need to be in. The IndexAlias has also been created and made into a fixed table prior to running the 'Running Sum' query.
RunTot: DSum("qty_ordered","Core Stock","[Index]<=" & [IndexAlias] & "")
I have taken this formula from the microsoft link below, (Method 2), and adapted it to my query. However, although I have a decent understanding of Access I do not understand the '&' in this formula. Removing this and the double quotes on the end returns an error and leaving them in returns zeros all the way down the 'RunTot' field. If anyone can help I would appreciate it as I am stuck on this.
http://support.microsoft.com/kb/290136
many thanks
:)
View 14 Replies
View Related
May 15, 2006
hey there,
i am trying to create running sum in a query. i found the function for a report but not i a query
From MS Access Help: ("You can number the items in your report by using a calculated control and the RunningSum property. First, you create a text box and set its ControlSource property to =1. Then, you set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All")
my overall goal is to number each Employees assignments that they have. That is Employee1 may be associated to 2 assignments, thus resulting in employee1, (assignment number) 1, employee1, (assignment number) 2, and Employee2 may have 4 assignments, employee2, (assignmetn number)1, employee2, (assignmetn number)2,employee2, (assignmetn number)3, employee2, (assignmetn number)4.
any ideas.
View 6 Replies
View Related
May 16, 2005
Hello, I have the following criteria for my Query >=0.8*[Projects]![EstimatedHours]. This tells me when we've used 80% of our actual hours. How would I go about the criteria line if I wanted it to return above >= 50% hour use, but below 80%? Thanks
View 1 Replies
View Related
Sep 8, 2005
I have a query that is built up of 5 fields with only the 2 below being visible. I am wanting to be able to calculate the total price for each pole no i.e. Pole no 01-02 total price would be £1441.79, What is the best way to do this? Sample data is shown below
New Pole NoTotal Cost
01-02.....£437.08
01-02.....£476.37
01-02.....£36.21
01-02.....£70.27
01-02.....£51.56
01-02.....£370.30
01-1.....£437.08
01-1.....£476.37
01-1.....£36.21
01-10367.....£493.43
01-10367.....£70.15
01-10367.....£76.51
01-10367.....£102.67
01-10367.....£87.54
01-10367.....£36.21
01-10367.....£51.56
01-10367.....£39.54
01-11448.....£437.08
01-11448.....£463.25
01-11448.....£493.43
01-11448.....£476.37
01-11448.....£70.15
01-11448.....£76.51
View 4 Replies
View Related
Dec 6, 2005
In qryAddticket, I am trying to calculate two conditions (see Condition 1 & 2)However, I am having a problem on the first condition because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one?
Condition 1: If the day_of_wk is 1, calculate time-reg_time/60, If the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24
Condition 2: If the day_of_wk is 2, calculate time/60, If the day_of_wk is 2 and time is less than 0, calculate time/60+24
My code: pre: IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60))
Thank you very much!
View 2 Replies
View Related
Feb 26, 2007
I need to calculate the total cost of the order and enter this into the "CostOfOrder" field which you can see in the picture...
http://i13.photobucket.com/albums/a298/steve01/untitled2.jpg
I need to read all the orderlines for an order into a record set and calculate the cost for each orderline. I need to use a loop in VBA to add them all together.
Here is the query for inclusion in VBA...
Dim StrSQL As String
StrSQL = "SELECT orderline.qty*sandwich.price"
StrSQL = StrSQL + "FROM sandwich INNER JOIN orderline ON
sandwich.sno=orderline.sno"
StrSQL = StrSQL + "WHERE (((orderline.orderno)="
StrSQL = StrSQL & orderno
StrSQL = StrSQL + "));"
Do Until recordSetData.EOF
Total = Total + recordSetData.Fields(0).Value
recordSetData.MoveNext
Loop
I think the Query is correct, but where do I put this in the form (or where do I actually put it as I don't know!) to make it work?
Thanks in advance!
Steve :)
View 1 Replies
View Related
Oct 19, 2004
Im building a database for a car dealership. what do i need to do to take how many times someones name comes up in a certain field in a table and show it as a number. I need to include this calculation in a subfrom on a form which displays "employee info" which is already stored. Basically i just need to take how many times there name shows in the salesman field and show it as a number.
thanks
please respond, URGENT!
View 2 Replies
View Related
Mar 12, 2007
Does anyon ehave any experience of running totals in an access query.
I'm reporting the data through excel not access reports so need a query not a report solution..
I have a table which looks:
RegionCategoryTypeDesc Period_IDPeriod_YTDPeriodTotal
CanadaEventsWSOP Team67Budget15000
CanadaEventsWSOP Team78Budget0
CanadaEventsWSOP Team89Budget0
CanadaEventsWSOP Team910Budget0
CanadaEventsWSOP Team1011Budget0
CanadaEventsWSOP Team1112Budget0
CanadaEventsWSOP Team1213Budget0
CanadaEventsTOTALAll12Budget15000
CanadaEventsTOTALAll23Budget15000
CanadaEventsTOTALAll34Budget15000
CanadaEventsTOTALAll45Budget15000
CanadaEventsTOTALAll56Budget15000
What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.
Any helpo really appreciated.
Simon
View 1 Replies
View Related
Jun 23, 2005
Hi - Once again I'm asking for your assistance. Ideally, below is how the form (and eventually a report) would look; the first three fields (ID, Date & Vol) I currently have in a table (tblMaster):
ID Date Volume YTDTotal Q1Total CurrQTDTotal Q2Total
A1 Jun-05 10,000 77,000 40,000 37,000 37,000
A1 May-05 15,000 67,000 40,000 27,000
A1 Apr-05 12,000 52,000 40,000 12,000
A1 ------ ------ 40,000 40,000
(Sorry, I couldn't get the above data to line up right :confused: )
How would I build a query such that I could show the above in a form/subform (and eventually a report)? Help is greatly appreciated.
View 1 Replies
View Related
Aug 26, 2005
I am helping a company reorganize its employee data to reflect recent changes in the company's organization. It involves a lot of data manipulation that, for the most part, can't be done programatically.
I can save a little time using SQL update queries like this:
update personnel set Department = "Support Staff" where Jobtitle="Shipper"
update personnel set Department = "Maintenance" where Jobtitle="Mechanic"
update personnel set Department = "Regional" where Jobtitle="HR Manager"
I have prepared a text file containing 530 such queries that can be pasted into Access. But as much time as that saves me, it is still a day or two of cutting and pasting.
What would be the best way to talk Access into running all the queries, without having to paste in every one individually?
Thanks...
...df
View 2 Replies
View Related
Dec 8, 2005
hi guys!
Is there a way to run a query with macros? I don't want to open it. I just want to run it, generate a report which will then be sent via email. How do I go about it?
Thx a mill!
cheers:D
View 1 Replies
View Related
Mar 9, 2006
I am running a query that has over 14000 records. What I tell the query to is read two other queries and tell me which record does not match with the other one. My problem is, that it takes over 10 minutes to do.
I have already compressed the file. I do not have a primary key (trust me on it, I can not do that). Are there any other ways to resolve this?
View 1 Replies
View Related
Mar 13, 2006
I have a query that have three columns invoice number, tran number and amount. What I would like to do is to sum the invoice number if they are equal. example:
new column
Invoice #: 1345652 50.00 50.00
Invoice #: 1345652 65.00 115.00
I have the same invoice multiple times, but the transaction number is different. so I need to SUM all the invoices that are the same.
Please help!!!!!!!
View 5 Replies
View Related
Sep 5, 2006
I have been trying to get this all night. This was my last attempt. I know it is all wrong and messed up but after 10 hrs I go for help. There is no date field to work with.
Expr2: DSum("[Amount]","[List]","[Item]=" & [Item] & "and [FinItem]>0")+0
But it ended up like this.
Item ------Location-------Amount--Expr2----Expr3
210090------030005 -------267470--error ----859103.74417
220170------ GYM -------409000--error ----859103.74417
220170------ 060010-------83297 ---error ----859103.74417
220170------ 050010-------17762 --error ----859103.74417
3429B -------060010-------0.74417--error ----859103.74417
3429B -------060010-------3 ----error ----859103.74417
7342900-----060010--------5369 ---error ----859103.74417
7342900-----060050--------76202 --error ----859103.74417
I need it to look like this.Where it will do a running sum based on the "Item" Groups.
Item---------Location------Amount----Expr2------Expr3
210090------ 030005-------267470---267470---859103.74417
220170------ GYM --------409000---409000---859103.74417
220170------060010-------83297----492297---859103.74417
220170------050010-------17762----510059---859103.74417
3429B-------060010------- 0.74417---0.74417---859103.74417
3429B-------060010------- 3-------3.74417---859103.74417
7342900-----060010-------5369----5369-----859103.74417
7342900-----060050-------76202---81571----859103.74417
Please Help.
View 3 Replies
View Related
