Microsoft Access - I'm having an issue using DSum() in a query and I cannot figure out the solution.
Asked By mark collins on 20-Jun-12 04:50 PM
I'm not a programmer by any means, I use the design view in Access to get what I need done so I'm getting lost trying to figure out how to write this DSum function...
The setup so far... linking two queries, Launch_NPI020 & Receiving020. Taking all of the data from the Launch query and adding receipt quantity. The Sold Qty from the Launch query is what I need to get as a running sum by date... The criteria I need to sum by are - Launch_NPI020.Carrier,
Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.MonDate
Mondate is the date field... monday date of each week.
Here is the code so far... everything works but the DSum...
SELECT Launch_NPI020.Year, Launch_NPI020.Week, Launch_NPI020.WeekLookup, Launch_NPI020.MonDate, Launch_NPI020.Carrier, Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.SoldQty, IIf([Receiving020]![SumOfDelvyQty] Is Null,0,[Receiving020]![SumOfDelvyQty]) AS RecQty,
DSum("SoldQty","Launch_NPI020","Carrier = " & [Launch_NPI020]![Carrier]) AS TotalSold
FROM Launch_NPI020 LEFT JOIN Receiving020 ON (Launch_NPI020.Color = Receiving020.Color) AND (Launch_NPI020.Model = Receiving020.Model) AND (Launch_NPI020.Carrier = Receiving020.Carrier) AND (Launch_NPI020.WeekLookup = Receiving020.WeekLookup);
......
I keep getting a syntax error. Do I need to force-define some of these field properties? If so, how do I do this? I saw somewhere to use a "Dim" function, but that just confused me.
Please help and thank you in advance
Desired output:
Year Week WeekLookup MonDate Carrier Model Color SoldQty RecQty TotalSold
2012 18 201218 4/30/12 V x75 B 10 7 10
2012 18 201218 4/30/12 A Y20 R 20 10 20
2012 18 201218 4/30/12 V x75 Z 30 8 30
2012 19 201219 5/07/12 V x75 B 30 7 40
2012 19 201219 5/07/12 A Y20 R 10 7 30
2012 19 201219 5/07/12 V x75 Z 70 7 100
wally eye replied to mark collins on 21-Jun-12 10:50 AM
Could you use a GroupBy query?
SELECT Launch_NPI020.Year, Launch_NPI020.Week, Launch_NPI020.WeekLookup, Launch_NPI020.MonDate, Launch_NPI020.Carrier, Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.SoldQty, IIf([Receiving020]![SumOfDelvyQty] Is Null,0,[Receiving020]![SumOfDelvyQty]) AS RecQty, Sum(Launch_NPI020.SoldQty) AS TotalSold
FROM Launch_NPI020 LEFT JOIN Receiving020 ON (Launch_NPI020.Color = Receiving020.Color) AND (Launch_NPI020.Model = Receiving020.Model) AND (Launch_NPI020.Carrier = Receiving020.Carrier) AND (Launch_NPI020.WeekLookup = Receiving020.WeekLookup)
GROUP BY Launch_NPI020.Year, Launch_NPI020.Week, Launch_NPI020.WeekLookup, Launch_NPI020.MonDate, Launch_NPI020.Carrier, Launch_NPI020.Model, Launch_NPI020.Color, Launch_NPI020.SoldQty, IIf([Receiving020]![SumOfDelvyQty] Is Null,0,[Receiving020]![SumOfDelvyQty]) AS RecQty;
Then, on your report you can use the running sum to get your Total Sold column.
mark collins replied to wally eye on 21-Jun-12 11:24 AM
Unfortunately I do not think the group by will do anything to help. It is already grouped in previous queries. For each week, there is only one unique Carrier/Model/Color. Grouping will give me the exact same information that I started with.
wally eye replied to mark collins on 21-Jun-12 09:08 PM
Short of using some VBA to build an intermediate table, your best option would be to sort on all identifying fields and create a report with running totals that breaks between groups to restart the running total.
Please help! I finally got the dsum to give me the correct answer. Now when I open the form I get "The Here is what I have in the control source in my unbound text box: = nz(DSum("TotalPriestCompensation", "tblActualExpense", "FiscalYear = 'Fiscal 12 July 11 to June 12'AND ParishNumber = 304")) I plan in a string and integer variables to isolate the problem further. Something like this: = nz(DSum("[TotalPriestCompensation]", "[tblActualExpense]", "[FiscalYear] = '" & ddlFiscalYear.value & "' AND [ParishNumber] = " & ddlParishNumber.value), 0) The problem could stem from but the best thing to do is work your backwards. Sincerely, Maurice Maglalang keywords: Microsoft Access DSUM, DSUM, FiscalYear description: DSUM finally working but. . . . . . Please help!I finally got the dsum to give
Record ID Part ID Line Total Running Total 1 ID), String Part ID's and a value (Line Total). How the Heck do I "DSum" in a Query to yield the "Running Total" values shown? I've killed a day do a SELECT SUM(Line Total) to get the current running total, SUM is an Access function that sums the values of a set of rows. Take this answer and insert it into your next row. http: / / www.techonthenet.com / access / functions / numeric / sum.php Thanks - I've edited out all but what I need for not just a grand sum, grouped under each part number. Would this not be a DSum for each line? Yes that would also suffice. However, you might be able to embed the SUM statement into the insert query: INSERT INTO Table (RunningTotal) SELECT SUM(LineTotal) FROM . . . DSum("[Field]", "[Table]", "[ClientID] = " & [ClientID]) This assumes ClientID is numeric. If it is text, then try: DSum("[Field]", "[Table]", "[ClientID] = """ & [ClientID] & """") You could probably also use a subquery (Select Sum([Field]) From
I'm having a problem getting an invoice to display the overall total. The problem is, I can get everything to work except the dsum that sums the line items by the two separate categories and the particular customer ID. I can dsum on the two category totals and get the total for all line items in the on that particuar invoice. I'm stumped and frustrated. Thanks!! If you are using an Access report, you should be able to Group By the categories and add the subtotal to that category by a simple SUM rather than using DSUM keywords: DSUM, SUM, Microsoft Access, database, report, invoice description: DSUM in and invoice report to total particular categories by CustomerI
I a record. Of the examples that I have seen that come close they use the Dsum function. DSum( expr, domain [ , criteria ] ) What I can't figure out from Dsum is. . In the examples expr is usually the colum of a whole record. . .I don t bother I fixed the issue I was making it overly complicated. keywords: Lounge Microsoft Access Running Total description: MS Access Running Total on Form without storing result I would claim I'm a noob but
sure if this question falls here but I didn't see a spot for Microsoft Access Questions. There has to be some type of code to put somewhere to help me refrencing the text box on the main form that references the subfrm total box: b = DSum("[Total]", / / field name for the total box on the subfrm "[Quote subtbl_Special_Detail_Lines]", / / table that holds t know what to do and its driving me nuts! Please help. Thanks! http: / / www.access-programmers.co.uk / forums / showthread.php?t = 141907 keywords: Access 2003, VB, Quote, Sum, text boxes, text box description: Access 2003 form and subform timing issue I am not sure if this question falls here but I didn't see a spot for Microsoft Access Questions.There has to be
I would like to calculate a student's' gpa by semester and cumlatively in ms access. For example, i want to know at any given time a student had a 2 a report, just create a running sum. In a query you can do it with DSum() or a self join. But for either of those methods to work, you must have be an ascending numeric value. I also assumed a numeric StudentID for the example. The DSum() example would be: Select Student, Semester, Points, Credits, GPA, DSum("GPA", "Yourtableorquery", "StudentID = " & StudentID & " AND SemesterID < = " & SemesterID) From Yourtableorquery; I was able to accomplish this The expression is the same at all levels. keywords: Select Student Semester Subjectdate, FromDate, Microsoft Access, Student Semester, Aggregate description: Calculating a Subtotal of an Aggregate I would like to calculate a student's' gpa by semester and cumlatively in ms access. For example, i want to know at
I am using the sample database Northwind sample database query, you have two options; both of them bad. The first option is to use DSum() which is actually the slowest option but necessary if you need an updateable query. Select t1.OrderDate, t1.Freight, DSum("Freight", "yourTable", "OrderDate < = " & t1.OrderDate) as CumulativeFreight From YourTable; The DSum() is the slowest option because for each row in your recordset, a separate query is run. So, if you have 1000 rows, then 1000 queries will be run for the DSum(). The second option is to join the table to itself using a non equi-join t1 Inner Join YourTable as t2 on t1.OrderDate > = t2.OrderDate; keywords: SQL query, Microsoft Access, database, report, database Northwind, cumulative sum, freight description: Summing based on dates I am using
I am trying to calculate a value using Dsum in the control source of a text box on a form. = DSum([DirNotional], "tbl_options_bb", "[Security Identifier] = Forms![Options Viewer].[Security ID]") When I run this it sums the rest of the form to be unedittable. Also, if you do stick with the DSum, you might consider pulling the form reference outside of the quotes, as in: = DSum([DirNotional], "tbl_options_bb", "[Security Identifier] = " & Forms![Options Viewer].[Security ID]) Finally, check the table, make sure the data you are expecting (only one positive and one negative) fulfill the DSUM criteria. I know this sounds patronizing, but at times, something obvious doesn't show up eyes sees it and says, "Hey, why is that there. . .?" I seriously doubt that the DSum() isn't working. How are you validating the results? I would create a totals query
Hi, I am trying to create a running sum table below. However I cannot adapt the following equation to replex my query: RunTot: Format(DSum([Projects Submitted by Quarter], [Total Of Public Comment Period Start Date], "[unknown]< = " & [unknown] & ""), "0"). I