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:
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?
I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".
My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.
Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.
I have a subform on which I log the times spent on any particular project.
I have another subform on which I want to select a month and use this to select all the instances on which work was done on any given project for a particular month. I would prefer to use a pop up calender to select the month (i.e. by slecting the last day of the month in question)
Has anyone got any idea how I might do this - I am completely stumped.
I have a subForm set to continuous forms. I need a field called txtStatusDetails required (cannot be null) if the field txtDutyStatus = "Not Available"
Here's what I have so far:
Private Sub Form_Current() ' This procedure runs after the record changes. On Error GoTo Err_Handler ResetAvailability Exit_Proc: On Error Resume Next Exit Sub
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 ?
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.
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 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.
hi - i have tried search for the solution to my query - but to no avail, so here goes:
I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount]) From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table? Thank you (sorry if it is easy - but i dont have a clue!)
I have the following info in a table - I am trying to make a file to import into a label printing program. I have qty 5 of item X13 and so need 5 labels for it, 3 of X24's and so on. The output is basically a print file that will print the right quantity of labels for each of the names equipment.
Name; number X13; 5 X24; 3 X77; 1
I need an output file as follows, in excel or query result:
X13 X13 X13 X13 X13 X24 X24 X24 X77
How do i achieve this in a query or do I need code and if so what would it look like..
Here i have a situation i have a table where like below
Name | orderno | mail no | contact No | --------------------------------------- a |123 |5555 | 553453 | b |321 |8569 | 52353 | a |123 |2344 | 553453 | c |143 |567 | 553453 | d |173 |6787865| 553453 |
But i need a separate table where the entry of all the entries where a is coming only once.
In the attached DB I have a form that has a combo box which enables you to select an employee and all the employee info is then pulled based on the selection. I want to make changes to the info in the form and have the changes update the correct employee on the table. Currently the from will not let me make changes to the info and the table seems to add new lines rather than updating the corresponding line.
I am trying to do something that I would think is very simple but I'm having a hard time with it. I have a form that consists of questions that the user is needed to answer. It displays the question, a combobox where the User can select 'Yes' or 'No', and a textbox where the user can input their comments. What I would like to do it the following: if the user selects 'Yes' as an answer, I want the corresponding comments box to be required.
I'm making a customer survey database. Also be aware I'm not proficient with access.
All the customer information is being imported from an excel spreadsheet. That will include ServiceRecordID, ServiceDate, TechID, CustName, ServLocation, PhoneNum, and DeptID.
Another table will include records for answers to 5 questions that will be asked by a manager on the phone. That table is called TblQuestions. This table will also have ServiceRecordID to go with the answers.
What I'm trying to do is create a Form that at the top has a box for ServiceRecordID. I'd like to be able to type in the Number for that ID, and then all the information shows up on the form for that specific ID. And below all that, fields to add the answers to TblQuestions for that specific ServiceRecordID.
The reason I'm doing this is because the manager will get a report with the customer information that needs a follow up call. So I want them to be able to call up that information on the screen with the ServiceRecordID number, and also be able to add in the information that they get back from the customer with the questions that will be asked.
I don't know why I'm having so much trouble with this. But I'm not experienced with access or VBA both.
I'm new to access. Basically I've put this formula in a field within my query(Query A):
PERIOD: [YEAR] & "-" & [Month Number]
And I want to use this newly created field "Period" to join another table (Table A) without having to create another query.
PERIOD(Query A) -> PERIOD(Table A) = Month Name (Table A)
I need the month Name from Table A but because Period(Query A) is a formula I created, I don't know any way of joining it to Table A without creating another query. Is there another way...
I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.
I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.
Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.
I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.
