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?
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.
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.
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 ?
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?
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:
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:
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.
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.
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?
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?
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
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:
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.
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
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.
