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.
I am using DSum to total some records located in a subform. I have code to save the value returned by DSum to a table on a lost focus event. It works great except for one thing. The value saved is always one record behind. The save code is:
Dim dbs As dao.Database Dim rst As dao.Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset) rst.FindFirst "ID = " & Forms!frmInvoice!txtID With rst .Edit !Total = Forms!frmInvoice!txtTotal .Update End With Exit Sub
txtTotal is the textbox containing the DSum value. It displays the proper value. If I insert a blank row the routine will finally pass the right value. I tried running the code from a button but it still copies the old total.
I have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:
I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.
I've tested both statements separately and they work ok, but as soon as I add the "AND" operator, the whole field is summed and the criteria thrown out of the window apparently.
[ID] Is the same in both tables and relates to a main record [Cage Number] and [IBC_Number] are related in both tables, but are and are strings
I am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.
I want to get dsum of pamount specific for each client with date criteria for example if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above.
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
I am trying to create a user-friendly database to enter invoice records (deductions and additions) for securities. I would like to be able to run queries with running totals from month to month inclusive (end total of one month = starting total of next month). I have already figured out how to create a running total; however, I have numerous "companies." Each record could be from any of 30 companies. When I add up the running total from say January 1 to February 28th, I get the additions and deductions of all the companies, not just the one I want. Inputting the companyID (example A110) in the query box only adjusts what is displayed, not the running totals that go by the total deductions from the records before that record (I assigned each record a "database ID" because dsum does not recognize dates in the criteria box, if I want to do a running sum.
I also was wondering if it would be possible to build forms with macros to create these queries in a user-friendly manner (so the company that I am interning for can create queries when they need them without me there).
I'm trying to generate a query that can be used for a pareto chart (Bar Chart shows the count of a defect and a line chart as a second axis counts the cumulative percentage)
I've grouped my data, and sorted the Count of each time the record appears but I cant get my head around working the cumulative percentage. My datasheet currently looks like this:
Reason Count Per Expr1 A 35 47.9 Random Numbers B 11 15.1 " C 10 137 " D 9 12.3 " E 7 9.6 " F 1 1.4 "
The formula works fine up to, and including, December. Then, for the last three months of the financial year (Jan, Feb and Mar), the running total of the 'Num' field stops and the entry for January is just January's 'Num' with the running total starting again for February and March.
I have a query, i need to get dsum of total task given to an employee.
I have grouped task_description and select count and it is now showing each employee as 1 which is correct.. i ma looking to get dsum based on this task count but with a criteria that i want to see these tasks sum month a have a month field which i get from assigned date the month field is showing like this december-2013.
Based on this i want to calculate dsum as first to see task count which i mentioned earlier, then employee id which is in query as ID and then month.. this sum will calculate monthly tasks given to each employee.
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")
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.
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.
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
I want to add to values based on if yes or no is selected in an option group.
My thinking is to use DSUM. When 'yes' is selected I want to grab a value from one field and add it to a value in another field giving a running total. I am currently using the following code but it throws back an error. Is DSUM the way to go?
Im trying to use the Dsum() function to add together the "Quantities" of specific products bought in transactions i.e. the total quantity for a product called "red t-shirt" and the total quantity for another product called "blue t-shirt". the following was the code i tried to use but it just adds together all quantities of all products:
Ok, I have a commercial database over which I have no control. It is ticket sales and I want to view them over time...
This is my table of transactions:
master_tix_header tix_header_year tix_header_performance_code tix_header_no_seats - The number of seats sold tix_header_first_transaction_date - The date of the transaction
The unique ID of the performance is made up of tix_header_year and tix_header_performance_code:
E.g. In tix_header_year of 2004 there is a tix_header_performance_code of JSVH01 and in 2005 there is also JSVH01 - but there is never the same tix_header_performance_code more than once in the same tix_header_year...
Now I can get the number of tickets sold on each day using Sum() for a specific performance using:
SELECT SUM(tix_header_no_seats) AS TotalSeats, tix_header_first_transaction_date FROM master_tix_header WHERE tix_header_year = "2005" AND tix_header_performance_code - "JSVH01" GROUP BY tix_header_first_transaction_date;
I tried http://support.microsoft.com/kb/138911/ but it returned ambiguous results (seemed to reset the running total at the begining of each year...) or would say the query was too complex to run - I;ve probably missed something simple as I a newbie to the world of access/sql
I am trying to add up the total amnt under a certain location # under a certain representative #. What im having the query do it pull information from several different tables including the rep # and that rep #'s loc #. I want to find the sum of the sales under the location # for that rrep #. So it will look it up based on the loc # after it finds the rep #'s loc #. What would be the best way to do that.
I have a table that contains Product, Manufacturer, Date and Cost and want to have a field that sums up totals based on criteria, for example, total cost for each manufacturer over a set period.
I think I need to use a dsum, but although I've tried to structure this query, I'm getting nowhere fast!
Any help is appreciated!!
P.s. I have no sql expereince, so please can any response not include a brilliant, but greek-to-me sql! :o
What do I do if wanting to sum my data in a table if it is text in the field that groups them together. In the examples, the grouping is by productID, but my grouping is by a text field.
DSum("[Amount Aus]","Costs","[Costs.Area]=" & [Costs.Area] And "[Costs.Project Code]=" & [Costs.Project Code]")
The Costs.Project Code is the text field. The Costs.Area part works fine, but not Costs.Project Code
