I have a table similar to the following:
ID Date Biopsy result
111 x ddd
111 y hhh
111 z fff
222 g ggg
222 h llll
333 d jjj
333 l eee
333 s aaa
I would like to calculate, for each ID, the date interval between biopsy dates
Is there a way of doing this?
Thanks.
Hi everyone!
I need some help. Here is my table:
NAME START TIME STOP TIME
J SMITH 12:00:00 AM 6:00:00 AM
S SMITH 3:00:00 AM 7:00:00 AM
Z SMITH 5:00:00 AM 1:00:00 PM
I want to calculate how many people work per interval of half an hour. From this example, the results should be:
12:00:00 AM 1
12:30:00 AM 1
1:00:00 AM 1
1:30:00 AM 1
2:00:00 AM 1
2:30:00 AM 1
3:00:00 AM 2
3:30:00 AM 2
...................
5:00:00 AM 3
5:30:00 AM 3
6:00:00 AM 2
I'm looking for a way to do this. Ultimately, I'm trying to automate scheduled hours in a way that I can produce an IDP (Intra-Day Performance Report). I work in a call center, but unfortunately, we do not have any Workforce Software as of yet. If anyone has any suggestions, I would greatly appreciate it.
Regards.
Hi, I am just learning Access and are having problems dealing with time intervals. My database form is seperated into two tables and in each column of the tables there are different time periods. The time periods are created by having two columns and one column is the start time while the second column is the end time.The problem is that in order to tie the two database together i have to group the database use the time periods as a key. But since the two tables time periods are different, i have to first seperate time intervals to specific months. My plan first is that using queries, i will insert new rows and in those columns i will have both the start month column to end month column both be the same month, but i could never figure out how to duplicate my information and change only the month. Now i am at a loss and i really need help for the project. I am sorry for the poorly worded explanation but i can't think of a easier way to say it. Please advise me on what to do and thank you for reading.
Best Regards,
Kane
Hi there, wondering if anyone can help me.
i have a database that is going to be shared across a network server. What i would like(if possible).
USER 1 has opened up the Microsoft Access database and has spent 15 Minutes entering information. USER 2 then goes to open the same database, but gets a message saying something along the lines of "Database already in use, please try again later.
I Hope ive expressed what im trying to do.
Any thoughts would be greatly appricated.
I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:
0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet
This is the query I'm coming up with so far
JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600
I want to put the SquareFootage into the intervals I mentioned above, and get the total revenue for each interval.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I am creating an online post, similar to a guestbook. When a user submits an entry, I am storing the date and time in a column titled "Timestamp", which is formatted 8/4/2005 9:16:58 ("General Date"). I am running the table through a query which is then posted on the web.
What I would like to do is this: I would like to use Timestamp to display the date that the entry was submitted, but not the time (basically, "Short Date"). Can I exclude the time using a query without changing the actual data stored in each record of the table? I understand that the time stored is completely different than the time displayed, even in the table. How can I do this?
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code:
Private Sub Update_DOD()
Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset
Dim DOD As String
rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code:
CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
Using access 2010; i have a form that includes a date field. Is there a way to force the user to only choose a month end date? When the user clicks the date from the popup, they may use 9/1/2013 when the mgr. want them to use only 8/31/2013. I am thinking validation field to put a msg but want to be able to force it not the option.
View 2 Replies View RelatedI have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.
So:
- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY.
- If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY
- If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY
I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY
I have attached a sample of a database.
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I create a database through ms access and there have a birth date box and admission date. Another box for Age.I want to see the age in month or year figure in to the age box when I go next field. Which will be calculate from admission date to birth date.
View 1 Replies View RelatedHow can I add 7 days in todays date to store target date in the table?
Code:
ssql = "Insert into tblUpdate([Update_ID],[Date],Username,Status,Target_Date) values('" & j & "',#" & Format(Date, "mm/dd/yyyy") & "#,'" & k & "','Open')"
CurrentDb.Execute ssql, dbFailOnError
I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.
The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.
I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!
Now I could do that in Excel, but I don't know how to get Access to do it.
I need to calculate the final date based on the begin date and the number of days.
The name of the fields are: sdatainicio; diasatribuidos; sdatafim.
I'm reworking a db to make it web compatible. Right now I'm working on my Price and Sales tables.There are about 900 Sales records, 450 Price records (for about 45 Items).
I have re-done my Price table with an Autonumber Key field. (It had a multi-key which I understand web db does not support.) Each autonumber key represents a Date with new Price for a Company/Item. The Price change Dates are random.
I have put a Foreign Field in my Sales table for the Price key field.
My dilemma is matching the Sales with the Prices.
When the Price Date and Sales Date do not match (at least half of them don't match), I need to look back in the Price table to the max Date BEFORE the Sales Date in the Sales table for that Company/Item in order to select the correct Price key.
I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key)
Employee ID (Foreign key to link to the employee table)
Start_Date
End_Date
Status (Either valid or expired)
Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
We use access to enter our service tickets in at work.What we have are three date fields.
Call Date
Start Date
End Date
We are 24/7 operation.Currently all 3 just autopopulate with the current date.What i would like to do is ADD a CHECKBOX next to each Date Field.And make it work like this.
1. let them autopopulate as they are currently
2. if you end the call AFTER MIDNIGHT (the next day). CHECKING the box would automatically populate yesterdays date in each of the fields that has the check box CHECKED
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)
I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:
ID
Employee Name
Date 1
Date 2
Date 3
Date 4
Date 5
Date 6
Date 7
....etc
So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.
It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.
Thanks very much, any help appreciated! Elspeth :confused:
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
I have a DB with Start Date (dd.mm.yyyy), End Date (dd.mm.yyyy) and Quarters. We have 4 Quarters (Jan-March, April-June, July-Sep, Oct-Dec) and extra columns of month (xxx) and year (yyyy) too.
The problem is the front end users have access permissions to modify the dates. if they change the start date and end date then they are manually gonna change other fields like quarter, month and year too (Which they don't want). The users don't need to manually update the column values for Quarter, Month and Year
For Example,
If the user modifies Start Date to: 22.05.2014 and End Date to: 24.06.2014 (then the quarter column should be fixed to 2nd quarter and month should be June and year should be 2014).
2nd Example: We need to consider the End Date for classifying the quarters, Month and Year
If the Start Date is: 22.05.2014 and End Date is: 24.12.2014 (then the quarter column should be fixed to 4th quarter and month should be December and year should be 2014).
The solution is when ever the front end user modifies the dates then automatically the quarter, month and year columns need to be changed.
Sample piece of Access data sheet with just 3 examples. Column names Start date, End date, Quarter, Month, Year.
I currently have a query set up which responds and filters by the date.
I use the current code in the criteria on the query for todays date less 7 days
Code:
DateAdd("d",-7,Date())
This works perfectly and I had no problem with it. However I need to adjust the criteria so it is less than 7days ago OR OLDER....
How would I go about doing that?
I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show.
Code:
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.
[code]....