I am trying to put together a quick database for my HR team. I need to be able to pull multiple date ranges to show which employees are up for an annual, 6 month and 90 day review.
Is it even possible to put multiple date ranges in one query, or is this going to get complicated? I have no real database background, and know absolutely no SQL so I'm flyin by the seat of my pants here...
I have not found the solution to this looking elsewhere, but basically what I am trying to do is the following:
person date
1 1/1/2012
1 12/15/2012
2 6/12/2012
1 6/13/2012
2 6/14/2012
3 6/16/2012
I want a query which shows the count or records for which a person has a date within 30 days of each other. This is being queried from a large table in which records would may not be sequential by person. Thus in my example above, the third record for person 1 would be omitted as well as the record for person 3.
During a client's stay with our agency, they are served among multiple contracts and are placed within multiple programs. Because clients move frequently between contracts and between programs, we have separate tables which have start and end date fields. It is common for any client to have multiple listings in these two tables, but with different event dates.
I am struggling to create a query that will capture each client's most CURRENT contract AND program. In the future, I'll want to create a parameter query that will allow the end user to enter a range of dates and capture all of the contract and program movement during a period of time.
Any suggestions?
Hi, I'm very new to this so your patience and help is requested.
I need to store multiple ranges per record in my relational db, i.e.
A: 3500-4800, 5230 (spot) , 6400-7900
Etc, I need the data to be searchable with minimal fuss and coding.
Any help would be very very welcome.
Cheers
I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!
Between Date()-Weekday(Date()-1) And Date()-1
I received help on previous threads. Basically, I have built a query to group by course title and then select the number of students that were assigned a course during a specifc time frame. Is there a way to show multiple columns in the query. For example my query now shows, enrollment for March, I would like to column next to it to show enrollment for Feb and then another column for Jan.
This is my current sql view.
PHP Code: SELECT tblContent.Title, Count(*) AS [Students Assigned]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Robins") AND ((tblContent.Code) Like "NH*") AND ((tblProfile.Groups) Not Like "*Professional Development Center*" And (tblProfile.Groups)<>"All Users,Professional Development Center") AND ((tblProfile.Delete)<>Yes) AND ((tblContent.Type)="Learning Track"))
GROUP BY tblContent.Title, tblContent.[Date Assigned]
HAVING (((tblContent.[Date Assigned])>=#3/01/2004# And (tblContent.[Date Assigned])<=#3/31/2004#));
Thank you in advance for any help you can offer.
I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.
I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing.
Hello,
Am posting this message (also on reports forum) as i dont know whether the problem is at report or query level. Hope you can help.
Hello all, this is probably simple but im new to all this!
I am trying to produce reports that show details for only the past year, at present my reports are bringing up details of all the previous years also. Is there any way you can put conditions on reports ( or should it be in a query) that tells the report to only include dates i.e. today - 365 days. At present i have to change the dates every day both a start and end date. Is this possible?
Thanks
I am attempting to create a parameter query to show a specific range of dates of service for my customers. Example: I want to see data from my table from Jan. 1st, 2006 to Feb. 15th, 2006 for any customers serviced in that time frame. In the query I have under my Date field, I have the following in the criteria field: Between [Input Start Date] And [Input End Date]. When I go to the while in the design mode of creating the query on the main menu under Query then to parameters and get my Query Parameters what should be put into the Parameter column and the data type columns and will this allow the user to input any start date and end date and get the information from the master table?
I also need to create a parameter query that will allow viewing of only each months table data. Example: Jan. 1st, 2006 through Jan. 31st, 2006.
Thanks in advance!
I must be having a bad day, as I can't get something really simple to work. I have a query, that amoungst other things, I want to select all records between two dates. For instance, the table has a 'ScheduledDate' field, and I have set up two parameters in the query that prompt for 'From' and 'To' parameters.
In design view, I have tried the following in the 'criteria' box for the 'ScheduledDate' field;
>=[From] <=[To]
This doesn't work ~ can anyone point me in the right direction. I've searched the forum, perhaps not using the correct keywords?
All help appreciated.
Calculating dates between TWO date ranges:
I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.
Example:
test table:
Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith
Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?
(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)
Don
I need a query that will relate values to given date ranges. For Example:
Table: TaxTypeRange
Values:
TaxType; FromDate; ToDate; Contact
W2; 1/1/15; 3/31/15; Tom
1099; 4/1/15; 6/30/15; Tom
W2; 7/1/15; 12/31/15; Tom
Table: Bills
Values:
Contact; BillAmount; BillDate
Tom; 1000; 3/31/15
Tom; 1100; 4/30/15
Tom; 1200; 5/31/15
Tom; 1300; 6/30/15
Tom; 1400; 7/31/15
Requirement: For every period where the TaxType = W2 the query should multiply the Bill amount by 1.20, so the result should be something like this:
Query: BillsAndTax
Values:
Contact; BillAmount; BillDate; TotalBill
Tom; 1000; 3/31/15; 1200 (TaxType = W2 so 1000* 1.2)
Tom; 1100; 4/30/15; 1100 (TaxType = 1099 does not apply 1.20)
Tom; 1200; 5/31/15; 1200 (TaxType = 1099 does not apply 1.20)
Tom; 1300; 6/30/15; 1300 (TaxType = 1099 does not apply 1.20)
Tom; 1400; 7/31/15; 1680 (TaxType = W2 so 1400 * 1.2)
Not sure how to set this up the right way.
i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.
in the Bookings table there is a field called [ServiceDate]
in the bonus table i have a field called [Period] which is text i also have 2 dates, [StartDate] And [EndDate] i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.
i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)
i've tried everything but had no joy
Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))
I have a form which I am using child/master fields to create a filter (see attached pic)
For the date ranges I need these to be
>= Start Date and <= Actual Finish Date
How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.
I have tried search through some forums and but still not able to get desire results.
Table Customer Agreed Transport Rate
Customer > Eff Date > Route > Rate
A > 1 June 2014 > AAA > MYR 99
A > 15 Aug 2014 > AAA > MYR 88
A > 1 June 2014 > BBB > MYR 77
A > 1 Sept 2014 > BBB > MYR 66
B > 5 June 2014 > CCC > MYR 88
B > 20 Oct 2014 > DDD > MYR 100
C > 1 Oct 2014 > EEE > MYR 222
If there is order from Customer A for Route AAA loading on 2 July 2014, I can easy to get answer "MYR 99".
How if I wish to make Route as primary key? (as I understand, Primary key must be unique and no repeat).
Hi
I'm really hopeful that somebody can help us out here. I'll explain as best I can.
I've got a table, where details of particular online environments are kept. Each online environment is assigned to up to four people. By this, it may be assigned to nobody, or it may be assigned to four seperate people, or anywhere in between.
What i'm trying to create is a query where the user of the database can type in a particular persons name, and it will return all the details of any record where that person is mentioned in ANY of the four colums, which can then be used to generate a report for that particular individual.
However, i'm really unsure of how to do this, and am aware that there is probably a really simple solution to my quandry, that will leave me kicking myself up the rear like theres no tomorrow.
Any advice as to how to proceed would be hugely appreciated, genuinely.
Thankyou.
Kip;)
Hi,
I need to run a parameter query in which you enter a product number and the query (a) returns the list of components and (b) then automatically reruns the query for these components. In other words, I enter an input (a product number) the query gives me outputs (component numbers) and then reruns the query with each of these outputs as the input (i.e. I want to know what are the sub-components of these components). This process stops when all the components are raw materials -- this will be easily identifiable.
Does anyone know how to set-up this type of query?
Thanks!
Andre
how to query data in my database based on a number of different criteria.I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors.In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.
I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.
I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.
Hi,
I use a query to return values between two dates, here's the code I use:
Between [Select Start Date:] And [Select End Date:]
However because my dates are time stamped (they need to be!) the query omits anything on the end date, for example:
Between [01/09/2007] And [05/09/2007] will return values for the 1st, 2nd, 3rd, 4th but not the 5th - because (I think I'm right in thinking this but I might not be!) it only returns values upto midnight on the 4th? so 05/09/2007 13.42pm won't show up because it's after 11.59 on the 4th.
I can't ask people to enter in an extra day because quite alot of people who use our database won't remember and it'll cause alot of problems when they forget and get the wrong figures.
I've tried adding the following:
" & "11:59:59""
onto the code but Access says the expression is typed wrong or is too complicated to be evaluated.
Does anyone know how to fix this code please??
thank you for your time
I am using the QBE grid and am writing a select query to select only records with an empty Date Closed Field. The Date Closed field is a Date/Time Field. I am using Access 2003. When I use in the criteria IsNull([DateClosed]) I do not get any records selected which have an empty Date Closed field, have I a bug? and if so please could anyone point me in the right direction.
View 4 Replies View RelatedI am working on a query form, in which user can enter three parameters: date range (datefrom field, and dateto field), 1st selection of category (a drop-down menu, multi-selection enhanced), 2nd selection of category (a drop-down menu, multi-selection enhanced. By clicking command button, user can choose whether the result show in query table, or in a report. It didn't work... the error has something to do with the date criteria. If I remove the date criteria, i.e. just allowing selection of two categories, it works perfectly fine.
I've checked out many postings in this forum, but it doesn't take me far... below is the code, please please help....
Note: the cmdOK is the name of the command button for presenting results from query table.
-------------------------------------------
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strDate As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str1MainCateCondition As String
Dim str2MainCateCondition As String
Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "QryCateDateForm" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips"
cat.Views.Append "QryCateDateForm", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then
DoCmd.Close acQuery, "QryCateDateForm"
End If
' Build criteria string for Date
strDate = "(((NewsClips.IssueDate) Between #" & datefrom & "# And #" & dateTo & "#))"
' Build criteria string for 1MainCate
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2MainCate
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1MainCate condition
If Me.optAnd1MainCate.Value = True Then
str1MainCateCondition = " AND "
Else
str1MainCateCondition = " OR "
End If
' Get 2MainCate condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[IssueDate] " & strDate & _
str1MainCateCondition & "NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("QryCateDateForm").Command
cmd.CommandText = strSQL
Set cat.Views("QryCateDateForm").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
------------------------------------------------
I'm using Access 2003 connecting through ODBC. I'm trying to set up an automated query to grab today's data. normally it's pretty easy, all you'd have to do is enter date() in the criteria in an access query. Unfortunately, the date in my database (i can't change this) is in an odd format.
example: april 1st, 2013 = 1130401
Every single date always has that preceding "1" in front of it, then the year, then the month, and finally the day.
I want to always specify the current date whenever the query is run (without having to type it in)
I have a db tracking vacation times for staff. One of the fields tracks a members vacation start and end dates. I need a way to pull a report to see who is on vacation based on Date() (today).
For example:
A record for John Doe has him start vacation 08/19/2014 and end vacation on 8/28/2014. If I wanted a report that shows who is on vacation today, 08/25/2014, using the date() function), how would I do this?
I need a form to set up records
The table is something like
AutoID, Sequence , Low, High
Data would be
1, 1, 1 499
2,2,500,999
3,3,1000,9999
4,4,10000,999999
etc
Ho do I go about ensuring my sequences are correct
ie I wouldn't be able to add a record - 5,5,100,200
or if a user was to delete a record the user would be informed the sequences were out?
Thanks for any assistance.
Hi all,
I am relatively new to access and need some help.
I have table with ranges of postcodes (NL) and regions. For instance
Table_Regions_Range:
Amsterdam 1000-1119
Haarlem 2000-2099
etc
And i have a records of schools:
Table_Schools:
SchoolA 1050
SchoolB 2050
etc
I want to query the data base and lookup in wich regio a school is based.
Where do I begin?
Thanx in advanced
Jan
Hi all
I have a bunch of student test scores. The test is scored out of 100. I want to COUNT the scores (ie I want to know how many kids scored 50 etc...). This is easy enough except the crosstab query will be very wide ie 100 columns.
I cant seem to reduce the size by grouping them into ranges (ie count the number of kids that scored between 50 and 60 in a cross tab. I've tried variations with select queries but without luck.
Maybe there is a way to code it.. and then show it on a report??
Any ideas?? Thanks