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.
So I'm trying to create a running total, to be able to graph a cumulative total. After looking around, I think I've figured out that I need to use a DSUM formula.I basically want to get a cumulative sum of profit in the 4th column.
I have a TextBox in my form, that currently has this DSum in it: =DSum("QtyAdd","Orders","ProdID")-DSum("QtySub","Output","ProdID")
(ProdID is another TextBox in the form, QtyAdd is from the Orders table, and QtySub is from the Output table)
I'm also wanting to query it with more WHERE statements but finding I can only do this once with a DSum.
I'm wanting to effectively do this: =DSum("QtyAdd","Orders","ProdID AND StockItem = Yes AND Arrival = Yes")-DSum("QtySub","Output","ProdID AND Dispatch = Yes")
So, WHERE the field StockItem and Arrival = Yes in the Orders table and WHERE Dispatch = Yes in the Output table.
Does anyone know how I can achieve this?
I was thinking an SQL query but don't know how to display a Query into a TextBox.
I understand that it is not good practice to store calculated values in a table, but I am having a difficult time doing otherwise. I have a form with the field "volume". I have a table that stores monthly volumes for each product type. In my AfterUpdate of my "effectivemonth" field I have a DSum query go to the Volumes table and sum the volumes for the particular product based on the effective month. Each month all of the volumes change and I have to go into every record via my form one at a time and trigger the AfterUpdate event in order to get the field updated with the new volumes. These volumes 1) have to be visible on my form at all times and 2) are used as the basis for most of my queries, pulling from the "volume" field in my table.
I am looking for a way to update the entire database volumes with one command button. Is this possible? I've thrown in my current AfterUpdate code to give you an idea of what I am doing now.
If Not IsNull(Me.fcstloc) Then
Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12") Me.prodvolume = DLookup("[volume]", "tblProjectDetails", "[projectid] = " & Forms!Projects!projectid & "") If Me!otherloc = "Supplies forecast" Then Me.prodvolume = DSum("[volume]", "qryMonthlySupplies", "[product] = '" & Forms!Projects!currentpn & "'" & " And [year]= '" & Forms!Projects!cutinyear & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")
hello i have a query summing sales data by month. what i need to do is be able to list in this query the sales summed for same period last year. I have some other criteria but this is the just of it. trying to use a dsum function in the query itself and using product id and date range derived from fields that calc starting date last year and ending date last year for time period.
example , sum this years jan and feb 2007 sales and also have the dsum function sum jan and feb sales of 2006 in a field right next to the 07 values.
I can not get the dsum function to work??? any ideas????
i enen tried creating a temp table with the 07 sales data summed and values for each period in seperate fields ie date1 = 01/01/06 and date2 = 1/31/2006 and the same for feb. The dsum did not work that way either. If i hard code the dates into the dsum it works by i would like it to be more dynamic based on the date1 and date2 values.
I'm attempting to do a DSUM in a query against a linked table that has a field name of "VOID". The field name shows up as a yes/no when looking at the structure and when looking at the data, it is either 0 or -1. I've tried just about everything I can think of but this is the structure I have:
Tons: DSum("Net","dbo_tblSALESTKT","JOB='" & Left([JobNumber],6) & "' AND Ticket_Date=#" & [forms]![frmForemanInfo]![CurrentDate] & "# AND [dbo_tblSALESTKT]![VOID]=0")
If I remove the VOID part, it will sum correctly but when adding in the VOID=0 or false or No or anything, the query doesn't show me a sum anymore. what I'm doing wrong.
The situation is I have two queries, one uses the other as a source, and the other uses DSUM with the source query as it's domain.
For example: I have one query called RetailCDState, this prompts the user for the name of a state (i.e. Texas), and then prompts for the name of the CD (i.e. Joshua Tree). The query returns the number of CD's sold for each retail store entered for that state.
I have another query called, TotalRetailState. This query uses RetailCDState as it's source. I pull some fields down into the QBE so that the CDname, state, artist name, etc will be shown. This works.
HERE IS THE PROBLEM. I have created 3 expression fields: TotalCDSOLD, TotalCDSHIP, and TotalCDRETURN. In these expression fields I have used DSUM, I want to use the query RetailCDState as the domain, which the help files said I can. However, it's giving me an expression error saying that it can't find the 'state' or 'CD Name', which is what should prompt the user when the whole thing is executed.
I get 3 error messages, one for each expression field, and then it prompts the user for the correct input, but then doesn't display the information. Here is an example of my DSUM line TotalCDSHIP: DSUM("[NumofCDSold]","RetailCDState")
I shouldn't need any criteria, because the results of the RetailCDState is what I want to total.
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
The Query is called KatiesPeriodTakings The field I want totalled is called Price The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
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!!
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.
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:
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.
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.
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.
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..
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.
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):
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?
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?
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?
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:
I have a query with 40 fields and 16,000 records. Field 1 is Location, of which I have 197 unique values. What I would like to do is have a running count for each unique location and when the location changes start the count over again.
I am using Order By to get the proper sort. In some other instances I may want the count to be dependent on the combination of Location and Function codes. Can someone provide some SQL tip(s) that would allow me to accomplish this.
Behind a command button, i have 3 queries being run. 2 of these queries are append queries, copying the specified record into another table (criteria set to ID of the main form) and the other query is to delete the record from the current table once it has been copied. This is then followed by another delete (not query)
When each of these are run, boxes pop up asking if you're sure you want to do it. I was wondering if it would be possibly to bring up my own box to ask if the user is sure they want to continue, and when they click yes, all the questions that the query asks about wanting to do it, are automatically chosen as yes.
I.E there is only one option box, yes and no, and no others appear.
Hello, Say for example I have a database containing the date of birth of people in the format of YYYYMMDD (a string/text field) and another field about the age of the person (a numerical field) and another field is, say, the date they had their birthday last year. I would like to check whether the year in the date of birth field and age adds together is the year in the date they had the last birthday field (or off by no more than 2 years), and give me the list of the people who are not. Is it possible to do that by writing a query or I have to use a macro and write an SQL statement for it? In both case, how should that be done?
Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all.
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal FROM Atest1 GROUP BY Atest1.StartDate, Atest1.LeftDate;
I'm doing a database for a client and I'm stuck on a graph (or rather having an overall total on the graph). I've done everything in the query but I do need a running sum for this. I've explored my books, looked on the help, checked out various past posts and I seem to be missing something.
I did find a helpful post which solved someone's problem and I have posted the code below. because everytime I try to come out of it (and I've retyped it a number of times) it keeps coming back with "Syntax error in string in query expression blah blah blah".
What I want ultimately is a running sum of the Net Cost for the product code so that it gives me an overall total on the graph. It's now been over 8 hours that I've struggled with this and I'd really appreciate a fresh pair of eyes to tell me what I'm not seeing.
