I have a form with two date fields: "Initiation_date", and "Correction_date". There is a third field called "Duration". I would like to store the values of all three fields in the corresponding table. "Duration" (Long Integer) would be the number of days from "Initiation_Date" to "Correction_Date" if Correction date is there, or to Date() if Correction date is Null.
I can use an unbound text box to get "Duration_cal" using the following as a control source:
But I cannot get that value (No.of Days) in to the "Duration" field. The reason I want to have "Duration" stored as a number is that it is being used in a query for another calculation (total days used for selected records).
I am working on updating another person access database, how to add a calculated field. I need to add a field to an existing table to subtract the date listed in one field from todays' date. It would need to return the answers in days
ie
10/10/13 - 10/1/13 = Return answer of 9 days
I have been told about DateAdd but I cant find these date function in the option of expression builder.
- Assets - Shifts - Machine Offline Date - Machine Offline Time - Machine Online Date - Machine Online Time
Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.What I have already done is allocate shifts per asset e.g. when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.
If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM
Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs
Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.
I would like to make this an automatic calculation, Is this possible?
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example: table1 01/01/2014 15/01/2014 17/01/2014 30/01/2014
Explanation: Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates) Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2 Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100 Matt 15/5/11 15/9/11 7/7/11 £200 Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
Trying to calculate a few fields. 1st field is [delays] and 2nd field is [Planned] The Plan scheduled for 60 and the Delays are an entered value. In the on update command button on the form I want code that will take the value above 60 and add then to the value in the [Delays] So if delays is 10 and the planned is 70 then planned will change to 60 and delays will = 20
I have 3 tables in Access: tblCurrency (Fields: IDCurrency, CDate, CRate) tblOrder (Fields: IDOrder, OName, ODate) tblCalculation (Fields: IDCal, Rate1, Rate2, Rate3, COrder) IDOrder is related to COrder with "One to Many" connection.
I have Form frmOrder, based on tblOrder. For Example Form has 1 record: IDCal OName ODate 1 XXXX 2005.10.24
For this record related table has 2 records: IDCal Rate1 Rate2 Rate3 COrder 1 10 3.5 15 1 2 6 4.2 12 1
tblCurrency for Exapmle have one record too: IDCurrency CDate CRate 1 2005.10.24 0.2
In frmOrder I have textBox txtRate. Here I need to be calculated:
=Sum (Rate1*(CRate from tblCurrency Where tblCurrency.Cdate = tblOrder.Odate)+Rate2*Rate3 for Each record related to tblOrder). By Exapmle: (10*0.2+3.5*15)+(6*0.2+4.2*12)=106.1
Please help with expression. Thank You in advance.
I am making a database for products and suppliers. Many suppliers can supply each product.
The product details are in a table called products, the suppliers details are stored in a table called suppliers and the suppliers offer for each product is stored in a table called prices.
the structure is as follows:
Table: Products Product Name Trade Price
Table: Suppliers Supplier Name
Table: Prices Product Name Supplier Name Supplier Offer
I have form that shows the product details and a subtable linked to the table:prices.
The problem
I need to add a field in the subtable called "Percent off trade" which is the trade price - supplier price / trade price * 100
i created a query showing the same records as the prices table plus the field "trade off price" and set the Field to:
Percent Off Trade: ([Forms]![Products]![Trade Price]-[Prices]![Supplier Price])/[Products]![Trade Price]*100
But this doesnt give me the correct percent :confused:
I hope i have explained this query and would really appreciate anyone who takes the time to suggest anything that could help
How can I multiplicate two numbers from two columns in a subform, show their result at the end of the row and then calculate sum of those results on the main form. This is something what I know how to do but the problem is following:
The total sum on the main form should be multiplicated wit 0,5 % and then reduced for a value of a specific row sum. It should be something like this: (Sum of rows sum * 0,5 %) - Sum of the specific row = Total sum on the main form. And all of this should be on a report too.
What would I use for a formuls to find all product that are in stock. like I have a price for all products that i stock, but some i don't have in stock. How would I query for the ones that I have in stock? In my table I have it as UnitsOnHand, I have it as how many, like 1 or 3 or 4 etc. And also a field for Paper or Metal. How would I query just for paper and not metal? I am also going to need a query for the lowest price and the highest price. Please understand I am New and I have allready search through all the post and can't seem to find anything close to what I am trying to do. Any and all help would be great
i'm working in a database and i face some problems in some calculation
this is my table which contain these fields: NOC number Company Name Warning Letter
Note: each company has many NOC
Here what i need from the query
1- create a field which contain a total of warning for each month 2- create a field which contain a total of warning for each NOC 3- create a field which contain the total of warning for the company
This is probably an easy calcualtion I am overthinking... I am trying to make a query where I am calculating the "Total Value" of each customer's order of each item for the current week. The item "my company" is selling is fish, sizes small, medium and large all at different prices. I'm not exactly sure what they mean by "Total value", but I assume I take the cost times the quantity...but then what is the difference between Total Value and total ordered (in dollar amount)? Any suggestions?
I wonder if someone can point me in the right direction with a calculation problem. I have never done calculations in access before but want to make a balance sheet for a club account that I am designing. Very much in its trial stages, I have a form with three currency text boxes in it, set up as continuous. MoneyIn and MoneyOut are in the main form whilst Total is in the footer. All I want is to be able to put in a value in MoneyIn or MoneyOut and for the Total to update. Problem! I dont have a clue where to start! :rolleyes:
Here is a part of my database nicely zipped for you guys to help me with.
whats happening is when i click the totals button whatever the highest cost option that is selected with a "Y" should be displayed in the totals text box.
example:
Installation of heating system - "Y" Replacement Kitchen and Bathroom - "Y" Total should be "£90"
The code behind the button works fine with every choice but not the installation of the heating system. When this is selected "Y" the code seems to be ignoring it.
I am trying to type a code based on 1 & 2 criterias. However, I am having a problem on the first criteria because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one? Thank you very much!
Criteria 1: If the day_of_wk is 1, calculate time-reg_time/60, If the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24
Criteria 2: If the day_of_wk is 2, calculate time/60, If the day_of_wk is 2 and time is less than 0, calculate time/60+24
My code: pre: IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60))
