Dsum Need 2 Fields In Calc
Jun 11, 2006
I have a Qry field:
TotalUsage: Val((Round(Nz([UomUsage])/DSum("Nz([UomUsage])","[tblDetail]",),4)*100))
the problem is that the DSUM needs to sum up all the like records using the same SubID. I need some extra code in the divisor part. EG
subID uomUsage TotalUsage
33 10 100
33 40 100
33 50 100
34 43 100
34 57 100
the dsum needs to use SubID in order to sum correcly EG on SubID 33 as 100 uomUsage.
SubID is a Number field, Long integer.
I am presently getting wrong info. the formula above Dsum is summing as 200 since i eroneously don't have Subid in the Qry calc.
How can i fix? Thanks
Penwood
View Replies
ADVERTISEMENT
Apr 23, 2014
How do I use fields in related tables to create a calc field? The wizard only shows me the current table's fields. Can I do create me desired field by manually creating the calculation and bypassing the wizard? Or is this another restriction of Access?
View 1 Replies
View Related
Nov 25, 2006
I have a Number field with value as
DSum function where the criteria is
Textfield which works well.
=DSum("[MCRef]","Miracle_Cloth_Main","[Cust_ID]='" & [Cust_ID] & "'")
It doesn't show the SUM when the type of
textBox is CURRENCY
Can anyone suggest IF Ineed to change the syntax ?
View 2 Replies
View Related
Nov 19, 2005
Dear all,
How do i insert or place the ms calc.exe in specific place on the form
I know how to activate with command button, but how to specifically place that application say calc.exe on the fixed specific place on the form
Thanks
View 2 Replies
View Related
Aug 11, 2006
I have a calc'd percentage field in my query. This is a multi user app and the problem is that is can (and does often) happen that the numerator information for my calc gets entered before the denominator data and therefore the calc'd field shows and as an error.
I attempted to use a simple IIF statement to input a message rater than the error code but now the field will not format to Percent. It worked, I got the message to display and it calc'd the records that had data, it just will not display in percent format. It does let me set the format to percent, it just doesn't display and does not allow the decimal places to be set.
This was the line:
CalcPercent:[Numerator]/[Denominator]
This is the new line:
NewCalcPercent:IIf([Denominator] = 0, "Message", [Numerator]/[ Denominator])
I've tried to search for an answer and I'm sure it is going to be something simple that I've overlooked.
Any thoughts?:confused:
View 3 Replies
View Related
May 17, 2007
Hi,
I've searched hours on the forum but with no luck. If there's anyone out there who can help me would be very much appreciated.
The calculation in Excel is very simple but I am unable to replicate in Access. Please don't confuse this with FIFO calculation as it is purely Weighted Average Cost method based on Buy unit prices i.e Sale unit prices are not required as it will sell at the Average Cost of the Buys upto the point of Sale.
i.e.
Buy 100 @2 = 200
Buy 100 @3 = 300
Total 200@ 2.50 =500
Sell 100 leftover will be
Total 100 @ 2.50
I am attaching an Excel S/sheet which shows the Calculation of Stock ABC and the Average Prices (which is what I am trying to achieve). I have also attached an Access Dbase with same details but without any calcs as whatever I have done I am unable to achieve my result.
Any help will be much appreciated.
Thanks
View 14 Replies
View Related
Feb 4, 2008
Ok, I know it's simple but...
subtracting field B from field A.
Field A has data in all records, field B only some. The 'only some' I get a result but the ones that have nothing in B I get nothing even though A has data.
Both are currency and default is 0.
How do I take nothing from something and get nothing you ask ?
View 6 Replies
View Related
Oct 29, 2004
This is the second edited section:
I got the update query to work properly. Is this the best way to solve my problem?
This is the original edited section:
For the problem below would it be possible to use an update query to update the table?
Criteria for the termination_date field would be "is not null"
payment_to_date Update to: field would equal DateDiff("m",[payment_start_date],Now())*[monthly_payment]
Is it possible to use other fields in the table as part of the UpdateTo: calculation? I get an error when I try this.
This is the original question:
I have a field in a table called payment_to_date that I want to always be populated with the current total of how much a customer has paid to date based on their monthly payment rate. I have a form for data entry into this table but once their account info is entered the first time their won't be any changes to the account unless they terminate the service so calculating from an event procedure on the form doesn't make sense to me (I am new at this).
I entered the following in a text box control source on the form and it works but the textbox is not bound to the table so it's not the right way to do it.
=IIf([termination_date] Is Null,(DateDiff("m",[payment_start_date],Now())*[txtpayment]),(DateDiff("m",[payment_start_date],[termination_date])*[txtpayment]))
The calculation from above is basically:
If there is no termination date then the payment to date = the number of months between todays date and the date payment was started multiplied by the monthly pay rate.
Can anyone tell me how to keep the information in the table current for the payment_to_date field?
Any help is appreciated.
Troy
View 2 Replies
View Related
Mar 7, 2006
Hello everyone. I am very new to databases, may have bitten off more than I can chew, but I was tried of working in 4 different spreadsheets and decided to pull them all into a database so that I could get what I wanted when I wanted. Here is what I need to be able to do: In a table I have 3 different industries entered 5 different times. They all cover a Monday to a Friday. Each day for each industry I record a reading from a meter. I make a calculation by subtracting Mondays number from Tuesadays, Tuesday from Wednesday, Wednesday from Thursday and Thursday from Friday. This calculation gives me how much water each industry used each day, over 24 hours. Doing this in a spreadsheet was simple. My table has the Industry name, date, previous day meter reading, present day reading. I want to have the calc show in my query. How do I set up this calculation. Any help would be greatly appreciated. Thank you. I attached a sample database that contains the table and query.:confused:
View 3 Replies
View Related
Jun 7, 2007
Hello again everyone!
My head's swimming right now so the solution to this problem may be right under my nose and I'm just blind to it.
I have a table with events, represented by an ID number, and the date/time the events occured. Each event is also attatched to a particular mission number. Looks kinda like this:
Mission EventID Time
Each Mission has different events like liftoff and patient contact, and I need to find the time between events for each mission. I'm trying to calculate the Time from inital call to mission end, from dispatch to liftoff, ect.
Do I need to put these times into my mission table as columns, Time1, Time2, Time3... or can I leave them normalized and still run calculations on them? :confused:
View 1 Replies
View Related
Apr 4, 2008
Hi
I'm trying to finish a query that calculates the time worked in the AM and PM, adds them together, then takes that away from the standard working day.
I've done everything else, but when I take the hours worked away from the standard day it always ends up with a positive number, regardless if they haven't worked enough hours.
I've looked through the forums and everyone is talking modules, but thats over my head.
Thanks for looking, apprieciate the help.
Alan
View 1 Replies
View Related
Sep 12, 2015
I want to create a calculated field that If the employment type is FT Hourly or PT Hourly it will have a pop up screen for the Hourly Wage to be entered. If those criterion are not met it nulls the Hourly Wage field.
View 1 Replies
View Related
Jun 23, 2006
If I put a calculated control on my form, I don't seem to have any way of pushing its changes to a field when those changes happen. Access can be downright infuriating at times. I've spent days just putting together a simple invoicing application.
My form is not based on a query like the northwind database, because that created such ridiculous and useless behavior with subforms that I couldn't. So now that it is based on a good old fashioned table, when I change a qty or price, the calculated "extended price" (=[qty]*[price]) field visibly changes just fine, but it never has an "after update" or "after change" event. Why would this be? How am I to save these bloody changes to an actual field in a table without using some grossly inelegant code?
View 6 Replies
View Related
Jan 13, 2008
I Have a table that I collect data for numerous ID's. One ID may have 5 dates that is was serviced. It is due to be serviced at set intervals which is calculated from the most recent service date. I'm calculating the next service date in a text box with the DateAdd func. =(DateAdd('m',[Cal Freq],[MaxCalDate])) with the MaxCalDate being a Max func in a different text box for selecting the most recent sevice date. I'm trying to update the [Next Due] field in the [Master Asset] table with the calculated next service date so I can do queries on items that are due service within a date range. If I do an Nex Due Expr1: in the queries to calc the next due service date then do a criteria on that Expr1: such as >=[Start Date] And <=[End Date] it states datatype mismatch? Is there a way to select a requested data range on a calculated type date/time? Or How can I update the [Next Due] filed in a different table?
View 8 Replies
View Related
Jul 30, 2007
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?
Case 1
txtTotal.Value = DSum("[s1]", "[s2]")
View 3 Replies
View Related
Mar 29, 2008
Hi there guys, ill keep it short and snappy,
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:
DSum("[Quantity]","Query2","[Product Name] =[Product Name]")
Does anyone have any ideas at all please?
Thanks
View 7 Replies
View Related
May 17, 2005
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;
This gives me:
TotalSeats tix_header_first_transaction_date
2 14/03/05
9 18/03/05
1 22/03/05
But what I want is:
TotalSeats tix_header_first_transaction_date
2 14/03/05
11 18/03/05
12 22/03/05
Can anyone give me any pointers?
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
TIA
Nick
PS: Access 97 if that makes any difference!
View 5 Replies
View Related
Jul 5, 2005
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.
View 1 Replies
View Related
Oct 2, 2006
Hi!
Can someone tell me why the following querie doesn't work?
txtcharges = DSum("[Charges]", "tblcharges", "[DateCharges] >= '" & startdate & "' AND [dateCharges] <= '" & enddate & "'") '
Thanks
JonyBravo
View 3 Replies
View Related
Apr 23, 2007
Hi,
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
View 5 Replies
View Related
Jul 17, 2007
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
Any help would be great
View 9 Replies
View Related
Jan 7, 2008
Hi Guys,
Look, I have a table to record supplier invoices for vehicle maintenance services.
The fields at this table are:
1. [Vehicle_License_Plate] - text - this is to input the vehicle ID, or registration
2. [Supplier_ID] - text - this is to input the supplier ID, or supplier code
3. [Invoice] - text - this is to input the supplier invoice number
4. [Invoice_Date] - date/time - this is to input the invoice date
5. [Service] - text - this is to input the type of service, from a pre-determined list
6. [Amount] - number - this is to input the invoice amount
7. [Accepted] - yes/no - this is to input whether the invoice has been accepted by our services or not
8. [Accounting] - date/time - this is to input the date when invoice has been booked in the accounting system
Furthermore, at my main vehicle table (the one where all the information respective to a vehicle is recorded), I have some fields where I need to have the total amount spent per service type (one field for each type of service).
In my intention to have an update query calculating the values for these fields, based on the supplier invoices table, I'm having a problem...
At the update query, I'm saying that I want to update i. e. field [Cost_Transportation], on my main vehicle table to the result of the following formula:
DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA'")
Problem is that, instead of having the total "Transportation" amount for the each vehicle well calculated and updated at the main vehicle database field, I'm getting the overall total transportation amount from the supplier invoices table, and this total amount is then updated on all [Cost_Transportation] fields at my main vehicle table.
Istead, what I would need was a formula that could calculate, from the "DB_Supplier_Invoices" table, the total [Amount] per service AND per vehicle, and then update the fields at the main vehicle table accordingly.
Any help will be highly appreciated!!
Should you require further clarification, please advise.
Many thanks and best regards.
View 4 Replies
View Related
Apr 24, 2006
hi
i have a access database (250mb)
when access run this line,
i get:
run time error : 6
Overflow
totaldga = DSum("nombre_dga", "tbExcel", " jour >= " + "#" & Me.txtDateDebut & "#" + " and jour <= " + "#" & Me.txtDateFin & "#" + " and heure_debut >= " + "#" & Me.txtHeureDebut & "#" + " and heure_fin <= " + "#" & Me.txtHeureFin & "#" + " and transit in (" & Left(strIN, Len(strIN) - 1) & ")")
if i do:
totaldga = DSum("nombre_dga", "tbExcel", " jour >= " + "#" & Me.txtDateDebut & "#" + " and heure_debut >= " + "#" & Me.txtHeureDebut & "#" + " and heure_fin <= " + "#" & Me.txtHeureFin & "#" + " and transit in (" & Left(strIN, Len(strIN) - 1) & ")")
that work fine........ access don't seem to like: jour >=....... and jour <= ........
any idea?
thanks
View 5 Replies
View Related
Mar 2, 2006
Hi
I am trying to use DSUM to calculate a running total for an outstanding balance for each period over a number of years. The problem I have is it is not totalling when the year changes, it starts a new running sum for the next year. For each year, there are 12 periods. Is there anyway I can get it to total for all years? My DSUM calculation is
RunningSum: Val(DSum("([CHG_AMT]-[CASH_AMT])","RUNNING","[FCYEAR_ID] = '" & [FCYEAR_ID] & "' and [PERIOD_NO] <= " & [AMonth]))
Thanks in advance for any help
Alison
View 1 Replies
View Related
Mar 16, 2006
Is there a way to add to the below code (a Field in a saved Query) that if the RemitDate is the same as the RemitDate and SOInvoiceNumber is the same the SOInvoiceNumber, that the DSum function will work. And also still work as the code reads.
PrevPaidCalc:Format(Nz(DSum("RemitAmount","tblCustomerRemitsDetail","SOInvoiceNumber=" & [SOInvoiceNumber] & " And RemitDate<#" & Format([RemitDate],"mm/dd/yyyy") & "#"),0),"$#,##0.00")
I have attached a view of what I’m trying to accomplish, I apologize for the crudeness of the Form it’s still under construction.
View 7 Replies
View Related
Dec 29, 2005
The following statement returns NULL
Dim str As String
Dim n As Variant
str = "RemitDate >= #" & Forms![Report Menu].FromDate & "# AND RemitDate <= #" & Forms![Report Menu].ToDate & "#"
n = DSum("GSTPaid", "queryJobRemittance", str)
yet however when I run the query ie
SELECT sum(GSTPaid) FROM queryjobremittance
WHERE remitdate>=[Forms]![report menu].[fromDate] And remitdate<=[forms]![report menu].[todate];
it works fine.
The remitdate is short date,the format is dd/mm/yy, and the dsum works fine when no criteria.
Any advice will be much appreciated. Thanks.
View 1 Replies
View Related
