URGENT HELP PLEASE! Auto-Updating Field Using A Calculation (Complex)
Aug 8, 2006
Hi, im relatively new to access but my problem is fairly complex.
I'm creating a database for a school project, and it is focused on improving the way a flying club runs.
I have been able to solve most problems myself, but this one truly has stumped me.
The basics:
There are two tables - Instructor Details
- Flight Completion
And one form - Flight Completion
On the instructor details table i have a field called 'Hours Flown'
The purpose of this field is to show the total hours flown by an instructor.
On the flight completion table i have a field called 'Flight Duration'
This field is for the duration of the flight the instructor just flew.
I want it so that when the instructor presses a button on the flight completion form, the duration entered into the 'Flight Duration' field is added to the 'Hours Flown' field and then the 'Hours Flown' field is updated with the new total.
I hope that is clear to you, but if not please let me know and i will try and clarify it for you.
First off, I want to thank ansentry for providing a solution to a problem I had (new record entry), and I apologize if reverse engineering the form you wrote for my own usage is considered bad form.
Ok, here's my issue. I'm designing a database that keeps track of materials mined in an MMO (quit laughing ) in this fashion: pick out a selected, user-inputted date rangepull all records that fall within the date rangeadd up the sum total for ALL users that submitted any minerals in the date range per mineral typeadd up the total for each mineral type for each person during the range (seperate from the total for everybody)run a calculation on each type for each person in the date range against the total of everyone, and output a percentage of what they mined vs. total Now, I'm pretty sure that I have to do this via a query or two. Or three or four, however many it takes. What I have little to no clue on is how do actually program the query(ies) itself to perform these actions.
Was wondering if someone could give me a few pointers/tips to point me in the right direction. Also, if you want to take a look at the DB, (and if you don't mind) post your e-mail address and I'll mail you a copy of what I have so far as it might clarify any questions.
I have a table, at the table I'v got these fields:
ID | num1 | num2 | sum
I want that the user put numbers at 'num1' and 'num2' fields and then the 'sum' field will calculate automatically the operator (sum=num1+num2).
I've tried to put any combination at 'Default value' of the sum field (all the fields are numbers , also tried to change the sum field to text...nothing works).
I'm getting a message that 'num1' field is not recognize at the table
(I tried =[num1]+[num2], without the '=', num1.table+num2.table , ...nothing works)
I also tried to do it with SQL command but it dosn't work.
There is any way to do it, is it possible? Or other way to do it at least at Form or at Report ?
Hi I'm creating a small stock control system. I want to be able to update stock levels. Simply by entering new amounts of stock when they arrive and adjusting for stock used that day. I've created a form from a query which takes current_stock level and then I enter a value (say 50) and it is calculated (added together). Then i want to save that new value back into the current_stock field? Can anyone help??
I was wondering if anyone knows any way to have the date/time field in my database automaticaly update itself to the current date when I change part of the data in that same line.
For instance, if I change the entry for last name, is there anyway to have the date field automaticaly change to the current date without me entering it manualy? Thanks for your time.
I'm creating database that will display about 6000 different names. My question is how can i insert a last name into the last name field, and have the first name of that specific person automaticlly filled in in the field next to it titled, First Name. The answer is probably simple, but i'm as stupid as they get right now. I would like to thank everyone in advance for their help!
Greetings everyone. I am not an Access Guru so please take me request with a grain of salt.
I am the marketing admin and web developer for the company I work for (co-op telcom). I have built a simple Access database that interfaces with a legacy service order system we utilize company wide. The database simply tracks marketing jobs we work and publishes them as service orders to the system. The problem I have is that I have to format the job number very specifically to fit the legacy systems expected input requirements and I haven not been able to figure out how to do this.
Below is an example of what a series of job numbers must look like. Note the date info preceding each example
Jobs entered on April 20, 2007
070420-01 070420-02 070420-03
Jobs entered on April 21, 2007
070421-01 070421-02 070421-03
The structure of the string is YYMMDD-'Sequential number restarting each day'
The date portion is no problem and nor is the dash. Where my problem lies is in the sequential number restarting each day.
Any thoughts on how to do this. I am guessing this should be some kind of VB or if then statement, but I have no idea where to begin.
To show that I appreciate the help anyone can give with regard to this mater, I am offering a free 1 year hosting package (200M disk space/2 Gigs of bandwidth/mysql/php/unlimited email addy's w/hsphere control panel) to the first person who can help me with this problem.
OK Guys, I am building a sales database for a client he has supplied the text files which I have loaded into tables. Problem on the sales file there are multiple part numbers in the part field these are separated by '-' the same format is on the Qty and value fields. The logic behind this is: For the 1st part in the part field the qty and the value are the 1st in there respective columns, for the 2nd part in the part field the Qty an value are the 2nd in there respective columns and so on eg
I have a form where I type in the time a person starts a job. The format is Medium Time. I also have a box where I type in the End time for that job. Also formatted in Medium Time. I have another box that is for if a break happens during that job to return the value 10. My formula for that box is: =IIf([Start Time]<"9:00 AM" And [End Time]>"9:10 AM",10,0). The problem that I am having is that it only works when the time is in the 9:00 AM to 9:59 AM time frame. I need it to work where if a person starts at 6:00 AM and gets done at 2:30 PM to return the value of 10.
I have created a 'junction' table between 2 many to many tables:
Jobs table (details all jobs)Jobparts table ('junction' table details Job ID, Part ID, quantity and unit price)Stock table (details all stock).The fields Job ID, Part ID and Unit price are each lookup fields from the corresponding fields in the jobs and Stock tables with quantity being a manual entry. This works fine.
I discovered you can automatically populate the unit price field using autolookup based on the entry of the Part ID field on the form. However having amended some pre-existing jobpart details randomly on the form when I checked back to the table they had not updated. Similarly when creating new entries while the form 'unit price automatically updated on entry of the 'Part ID' these details did not feed through into the table.
I assume it has to do with how the table was originally set up the field properties will need to be changed.
Ok so here is my problem: I have a form that is realted to four differnt tables with the names: CUSTOMER, ORDER, PRODUCT AND ORDER LINE. With-in the table Order-Line there is a entity called, line_item_cost whcih is the total amount of the order with respect to Quanity_Ordered which comes from the ORDER LINE table and Price which comes from the PRODUCT table and Discount (%) which comes from the CUSTOMER table. When the users goes to change the Quantity_Ordered I need the form to automaticly update the line_item_cost which would calculate [Price]*[Quantity_Ordred]-[Price]*[Discount] . I tried to build a code in VBA to do this, Here is the Code: Dim a, b, c, d a = Me.[Quantity_Ordered] b = Me.[Price] c = Me.[Discount] d = a * b - b* c Me.[Line_Item_Cost] = d
VBA Complies the program but it does not show up in my form. I think I might be updating it at the wrong point. Any Suggestions.????
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):
I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".
What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.
Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.
I have a large problem that i need to get figured out ASAP! I have a form text box (named actual cost) that needs to pull a specific record from a table
Now in this table it has multiple rows according to each job. There is a text box (Project number) that has the project that this (actual cost) box should pull from the specific row in the table.
if anyone can help me it would be the biggest help. -Thanks Jon
Most of the project is done...I am stumped on calculating a field for the report...any help is greatly appreciated..thanks so much.
Use a Microsoft Access database to create the tables and column headings show in Figures 1-3 and discussed in the Background section. Create the tables in the design view. Populate the tables with the data as shown. Make sure you create a composite primary key field for the ORDERS table. The key should consist of the fields Invoice Number and Product Number to allow customers to order more than one item.
Customer Customer NumberCustomer SchoolCustomer StreetCustomer CityCustomer StateCustomer ZipContact Name 105Central High123 Main StreetDenverCO80208Mary 107Central Middle35 South ParkDenverCO80278Sue 423JKF Elementary69 5th AvenueCheyenneWY90200Pat 516Toddler Time8000 ParkwayFt. CollinsCO80487Jan 799Georgetown High819 Rt. 909BoulderCO80303Jack
The owner wants to find out how much of each product is selling for the date ll/15/2005. Develop a query in the design view to give that information using the headings Date, Product Name, and Quantity. Save the query as Today’s Sales.
Creating another Select Query
Tomorrow, the owner is going to be in Denver, CO, and she would like to call on the customers there personally. Make a query to display the contact name, school, and street address ONLY for those schools in Denver. Save the query as Denver Schools.
Generating a Sales Report
Finally, make a report to show the dollar amount sold to each customer for each product. To create the report, you will need to:
•Make a query first and then bring that query into the Report Wizard. •The query will contain a calculated field for Total Owed. •The query should show the fields Customer School, Customer City, Date, Product Name, Quantity, and the calculated field, Total Owed. •Name this query “Report Data” •The report will be grouped by Customer School and Customer City, with subtotals for each school’s sales and a grand total. •Make sure you delete any bogus total lines, and make all appropriate fields currency.
Deliverables
Three tables Query output: Today’s Sales Query output: Denver Schools Query output: Report data Report: Current School Sales Defined relationships (I can press the relationships button and will see that the relationships between tables are defined)
im doing a report based on a query (which has a few fields, including KTYPE, EXPIRATION_DATE, OVERAGE). im trying to determine the OVERAGE based on the following conditions:
here are the conditions:
if KTYPE="PO", then OVERAGE:=(EXPIRATION_DATE + 6 months) ie.. KTYPE=PO, OVERAGE:=(01/04/2004 + 6 months) = 07/04/2004
if KTYPE="IAA", then OVERAGE:=(EXPIRATION_DATE + 20 months)
if KTYPE="FA", then OVERAGE:=(EXPIRATION_DATE + 12 months)
and so on and so forth.
I'm very new to Access. What formulas do I use and do I put the formula in the query or the report.
I have a cross tab query that has field names 6, 7, 8, 9, 10 representing months and a relevance date field with a date.
I have a form and from that form i need to get the value of the last three months from a relevance date field.
I.e relevance may be 01/10/2005 so i would need 3 labels, (labels are already made, but there source needs to be) 10, 9, 8. But the next row may have a relevance date of 01/08/2005 in which case it would be 8,7,6.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
Having some problems with a calculated field on a form. The field came from my table but I typed in the calculation (=[cost_price]+[sale_price]) and it works fine but it wont save to my table so when I run a repot off it doesnt have any of the calculated values because they arent saved in my table??? Can anyone help?
How do I get a field to calculate this formula? I want a script to find the precise current date and from another field find a number and add it to the current date day to make current date + number = date in the future.
For example Field: Total Nights Hired = 2 Field: Date due back = 23/02/07 + 2 = 25/02/07
I am sorry if I did not explain this well enough. I am totally new to databases but need help in my corsework. I am designing a video rental system.
So here is my problem i am semi access savy but i have having trouble with one thing. At work when we make a schedule there are 5 possibilites for how you want to mark your day. I have created a field using text as my parameters since my possibilities include both letters and numbers. At the end of each week I need to create a calcuation only utilizing the number which is 1 to add up the total shifts in the week.
I have tried some IIF statements with no luck. The 5 possiblities are 1, c,r,a,l. Each of the letters stand for something. When the scheduler runs the query he needs to see the letters or the number for each day. But i can't create a properly working calculated field for adding all of the 1's together only. The letters don't need to be added. Any ideas?
Also I have to run a sum for the total of each day at the bottom so I am running into the same problem there if there is any letters in the boxes. Going to a number field is not an option thanks for the help
Below is the code I have written to calculate the arrival and departure times based on the users input on time in the air (tripETE) and on the ground (tripGT). The arrival (txtArrTimeZulu) and departure (txtDepTimeZulu) times are unbound boxes since there is no need to save the information. My problem is when the next leg's ground or air times are entered, it updates the arrival and departure times for all the records to that value; overwritting the previous legs times. What am i missing here? Do i need to open a recordset when opening the form or when entering "TripETE" and "TripGT"?....
I have a report which is a statement. I want to be able to have a field that calculates rent, and minus payments. There are a few rent types, Pro Rated rent, Rent brought forward and rent changed, then there is 1 payment type. I am trying to write the sum a few ways, but have not had success. If rent type i= 1 or 2 or 3 I want it to add, if rent total = 4 I want it to minus.
=Sum(IIf([RentType]=1 Or 2 Or 3,[RentAmount],0))-Sum(IIf([RentType]=4,[RentAmount]))
I have 2 tables, "Counselors" and "Appeals". There are several names in the Counselor table but it is not a fixed number of names (rows) usually around 7. There is also some variable number of records in the Appeals table, could be 0, could be 50, depending on the day.
I need to, in a round robin fashion, assign the counselor names one by one to the records in the Appeals table. How to loop through the 2 tables and update the name field in the Appeals table with the name in the Counselor table.
OK, I figured out how to make a calculation. My big problem now is to have a global field, which would be "beginning balance" and "ending balance". I have 5 employees that share the budget. The budget starts at $1280.00. One employee may buy somthing one day and then another day while another employee may also buy something. I want to be able to put in the amount the employee spent and have it deduct from the balance. But then when I go to another employee and subtract what they spend I want that to subtract from the balance. I want the balance to be visible at all times no matter what employee I am on. Hope this makes sense. I have an attached file that shows how I did it for one employee. Cannot figure how to be able to do it for each employee and just the balance changes. Also I built a subform to be able to show different dates. But the problem again is having it subtract from one global balance. Thank you.
