Two of the fields in my query are for Progress Note and Progress Note Date. Each client has several progress notes. How can I have the query show only the Progress Note with the latest date?
:confused:
I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.
e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.
Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?
Below is the my VBA code so far, which produces the above result.
SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date
FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn
WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date]))
ORDER BY tblEmpSalaryHistory.next_review_date;
My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.
I have a sub report on my form to show all data needed for a delivery note.
these fields are :
House Type
Plot No
Product
Delivery Note No
when i use my query to search orders in my subreport - it will show the required data but will also show data i didnt search for ? why is this?
Sometimes my search results in the subreport show further down the subreport than the irrelevant data - all i would like is for it to show the specific data not the irrellevant data.
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.
I am working on a jobs database where employees enter information where the job is being handed off to. I want to create a table showing the latest job entry by date. The jobs are listed by "Job Number" and when I try to create a table and remove the duplicate "Job Number" it does not always remove the oldest entries.
Any help would be appreciated.
I have a database I want to load tire and brake measurements into. I have created a table which I enter records into, including the date, measurement, the wheel position and the item (tires or brakes) being measured.
On the form I have a picture of a tire and a brake at each wheel position and I would like to be able to display in the control by each item the latest measurement in the main table that matches that wheel position and item.
Hello..
I have a one applicant to many callers database setup. I want to be able to see only the last call to the applicant based on date.
I have tried the following:
SELECT tblApplicant.ApplicantID, tblApplicant.ApplicantLName, tblApplicant.ApplicantFName, tblApplicant.LastFour, tblApplicant.TestDate, tblApplicant.JacketExpireDate, tblApplicant.ContinueProcessDate, [ApplicantLName] & ", " & [ApplicantFName] AS Name, tblYsNo.YesNoType, tblLuTestType.Type, tblCaller.Comment, tblCaller.Date
FROM tblYsNo INNER JOIN ((tblApplicant INNER JOIN tblLuTestType ON tblApplicant.TestType = tblLuTestType.TestType) INNER JOIN tblCaller ON tblApplicant.ApplicantID = tblCaller.ApplicantID) ON tblYsNo.YesNoMain = tblApplicant.Interested
WHERE (((tblYsNo.YesNoType)=[Forms]![frmReport]![cboInterest]) AND ((tblLuTestType.Type)=[Forms]![frmReport]![cmdTestType]))
ORDER BY tblApplicant.ApplicantLName, tblCaller.Comment DESC , tblCaller.Date DESC;
Which had both the caller date and coller comment descending and I have also had the caller date and coller comment ascending. Then I have tried one at a time. But in the report that is based off of this query it still only shows the first comment by date not the last comment.
IE
01/01/2004 comment this is a test
01/02/2004 comment this is a test again
01/03/2004 comment test test
On the report I only want to see the 01/03/2004 comment not the 01/01/2004 or 01/02/2004.
Does this make sense? I have been banging my head...
I created a vehicle database that includes a sevice subform to track service history. The main form is bound to the vehicle table that tracks the vehicles we have. (Vehicle_ID). The service history has its own table that stores its history and is link to the vehicles table. I created a report that shows my vehicle and its last history item. When i click on it, it will bring up the vehicle form where you can add information. Is there a way to create the form, so when i enter the latest information and i click on it, it will bring up my main form, and in my subform svc history, display the lastest information.
View 1 Replies View RelatedHey 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
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.
I have a query that returns numerous rows of data per person per day.
Simplified Example ( ';' used to show columns):
LastName ; Date ; DrillNumber ; Score
Smith ; 18/9/14 ; 0 ; 100
Smith ; 18/9/14 ; 1 ; 150
Smith ; 18/9/14 ; 2 ; 120
Doe ; 18/9/14 ; 0 ; 150
Doe ; 18/9/14 ; 1 ; 150
Doe ; 18/9/14 ; 2 ; 100
etc.
I'm trying to create a report that shows each of the scores per drill in the report per person (Dont worry about the formatting):
Name: Smith
Date: 18/9/14
Drill & Score:
0 - 100
1 - 150
2 - 120
However, when I drag the field lists into the report Im getting a new report entry for each drill eg.
Smith
18/9/14
0 - 100
Smith
18/9/14
1 - 150
etc.
Any way to get all drills to show in the one report entry per person? So all data for Smith is in the one report or report section, and then all the data for Doe.
when i want to make a delivery note - on many cases the Delivery Note Number that shows has more than one house name, Plot Number and Product - at the moment it only shows one specific house name, plot number and product when i would like it to show every one of these under the delivery note number and delivery note date?
View 2 Replies View RelatedHere're something I want to achieve in my database (Access 2010):-
1. To create a login page
- The table “tblUsers” has been created.
- The login page will be popped up once the database is opened.
- The user1 will be set to be an administrator who has the right to edit the info and the structure of the database. (User1’s password: 1234)
- The user2 will be set to be an end user who will be responsible to create a quotation request through the form “frmQuotationAllinOne”. (User2’s password: 5678)
- The user3 will be set to be a supervisor who will be responsible to approve/reject/give comments to the quotation request. (User3’s password: 2468)
2. Welcome note
- Once the login procedure is done, the Welcome note will be popped up.
- Under On Load event, I have keyed in the event procedure. It worked sometimes and sometimes not. It seems that the Access database is not performing consistently and stably.
I'm building a database for my folks's volkswagen car show (www.texasvwclassic.com) if anyones interested ;)
So I've got a Registrations table and a Classes table. Each person that registers for the show is an entry in the Registrations table, and a field in that table is Class, with a relationship to the Classes table. That is all set up and working fine, but I want to add an EntryNumber field generated at time of checkin, so some VB function I can call when the checkbox is clicked in the form, or a query based off the value of the checkedIn field. I want the EntryNumber field to contain a number like 0503 (CCNN) where CC is the class number (class #5) and NN is a counter of how many people have checked in so far in that class (so 0503 would be the 3rd person to check in for class #5). I can get the class number fine, and I can prolly figure out how to stick em together into another field, but im not sure how to generate the NN part.
In the classes table, each class has a sub-table listing all the registrations that have entered that class. I need like an autonumber field in each of those subtables, but I dont understand how to add something like that.
I hope this makes sense, as always any help is appreciated. =)
I need to add a credit note to my statement report which minuses a wrong invoice. For example, if a wrong invoice is given to a customer, then the user uses a credit note form which updates in the credit note table and this shows in the statement report as a minus. I hope that's not too confusing. The problem comes when I need to add it to my report. It will have the same date as the original invoice and the same price, it will just be shown as a minus.
My statement starts as a form which the user has to enter a start date and an end date and select a customer from a combo box, they then click generate statement which opens the report. How would I add the credit note in? I want it to be in the same date selected but the query for the report only contains the invoice date and not the credit note date. If I add this into the query, it clashes and brings up a blank report. Is there anyway to do this? Will I need to create another query for the credit note and add it in as a subreport? Will I have to add another startdate and enddate for it too as a subform in the statement form?
How do I make an append query only append the records beginning with the next autonumber?
I have linked tables in db...one db is for warehouse employees to input their orders and the other table is in the original db for managers to use for reports. I need to append the records to original db throughout the day, but the records cannot be deleted from warehouse db because it keeps running total of order minutes (for production purposes) so employees know how many minutes they have (and need to make up) through the day. Also, managers need up to date db so they can see if they are on track.
The autonumber field is my primary key..
I hope this all makes sense.
Thanks in advance
Noreene
Hi,
I am working on a report and I am stuck with the following problem:
Here at work we use two tables. One for creating records and for the updates of the created records. In the attachment you see a part of the table 'updated'. What you see is one ServiceNumber (1-551747091) which is changed users (Eigenaar.) three times.
I want to query the latest user of each ServiceNumber. So that the result is:
ServiceNumber | User
1-551747091 | MKB_VERHUIZEN
etc.
How to do that? :confused:
Thanks!
I am having difficulty formulating this query.
I have 2 tables... 1 is customer info (name, address, etc...), and the second is shipments (ShipToCustomerName, ShipmentDate, etc...).
Now, I wish to query the shipments table to get ONLY the LATEST shipment information for EACH customer.
If customer A has a shipment in Jan 1, Feb 2, and March 3... while customer B has a shipment in April 4 and May 5. then the result of the query should be
customer A - March 3
customer B - May 5
Is there a simple way to construct such a query?!?
Thank you for your help,
- arm
I have two fields. A barcode field will have several instances of the same data, and the other field has different dates and times when it was scanned. I need to be able to query the barcode field and only return the record with the latest time. Can this be done in a query or do I need to do something else? Here is a sample of data I am working with. I attached a small sample of data as an example.
Thanks,
I'm fairly new to SQL and have a problem finding the latest record.
Lets say I have a development project that has many disbursal payments to be sent. I want to find the latest disbursal record created for that one project instead of having all of the disbursal records show on the query.
I'm trying to work out how to do this in a query but haven't got a clue where to start.
Please, if anyone can help I'd be very grateful!:D
I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?
Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.
SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total
FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id
WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005"))
ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;
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 need to find the latest number using a query.
All of the numbers follow this format:
[Doc No]
ABCD123
but the tricky part is the number could be suffixed with a revision. It is the latest revision I would like to find. For example, If I have a number followed by 3 revisions I would have:
[Doc No]
ABCD123
ABCD123+200
ABCD123+300
ABCD123+400
I need a query to give me the latest revision, in this case
[Doc No]
ABCD123+400
There is a possibility there are no revisions in which case the latest will be
[Doc No]
ABCD123.
How is this possible in a query???
I am extremely new to Access. I have my database up and running ok(ish) and would like to know if in my data entry form, I can have the "Date Entered in Database" box display the current date that does not change from day to day. I know you can use the "Now()" function but won't this just change everyday?
View 3 Replies View RelatedMy table appears like this with the following columns:
Name| CUSTNo| ORderNo.| AcctDate| OrderDate| OrderDescription
I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records.
I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.
SELECT * FROM TableName AS a WHERE Not Exists (SELECT *
FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);
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"