Queries :: Select Last Input From Several Date Columns
Oct 2, 2013
Having built a database that tracks projects across their lives, I've now been asked it I can identify the latest status of the project in a "quick view"
The table "All Projects" has columns that list the Project Number; Project Title; Project Manager and further colunms that list dates that the various stages of the project were achieved. i.e. Start Date; Date Milestone 1 Completed; Date Milestone 2 completed etc.
The projects are such that Milestone 5 may be completed before Milestone 2 and so on, so I need to be able to identify which Milestone was the last to be completed.
I thought of trying an IIF but the progress across the Milestones isn't logical so it won't work. I found something on the Internet about "Greatest" being used on some other program (not Access), which Access didn't like at all!
If the requirement had been given before starting building the db, I would have done everything differently, but I have no real desire to go back to stage 1 and start again, so how or if it's possible to find the highest / latest date relevant to the Project Number across the row of data ?
View Replies
ADVERTISEMENT
Sep 10, 2013
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
View 1 Replies
View Related
Mar 14, 2013
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
tblStudents
studentID
studentName
studentEmail
tblResults
resultID
resultStudent
resultDate
resultTest
resultScore
tblTests
testID
testName
testPassScore
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
View 8 Replies
View Related
Jul 28, 2014
How do you return the most recent date of multiple columns.
I have a table (tbl_courses) that has a list of training courses. We want to know when a client completed the course most recently.
The problem is, for one course there has been up to 4/5 different variations of the course with different names over the years. E.g. "Drug awareness" has also been known as "Drug Aware" "Illegal Substances" and "Stoppers". I want to pull through the most recent date for all of the above.
We have a field in the Courses table that links the courses into groups (e.g. All drug aware courses come under "23"). Not sure if that works?
Is there a way to do this? The Tbl_Courses is linked to Tbl_Clients via a ClientID.
I've managed to do it in SQL using GREATEST() but that isn't an option in Access.
View 6 Replies
View Related
Jun 6, 2013
I want to create the date from multiple columns to single colums. Just for example
table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013
Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013
Now i want to make one query, which can you the date as follows when we give criteria = apple
Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import
Means two dates from different table into one query column... One way in my mind to make one table for both tables.
View 9 Replies
View Related
Mar 10, 2014
I have a large database filled with customer records. Some customers come one time. Other customers come 50 times at year. I want to find all customers that have records that are less than 30 days apart so I can ultimately see the types of products they buy. How do I write the query?
I attached a picture of a sample database. The 30 day (+/-) field doesn't exist but I would like the query results to build it.
View 11 Replies
View Related
Jul 1, 2014
I'm using a form to select a date range. Using the following, and entering start date of 6/1/14 and end date of 7/1/14 I would expect to pull the records with a date of 7/1/14; however it doesn't. I have to enter and end date of 7/2/14 to pull 7/1/14 records.
>=[Forms]![F_Transaction_Date_Range]![txtStartDate] And <=[Forms]![F_Transaction_Date_Range]![txtEndDate]
View 14 Replies
View Related
Dec 24, 2013
How would you write a Select Query to select the most recent dates?
Select OrderDates
From Orders
Where >=Date()
No good if recent date is older than todays date!
View 3 Replies
View Related
May 19, 2015
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
View 14 Replies
View Related
Jul 10, 2014
I need to find the frequency of client's appointment activity after their last appointment in a 4 month window.
My question is:
(1) Is my SQL going to give me the last time a client was in the office within the 4 month window?
(2) What will my SQL need to like to count frequency of appointments before and after window for each client?
(3) Can I do (2) in the same SQL shown above or will I need to make a new SQL statement?
Code:
SELECT tblTest.Client, Last(tblTest.AppointmentDate) AS LastOfAppointmentDate INTO tblTest
FROM tblTest
GROUP BY tblTest.Client
HAVING (((Last(tblTest.AppointmentDate))<#4/30/2014#))
ORDER BY Last(tblTest.AppointmentDate);
Details
Date Window I need to select the clients last appointment: Jan 1, 2014 - April 30, 201
Once the last appointment has been determined for each client in that window I need to count the frequency of appointments before and after that date.
View 14 Replies
View Related
May 21, 2013
get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
View 1 Replies
View Related
Apr 23, 2014
Is it possible to create a query to select all dates from a given reference date? I don't mean all dates in a table - I mean all dates generally?
(The idea being to fill the first field in the resultant dataset with the list of dates, then run subqueries off that to fill the remaining calculated fields)
I'm currently using a date field in one of my tables to populate this first field (the full SQL is in a separate thread here)
But that was just a convenient way of getting a list of dates; the dates in that table don't actually have any significance to the resulting dataset (other than they should roughly overlap with the dates I'm looking for)
The flaw in that method is that the table from which I get those dates can only ever have dates up to and including yesterday. I also need to get today's date in there (and calculate the subqueries based on that date as well).
It's also possible - although unlikely - that there could be random dates missing from that table as well - in which case I need to plug those gaps and calculate my fields for those missing dates as well.
For clarity; that first field (AsOfDate) should contain every weekday from the earliest date in that table (i.e. Min([tblBalances].[BalanceDate]) up to and including today. It doesn't matter if any of the dates inbetween are missing from tblBalances as the subqueries will just return zeroes for those dates (which is exactly what I want to see).
View 3 Replies
View Related
Jan 17, 2007
SELECT contractid, min(RTRbalance) AS minRB
FROM ACHACT
GROUP BY contractid;
will have two culumn
What if I want to select all columns of ACHACT associated with minEB in each contractid group?
THX.
Jeff
View 1 Replies
View Related
Oct 21, 2012
I'm developing a small Access 2007 database. I am looking for a dropdown list for one of the columns that would automatically reflect the contents of the previous 4 columns of the same row.
Sample table mentioned below. Column 6 (Winner) should be a dropdown reflecting the nominations1,2,3
Zone
Nomination 1
Nomination 2
Nomination 3
Nomination 4
Winner
South
CV Raman
Narayanmurthy
Mohanlal
I M Vijayan
East
Azim Premji
Bhaichung Bhutia
Jagdish Chandra Bose
Bhupen Hazarika
North
Gulzaar
Sam Pitroda
Kapil Dev
KP Singh
West
Sachin Tendulkar
Asha Bhosale
Anil Ambani
Anil Kakodkar
View 1 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
Oct 5, 2005
Hi!
I want to add two columns a and b. However, there are rows in which EITHER a OR b is NULL. This should be handled as if the cell would be zero. Is there any way besides filling in 0s in the tables?
I tried
SELECT a, b,
IIF( a = NULL, b, IIF(b = NULL, a, a+b))
AS c FROM
but this doesn't work.
Any ideas?
Cheers
Tom
View 1 Replies
View Related
May 11, 2014
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
View 3 Replies
View Related
Sep 7, 2006
I am trying to write a query that will select distinct values from three columns. The table name is Hoods. The columns are C1, C2, and C3. All three columns contain colors and can have the same values, but I only need to have a particular color selected once. For instance, all three columns contain "Black", but "Black" should only be listed once. The result of the query will be used in a drop down list.
The following works for one column:
SELECT DISTINCT [Hoods].[C1]
FROM Hoods
WHERE C1<>"";
Are there any suggestions?
Thanks
View 2 Replies
View Related
Sep 25, 2006
I can create a customized Input Mask, however there are two possible structures that the data may take. I think that this should be doable by the use of two fields, if it can be done with one field it would be preferable for searching options. If I have to use two fields there would only be one or the other used for each record so I would prefer to have the unused field inhibited. Either way the code work is beyond my present ability, any assistance would be appreciated.
View 1 Replies
View Related
Oct 22, 2014
I have a listbox set to Multiselect property of Simple. The listbox is populated by using a table. There are 4 columns in the listbox
Code:
1 3/23/2014 4/5/2014 2014
2 4/6/2014 4/19/2014 2014
3 4/20/2014 5/3/2014 2014
The columns are PayPeriod, StartDate, EndDate, FiscalYear
What I want to be able to do is highlight a chunk of dates and have the first selected StartDate and the last selected EndDate populate two hidden text boxes so I can use them for my queries/reports.
I've tried a couple different ways. Each time what happens is it only uses the last item I have selected in it's calculations.
Code:
Dim ItemIndex As Variant
For Each ItemIndex In Me.lstPayPeriods.ItemsSelected
If Me.lstPayPeriods.Selected(ItemIndex) And Me.lstPayPeriods.Selected(ItemIndex - 1) = False Then
Date1.SetFocus
Date1.Text = Me.lstPayPeriods.Column(2, Me.lstPayPeriods.ListIndex)
End If
Next
In this example I tried to have it go through each Item of the listbox. I wanted to check to see if the current row was selected and the row before it wasn't. That way I could determine it was the first item selected in the group of selected items. It would always only use the last item I had selected.
Code:
Dim CurrentRow As Integer
Dim FirstDate As Date
For CurrentRow = 0 To Me.lstPayPeriods.ListCount - 1
If Me.lstPayPeriods.Selected(CurrentRow) Then
Date2.SetFocus
[Code] ....
I tried to do something similar with this code. Again, it only uses the last item I have selected.
View 2 Replies
View Related
Jun 14, 2005
I am not a professional programer but would like to do a simple database for my group to track research progress. Is that possible to "pick up a date from drop down calendar" then the date will input to the cell (save in table) in a form, instead of typing it in? I saw that in lots of web sites, but can not figure it out how to do it in Access. Tried "Canendar Contol" and "LANDesk Data control" in the toolbox.
Thank in advance
PK
View 4 Replies
View Related
Oct 20, 2005
Is it possible to select 1 MAX date from multiple date fields for a record?
For example, on an employee record there are 10 date fields, each for a performance review date. Some employees may have date fields 1-3 with values, some with just 1, others 1-5, etc.....depending on how many performance reviews they've had.
Is there a way to pull the MAX review date for an employee, knowing that the MAX date could reside in Field 1 for a certain employee, and could be from Field 7 for another?
I appreciate the help guys!
View 1 Replies
View Related
Jul 31, 2013
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
View 6 Replies
View Related
Feb 18, 2014
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
View 3 Replies
View Related
Jan 13, 2008
Dear fellow forum members have been trawling the forum but unable to solve what seems a simple issue.Have a form with a field for a short date input.I would like the year to be input automatically and the user to be left to input day and month.Have played around with format(date(),mm-yy) but unable to crack it.If someone can save me another sleepless night I would be more than grateful.
View 3 Replies
View Related
Feb 18, 2005
What is the simplest way to input a date in the form?
I it possible to include something like this?
http://tinypic.com/1rdwtz
View 1 Replies
View Related
