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.
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?
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.
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2 4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
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?!?
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.
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
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.
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.
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 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.
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;
Below 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_;
I have a table that contains readings from several pieces of equipment as well as the status of each one. Each record has a timestamp, equipment number, status, etc. What I want is to create a query that will return the latest record for each equipment number. Simplified example table:
There are more than 20 different Equipment numbers and they are read several times per day and sometimes some of them get missed. What I'm looking for is a way to get a list of all the machines with their latest reading so they can tell which machines are running and which are down based on the last time they were read (instead of specifying a date). I can get this for one machine with no problem. I'm having trouble getting it for more than one machine. I tried a union query (with just 2 of the machines included for testing) but it only returns the results from one machine:
Code:
SELECT TOP 1 TestCompressorRoundQuery.LoggedAt, TestCompressorRoundQuery.AssetNumber, TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes FROM TestCompressorRoundQuery WHERE (((TestCompressorRoundQuery.AssetNumber)="104399")) UNION ALL
[Code] ....
I'd rather not have to create a seperate query for each machine and then join all of those together! I think perhaps a Union query might not be the correct approach. All the data is coming from only one table.
I have a form that allows users to search the database for records based on there criteria. The form allows the user to search, through combo boxes, by variables. The first is MAT, the second is Relocation Area. The search works fine when the user selects options form the combo boxes. However sometimes it is neccesary to leave the location field blank and only search by MAT. When the location combo box is left blank no records are found. Is there anyway around this. The SQL code for the query is below:
SELECT [Extract Data].[ID], [Extract Data].AREA, [Extract Data].[MAT], [Extract Data].[Relocation Area 1], [Extract Data].[Relocation Area 2], [Extract Data].[Relocation Area 3], [Extract Data].[Grade Score] FROM [Extract Data] WHERE ((([Extract Data].[MAT])>forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 1])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 2])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 3])=Forms![Data Search]!SearchArea));
I am using Access 2002 database with ASP.NET(VB). I usually try to get something working in the access 2002 GUI 1st and I am having trouble with creating the code to require that a field entry be forced to be another exisiting entry in another table. For Example:
UNIT_TABLE has fields UNIT_ID(key), and UNIT_NAME having values of: 1 Tigers 2 Bears 3 Sparrows
BATTLES_TABLE has fields BATTLES_ID(key), ATTACKER_MAIN, and DEFENDER_MAIN
I want the ATTACKER_MAIN and DEFENDER_MAIN choices to ONLY be members from UNIT_TABLE.UNIT_NAME
I set up a combobox and lookup in BATTLES_TABLE as follows: SELECT UNIT_TABLE.UNIT_NAME FROM UNIT_TABLE ORDER BY UNIT_TABLE.UNIT_NAME;
this OFFERS the CHOICE of any of the 3 clans (ie Tigers, Bears, Sparrows)
but I cannot not FORCE the USE of these....because the end user can option to enter in anything else.(which is not wanted).
How do I add SQL to the LOOKUP such that... the user can ONLY choose to enter text that EXACTLY matches an Already existing entry in the UNIT_NAME field?
I tried to enter a validation rule: [ATTACKER_MAIN] In ([UNIT_TABLE.UNIT_NAME])
I get an error that the field 'UNIT_TABLE.UNIT_NAME' does not exist.
Thanks for any help with this validation problem. HKP_MACK
Is there a method of setting the default value for new entries to the same value as the previous entry?
For example, I have a quality control information table that stores QC data for different lot numbers. It is annoying to enter in the lot number and product code field over and over again if there are dozens of records per lot number. Is there a way for Access to easily display the previous entry as the default value?
I have a database that is tracking attendance for several 100 employees. The Db is mainly used to log any policy occurrences (no call, no show). If an associate doesn't have any occurrences for a month, then they get a point credit. Right now, I set it up so the credit can be manually added. the problem is a supervisor (the user) may not know if their associate should receive this credit unless they review their attendance report. Opening the form to add a policy occurrence, then running a report to only re-open the same attendance form is inefficient. What I'd like to have is a way to have a credit automatically added if they did not receive any points for a particular month.
ps. My office does not allow uploading any data/files so I am not able to upload a copy/sample.
I have table with different companies and related info. Another table with contacts for each of the companies, each company might have different number of contacts. I want the form to show all the contacts for each of the company, if there are two contacts in the company then show two, if five then show five. Just to make sure I am doing it the right way, what is the best way to build a form that is based on multiple tables. Thanks in advance, John
i am trying to show the total number of records that are in a table.
there are currently 8 entries in the table 'applications'
now i would like to show this on a tabcontrol in a form. I have been told to use Dcount. I have read about this and tried this on my form but unfortunately its not working.
Can anyone show me an example of how i would do this.
tblEmployees - Stores employee data tblProcess - Stores process data tblTraining - Stores data on which employees have trained on which process
tblEmployees has a 1 to many relationship to tblTraining through empID tblProcess has a 1 to many relationship to tblTraining through pcsID
When a new Process is added, a query auto populates tblTraining with 1 record for each employee in tblEmployees. Equally when a new Employee is added, a query auto populates tblTraining with 1 record for each Process in tblProcess.
In theory this should ensure that every employee has a training record for every process (and vice versa). However, as many of us know, never assume the user wont find a way to mess things up!
So i want to have a query that can show me Employee and Process without a training record. I have been manipulating a SELECT (SELECT) query to pull this together, but i just keep coming up blank.
I have attached a copy of the relevant tables and query. I have deliberately deleted a training record for empID 6 on pcsID 1 AND empID102 on pcsID 2 (qryCheckTrainingRecordsBalance will show the processes these 2 DO have a training record for).
I am trying to create a query which show me which of my customers in my database does not also contact a contact to go with it.
I have two tables; Customers and Contacts.
When I create a query I add the fields Company Name from the Customers Table and First Name from the Contacts Table. There are obviously other fields in both tables.
However when I run the query it does not not show me all the results. It only shows me customers if something has been entered into any of the fields in the contacts table for that Customer. I suppose this triggers a contact ID entry for that customer even if no Name has been entered.
However, how do I run a query to show me customer entries which have no contacts to go with them because no contact entry has been made for that customer?
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.
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)
I created a form for data entry only where records could be continually added until you were done and closed the form. Into that form I dropped a query that kept a running list in datasheet form of all the new records entered while the form was open, and then cleared when the form was closed (so the query was empty when the form was opened again).
My Problem: I accidentally deleted that query.
What I need:
-Query to be empty when form is opened. -Records added while form is open get displayed in the query after record is saved.
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...
