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...
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?
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'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 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 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?
I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
I am working on creating an access database for tracking physical assets linked to locations. I need to make a combo box list to show items other than the current location of the asset. Basically I need it to refer to last enery of the user and define the new possible entries. so we have a unique relationship between location and asset. The assets and location will always remain fixed and there is never going to be any addition. I am creating a web form so that it can be uploaded into sharepoint.
When i go to input my query, firstly it doesnt display results half the time, secondly it is normally very problematic in terms of being case-sensitive, exact word matching etc.
What do i use (criteria) to program the query to let me use partial and non case-sensitive search methods?
Is there a way to have an update query update values in a table based on the user's input?For example, I have categories of tests and educational assignments indigenous to a course I manage."How many students failed the Science Exam?" The user would then put the manual calculation in since it varies from semester to semester.
I have the following Form and Subform. Form name is "100-Select Form". Subform name is "103-Report Dates". Within the subform I have two fields I'm using, "Start Date" and "End Date'.
In my query I have a date field. I want the query to read from the Subform (if I open the subform directly my criteria listed below works, but when the main form is open, my query doesn't seem to be able to read from the subform).
Here is the criteria I have in the date field of my query: >=[Forms]![103-Report Dates]![Start Date] And <=[Forms]![103-Report Dates]![End Date]
I have a feeling that I somehow should be pointing my criteria first to '100-Select Form', and then to '103-Report Dates' within that form, but I'm just not sure how to write this and nothing I'm trying is working.
I need to create a form, in which would be a query at the bottom.
I have table f.e. "A" in which is all data about cars. I have table "B" in which is all data about customers.
I would like to create form "B" - which would add new customers, but at the bottom of that form I would like to see tables "A" filtered results.
Something like: In form B I create new customer. When I insert what car they want and how much they can pay, at the bottom generates query which would show all possible results from table "A".
With one customer I know how to do it. But when I insert second customer in the form, that query stops working while it doesn't know from which entry it should take the information. I don't know how to tell, that the query should take the data from the customer ID1 when I'm looking customer ID1, and from customer IDX - when I'm looking customer IDX.
I thought, perhaps it possible to create button with macro - when I press the button it copies all data from the current entry, it pastes in the form where is only one entry and works with query, than generates the query results which will be showed in the same entry that I pressed the button.
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
If I create a form based on the fields in my query the form will not allow me to edit any fields - no matter what.
All fields are unique however I am stumped as to why I can't edit the data. Even adding in the second query immediately stops data entry even without linking.
I currently have a form that creates a report based on a query that takes in a start date and an end date. What I would like to do is to create comboboxes that will let the user choose a month and year for the report. Is there a simple way to choose an entire month in access? I've tried using the Month() function to no avail.
The SQL statement for where I select the start and end date is below:
WHERE ((([tblData].[EntryDate]) Between [Forms]![frmByRange]![BeginningDate] And [Forms]![frmByRange]![EndingDate])
I'm new to access so I thought this would be easier than it is. I have a list of cities in a simple table . I want use this list (or a query of it) to create a validation rule for a field in a second table "City". How, either with VBA or some other method do I use the Cities database to validate entry. These tables are both in the same project.
I have a form where customer data is entered. one field serves as a criteria for a query. If, for example, A is selected in the form the query uses the criteria "A" in the X field f query. However, if B is selected in the form, the query uses the criteria "B" in Y field of query.
I can easily do this by setting up two queries and having criteria A run in X field of query and similarly for B. However, it seems as if there should be a way for me to have one query and simply use the criteria in a different field.So, is there VBA that will update a given query's SQL to use one criteria in one case and another critiera in another case?
I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.
Table tClients stores the current clients - primary key is named "clientRef" Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
Once the completed flag is set that client will be ignored so no further entries will appear.
So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.
table name : Schedule Field 1 = Vessel code Field 2 = Voyage Field 3 = ETA Field 4 = berthed Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
I have a database that I run a few queries on to do some simple calculations for me using IIf statements. I did not need these fields in the datasheet view so I unclicked the Show button and everything looked great. Now when I go back into my query, those feilds are gone, how can I re-show them to make some changes? Thank you for any help.
A friend has developed a database at college. All seems pretty well but the query she uses to populate a form and reports is incorrectly displaying the curence. Her main tables are just number fields. The query calculates correctly but her lecturer has told her the query needs to show a £ sign with the figures retrieved. Any ideas?
