I would like for my report for default to the earliest and latest date ,should the user not select a start date and end date.
I have read about using MIN and MAX , but i am not sure how this would work.
Ok, I have a query that returns a case manager, their clients, and a date attributed to each client. Ex:
Case Manager Client Order Date
James Bond Pussy Galore 01/05/2005
Oliver Cromwell Janet Roundhead 05/25/2005
Alan Turing E. Nigma 08/20/2005
James Bond Holly Goodhead 12/18/2004
What I would like is for the first James Bond record to disappear, as it is later than the last record, giving me only the FIRST Order Date for each Case Manager. Is this possible? If so, what is the criterion expression?
I have a table that contains ID numbers plus appointments that these patients have had with a carer. The appointments are a dd/mm/yyyy entry and there are a number associated with every patient.
I need to isolate the first/earliest date the patient was seen. What is the criteria I use in a query??
I have the below listed expression, which will give me the earliest date of when documents were received.
However, if I only have a date in the "B - Datasheets" field, it will not appear on my report because it starts off "isnull".
How can I end the expression to use this date field if there are no other dates?
Submittal: IIf(Not IsNull([BID_PO]![B - Datasheets]) And [BID_PO]![B - Datasheets]<[BID_PO]![C - Curves (Performance Data)] And [BID_PO]![B - Datasheets]<[BID_PO]![D - Schematics] And [BID_PO]![B - Datasheets]<[BID_PO]![E - Drawings],[BID_PO]![B - Datasheets],IIf(Not IsNull([BID_PO]![C - Curves (Performance Data)]) And [BID_PO]![C - Curves (Performance Data)]<[BID_PO]![D - Schematics] And [BID_PO]![C - Curves (Performance Data)]<[BID_PO]![E - Drawings],[BID_PO]![C - Curves (Performance Data)],IIf(Not IsNull([BID_PO]![D - Schematics]) And [BID_PO]![D - Schematics]<[BID_PO]![E - Drawings],[BID_PO]![D - Schematics])))
I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)
The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.
How can I go about it - and still leave both the main and subforms fully editable?
I've run several searches and can't seem to find what I'm looking for, so here goes:
Have a table with several (maybe as many as 30) dates related to each employee, such as:
NAME-------CLASS 1-----CLASS 2----CLASS 3
J.Johnson.....10/16/05 ......12/17/05...12/25/05
S.Smith.........09/05/05......10/15/05
Not all employees will have entries in every class.
Need a query to result in most recent Date for each employee, such as:
--------------
J.Johnson......CLASS 3........12/25/05
S.Smith.........CLASS 2.......10/15/05
Any suggestions?
I have a table that shows me 3 fields:
PROCESS
EFFECTIVE_
OPERATION_
My issue is each process and operation may be there multiple times due to multiple Effective dates. I only need to see each Process and Operation one time based on the latest Effective date. Below is what I have NOW:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-01 10
1/010/1-8TCOWLFEEDER 2005-11-01 20
1/010/1-8TCOWLFEEDER 2005-11-01 30
1/010/1-8TCOWLFEEDER 2005-11-01 40
1/010/1-8TCOWLFEEDER 2005-11-03 10
1/010/1-8TCOWLFEEDER 2005-11-03 20
1/010/1-8TCOWLFEEDER 2005-11-03 30
1/010/1-8TCOWLFEEDER 2005-11-03 40
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
This is what I need:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
Out of the records above I would only want to see the records with the 2005-11-09 date. The dates can vary based on processes so I really need something that selects the the latest date for each Process and Operation. I would like to do this in a query or multiple queries.
Thanks for any help.
I am trying to create a query or series of queries that will identify the latest record of a field called CheckInDate. The table will have multiple entries. For example.
Book#__________CheckOutDate_____________CheckInDat e
__1_____________01/01/2007_______________03/01/2007
__1_____________04/01/2007_______________05/01/2007
__1_____________01/01/2007_______________
__2_____________01/01/2007_______________02/01/2007
__2_____________02/05/2007_______________04/27/2007
__3_____________01/01/2007_______________03/01/2007
__3_____________05/01/2007_______________07/01/2007
I need the results to be:
#1(ALL) will not be listed, as there is a book still checked out
#2 will show the record with the 04/27/2007(ONLY), as I want the last time the book was checked out.
#3 will show the record with the 07/01/2007(ONLY), as I want the last time the book was checked out.
I currently have a somewhat flat table where this is concerned. Please advise on what I would need to do to create this. Thanks!
I am hoping someone may be able to help me with this one. I have a set of dates that I would like to query for the next latest date based on certain criteria. Not sure if I explain clearly, if more info is required, please advise. Thanks so much in advance for your help.
View 4 Replies View RelatedI have a simple table in Access 2003:
Case# (numeric)
CaseOpen (Date)
CaseClose (Date)
Item# (numeric)
ItemOpen (Date)
ItemClose (Date)
Some of the date fields may be null; If null then that date may NOT be selected
I am trying to write a query that returns:
Case#
Item#
Only the most recent date in any of the four date fields
The heading that matches the selected date field
ie:
Case# = 251
Item# = 4756
Most Recent Date = 7/8/2005
Matching Heading = "ItemOpen"
I have been searching and reading for two days-
Help, please?
TIA
I'm trying to sort dates by the latest date when the query returns multiple IDs with different results. Ex.
ID1 1/1/2006
1/8/2006
ID2 1/2/2006
1/9/2006
In this example I would want ID1 with the date of 1/8/2006 and ID2 with the date of 1/9/2006 since they are the latest date. I will have many IDs that I need to run a query on that will all return the latest date. TIA
I need to find the LATEST date in field Recdate that is 45 days or older in a query. I have tried <Date()-45, etc. but it returns all dates not just the last one. Does this require a range of dates to do this ? If not, how would I id the last date input ?
Thanks
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
Hi guys
I am trying to create a simple database to keep track of computer components that I order for custom PCs. The data is fairly basic Quantity, Description, Stock Code, Delivery Status, Cost, Cost+VAT, Date Delivered. The list is currently 500+ rows of data. I simply cut and paste this info directly from my online invoices.
What I want is to be able find the the last price paid for an item (often the same items have different prices week by week) Eventually I would like to be able to create a rough quote using the latest prices (plus mark up) and also view a price history.
I would also like to include a few pictures as URL links to the suppliers website ????
At present the data is in Excel and linked to a single table in Access (but this can be changed). I realise that I have to group the records somehow but cannot get it to work. At present it lists every record either in date order or product order.
The stock code should not change, but product descriptions do. So any links to pics would be based on stock codes
Someone please tell me how to display the full list of products but only the latest price/date
If you think the current format is rubbish then feel free to suggest an alternative. I much prefer Excel but have never really got to grips with Access (used to be a vba programmer with Excel). To link to pictures and hide data that customers might see Access seems the obvious choice. So I try again!!!!!
Thanks
Steve
I have a list of project, each of which have dates which work were carried out on them. Each project can have more than one date. I want to be able to find the last date that any work was carried out, and then calculate how many days have passed since that happened.
View 6 Replies View RelatedBelow is the code I have for a query. This query shows Processes, Operations, etc. Due to multiple Effective_ (Effective_ stands for Date) The Operations show multiple times. What I would like to do is have each Operation show only one time based on the latest Effective_ i.e. If Operation START-UP is listed three times due to three effective dates 2007-01-04, 2007-01-08 & 2007-01-17 I would only like to the the one associated with the 2007-01-17. Is there a way to accomplish this in my query?
SELECT ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
FROM ASSYROP INNER JOIN ASSYWC1 ON ASSYROP.OPERATION = ASSYWC1.OPERATION
GROUP BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.OPERATION_, ASSYWC1.PROCESS_AT, ASSYROP.OPERATIO_1, ASSYROP.EFFECTIVE_
ORDER BY ASSYROP.PROCESS, ASSYROP.OPERATION, ASSYROP.EFFECTIVE_;
My table shows
ID ID_Clients EndDate
A particular value of ID_Clients can recur many times, but with different EndDate value each time.
I want to create a select query that displays
ID_Clients EndDate LatestPreviousEndDate
Any help with this would be much appreciated.
-Thanks
I have two tables. The first is called Drawing Register and contains the fields Drawing No (primary field) and Drawing Title.The second table is the Drawing Register Details table which contains the fields Drawing No (joined field - Drawing Register table), Revision, Revision Notes, Date Issued.
I have a query and report which will list all the revisions for each drawing. This is very important and useful. However I want a summary report which will only show the last revision for each drawing.
I copied the original query, turned on the Totals option and under the Date Issued field changed Group by to Max. However it is still returning all results for each drawing instead of only the last issue date for each drawing.
Hey Guys & Gals, extreme newb here attempting to display the latest activity date in an SKU activity db in a query that spans 12 months with multiple activity dates on each of the 1,200 SKUS.
I have this so far and of course it only shows the last (DMAX) activity, not SKU related. How can I relate this result to each SKU to diplay the latest activity for each.
SELECT [Usage06-07].workorderDateClosed, [Usage06-07].qty, [Usage06-07].itemNo, [Usage06-07].Item
FROM [Usage06-07]
WHERE ((([Usage06-07].workorderDateClosed)=Dmax("workorderdateclosed","usage06-07","item")))
ORDER BY [Usage06-07].Item;
Thx in advance for your patience :)
Joe
Hello All...
I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid".
The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
Thanks!
I'm trying to create a query that has a calculated field that shows a maximum value from multiple fields.
As far as I can find, this is not built into Access, so I've used this code from a Microsoft page:
Code:
Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
' Set the variable currentVal equal to the array of values.
[code]....
The problem I'm having is, well this doesn't work for me.I'm a bit of an beginner VBA coder, but I understand this code and don't know why it won't work.It only displays the value from the field within the brackets.The values used are Dates, so I need to display the latest date from multiple fields.
How do I query from this table:
ORIGINAL_table
FName | END_Date|
--------|---------------|
John| Oct 09, 2014|
John| Oct 15, 2014|
John| Oct 25, 2014|
Mike| Dec 10, 2014|
Mike| Dec 15, 2014|
Mike| Dec 20, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 10, 2014|
Jimm| Dec 15, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 01, 2014|
Alex| Dec 05, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
Thom| Oct 10, 2014|
Thom| Aug 10, 2014|
The output will be:
FName | END_Date|
----------|---------------|
John| Oct 25, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
The query table(output) will just have to be latest date("END_Date") per "FName"
I try to make a query that can show me data from the lastest and max date from the table i have ( see attchment file )
I try using max but still the result is not what i am looking for...
I am trying to make an on-click event that would open a form showing the last inspection done on a site.
Unfortunately, I cannot even first create a dlookup function to use, so I haven't even attempted the rest!
The data needed to reference is in one table, and I just...can't... quite get it.
Here is my last attempt (which at this point probably isn't my best )
Code:
=DLookUp("InspectionID","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "' AND InspectionDate = #" & DMax("InspectionDate","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "'") & "#")
After breaking it apart I'm pretty sure the DMax function (and using date?) is the culprit, but I can't figure out why.
If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).
In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.
So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId
I then need to check the days between tbl_StockItems.LastStockDate and Today.
How would I write an Access VBA query to give me the amount of days and put that number into a variable.