Queries :: Finding The Next Min?
Jul 6, 2013Im looking to display the 4 lowest figuers obviously Min is the lowest but how would you dind the 2nd 3rd and 4th Min or is not possible?
View Replies
ADVERTISEMENT
Queries :: Finding First And Last Value?
Oct 8, 2013I have a table with assignments if employees to teams.
PositionsID(autowert),EmployeeID, timefrom, timeto, TeamNo
3, 123, 13:30, 16:45, 4
4, 123, 17:00, 19:00, 7
5, 432, 17:00, 20:00, 8
6, 987, 17:00, 19:00, 9
7, 987, 13:30, 16:45, 5
....
Some of the employees can be assigned to more than one team, the assignement is not chronicaly, meaning an earlier timefrom can be assigned later (see Employee 987)
As a resultlist I need following:
123, 13:30, 19:00, 4
432, 17:00, 20:00, 8
987, 13:30, 19:00, 5
I can get out MIN(timefrom) and MAX(timeto) per EmployeeID but not the first teamNo, which is the first team he starts his duty!!
Queries :: Finding Non-Zero Records
Aug 7, 2015I have a table in Access that looks like:
fiscal_year acct_period loc_rc account afe description amount system
2015 6 01001028 745003 100050652John A Doe -69.72 E
2015 6 01001028 745003 100086599Jane Doe -33.37 E
2015 6 01001028 745003 100086599Jane Doe -587.69 E
2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 1 33.37 L
2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 2 587.69 L
2015 6 01001028 745003 994709 AP - EXPRPT05
2115PK - John A Doe - 1 69.72 L
2015 6 01001028 745006 100048910KROGER -389.74 E
It's combining data from 2 system Identified by E or L. The data is similar but not exact & I'm comparing it using the description. What I need Access to do is find the record that doesn't "zero-out". In the example above the record it should pull is the last one "Kroger $389.74". How to do this but I do have a query/report that will subtotal based on the LOC-RC field.
Queries :: Finding Duplicates Using Last Name And First 3 Letters?
Apr 2, 2014I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
Queries :: Finding Records Between Dates?
Jun 4, 2013I have a table tbl_PolicyDetails with details of the policy the customers have with us.. In the tbl_PolicyDetails, I have two fields (these are the ones in question) called
"policyStarted" - Start Date of a Policy and
"policyPeriod" - Term of Policy Monthly/Annually/Quarterly
So in a Form view I just get the Next installment for the customer based on this information for display.. For example..
Mr Butters Stotch's policy started on 26/04/2013 and is paying Quarterly; the next (i.e. 2nd) installment would be on 26/07/2013..
Ms Wendy Testaburger's policy started on 07/04/2013 and is paying Monthly; the next (i,e. 3rd) installment would be on 07/06/2013..
This is not hard to get.. I have that sorted.. But the problem is, my manager wants to be able to specify two dates and search all policies that will be 'paying in' that Date range should be picked up..
So if the search range happens to be.. 01/07/2013 and 31/07/2013 Then Mr Butters Stotch's policy should be picked up, as his next installment falls on 26/07/2013.. Since this information is not stored, I cannot run a direct Query on this.. Also as the Installment number varies for each customer, I would not be able to just add 1 - Month or Quarter or Annum, and see if the date falls in that range..
Queries :: Finding Names That Appear More Than Once In A Table?
Jul 29, 2013I have a table with associate names and rankings for each month. I need some way to find out which associates appear in 2 consecutive months with a certain rating. I have a query that will allow you to key in which month you want to look at, but I can't figure out how to write the query to return results only for those associates that appear twice in the months chosen. For example:
John Doe was rated 1.5 in May and 3 in June.
Jane Doe was rated a 1.5 in May and 1.5 in June.
I want a query that will allow me to put in May and June as the criteria as well as < 2 for the rating and only return those that show in both months...
Queries :: Finding Difference Between Variables
Nov 8, 2013What I am trying to do is the following
I have three columns
Name, Date, Number
What I need to do is find the difference in the number column where the Name is the same but the date is not ex:
Bob 2013-12-12 123
Bob 2013-12-15 456
Jane 2013-12-14 789
Jane 2013-12-25 987
So what I need to do is to get a result that is independent of date i.e.
Bob (456-123)
Jane (987 - 789)
Queries :: Report Not Finding A Field
Oct 11, 2013I have a form that runs a report with two text boxes, one combo box, and a button.
The text boxes hold dates and SQL feeds them the oldest and newest dates from their fields (Date Received and Date Taken). They work fine, when the form is opened, the fields are filled and if I change the dates, the report pulls back data from the correct range.
The combo box is called Analyst Name and this is the row source within the parenthesis (SELECT [PickList-AnalystName].ID, [PickList-AnalystName].[Analyst Name], [PickList-AnalystName].[Analyst Manager] FROM [PickList-AnalystName];). The bound column is 2. The correct names are displayed, it looks like it works fine but then there's the button.
The button is named cboAnalyst. Enabled - yes. On Click - Event Procedure. This is the code with the backslashes added:
option compare database
//Private Sub cboAnalyst_Click()
Dim strCriteria As String
If IsNull(Me.[Analyst Name]) Then
strCriteria = ""
Else
strCriteria = "[Analyst Name] = "" & Me.[Analyst Name] & """
End If
DoCmd.OpenReport "Ad Hoc Reporting", acPreview, , strCriteria
End Sub
If I run this without a name, I get back all of the data within the right time frame. Once I put a name in, the report comes back empty, showing 0 for the count. I don't even get an error, I just get that empty report.
Queries :: Finding Top Three Values In Six Fields?
Oct 3, 2013I'm doing a medical db, where I'm trying to calculate ISS
Code:
en.wikipedia.org/wiki/Injury_Severity_Score
There are 6 values, and i have to sum the largest three (squares to be precise). How can I get three highest values from 6 fields in the same record?
Queries :: Finding Unmatched Records With A Condition?
Dec 26, 2013I want to find unmatched records, where there is a condition. Specifically, which employees did not get a specific mail.
My employees are in this table:
tblEmployees
---------------
EmployeeID (PK)
FullName (text)
and sent mails are logged in tblMailsSent
tblMailsSent
--------
MailID (FK)
EmployeeID (FK)
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
Queries :: Finding Only The Clients That Has Only 4 Types Of Products?
Sep 14, 2014find only some clients that has only 4 types of products, but no other type of products.
Just to put it in a much easier way to understand. If I have to find only the client that did buy only 1 or more from the 4 products. Fridge, tv, dvd player, mobile phone. But I'm not interested in the clients that also did buy for example, laptop, pc, video cameras, etc. So if the client has only one of the 4 products, I want to list them all, did they buy another type of product too, then not.
How could I create a query that will show me only those clients?
Queries :: Finding Latest Number Using A Query
Jun 19, 2013I 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???
Queries :: Finding Booked Vehicles Between Dates
Apr 15, 2013I have a Table with Date Booked From and Date Booked Till
I want a query to show all booked vehicles between the dates in a parameter query, I thought I solved the issue but this is not the case
I am using the following codes
DateFrom - >=[ENTER Start Date]
DateTill - <=[Enter End Date]
All works fine but I have noticed if I put in the
start date 01/01/2013 and End Date 07/01/2013
if there is any vehicles booked between these dates E.g. 03/01/2013 - 05/01/2013, they wont show up.
how can i get round this problem?
Queries :: Finding Work Orders That Only Contain Certain Type Of SN
Aug 13, 2013I am new to access but understand how to do a simple query. in this case i have 4 fields in my query. I only want to return work orders where the only SN's for that work order begin with 600 or NEX. when i use the like button it gives me all those work orders, however it doesn't exclude the work orders with other types of SN's. is there a formula i can use that says return work orders that only contain this type of SN?
View 14 Replies View RelatedQueries :: Calls Table - Finding Last Contact Date
Aug 7, 2014There are three tables. An [Action Register] table, a [Calls] table and a [tblContacts] table. The Contacts are common to both.
The Calls table records calls to customer by date
The Action Register table records issues that Customers send in by Open date.
I am trying to make a query where we see the latest date the customer was contacted regardless of which table.
I created two queries.
qryLastCallDate finds the max date from the Call table:
SELECT Max(Calls.CallDate) AS MaxOfCallDate, tblContacts.ContactName
FROM Calls LEFT JOIN tblContacts ON Calls.ContactID = tblContacts.ContactID
GROUP BY tblContacts.ContactName
ORDER BY Max(Calls.CallDate);
qryLastIssueDate finds the max date from the Action Register table:
SELECT Max([Action Register].Open) AS MaxOfOpen, tblContacts.ContactName
FROM tblContacts RIGHT JOIN [Action Register] ON tblContacts.ContactID = [Action Register].Contact
GROUP BY tblContacts.ContactName
ORDER BY Max([Action Register].Open);
The problem I am having is that if I use Left Join I can see all the records from the Calls table but not all from the Action Register table. And vis versa if I use Right Join. This is because sometimes we have calls but no issues in the Action Register table and sometimes issues with no calls.
This is my Left Join query using a Min Max Module I found here: [URL] ....
SELECT qryLastCallDate.ContactName, qryLastCallDate.MaxOfCallDate, qryLastIssueDate.MaxOfOpen, qryLastIssueDate.ContactName, DateValue(MaxOfList([MaxOfCallDate],[MaxOfOpen])) AS [Last Contact], ([Last Contact]+21) AS NextCall
FROM qryLastCallDate LEFT JOIN qryLastIssueDate ON qryLastCallDate.ContactName = qryLastIssueDate.ContactName;
How do I get to see ALL the records from both queries.
Queries :: Finding Records In A Query Where Date / Time Has Been Used?
Aug 15, 2013I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.
Queries :: Finding Max Record Based On Primary Key In Query
Jul 25, 2015How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
View 2 Replies View RelatedQueries :: Finding Previous Record Date Difference?
Dec 17, 2013I am trying to use a query to find the previous record of a transaction and provide an additional field that computes the difference in the dates in days. (The days from TransferID 23 to TransferID 24)
TransferID Job Number Tool Number TranferDate DaysOnRent
22 1 10 9/1/13
22 1 12 9/1/13
23 2 10 10/18/13
23 2 12 10/18/13
24 3 10 12/15/13
24 3 12 12/15/13
I have seen other posts that reference the following link.
[URL]
Queries :: Booking System - Finding Available Room For Date Range?
Feb 18, 2014So I'm trying to do this database for my ICT coursework and its a full system for dog kennels.
So in actuality the rooms are kennels.
I have a table tblbookings that amongst others has fields:
Kennel No
Date In
Date Out
I need a way of users entering the requested dates for a new booking and getting an output of a list of all kennels that are available to book for that full date range or even better, a way of running this straight from the form for a new booking frmbookings to just leave the first available kennel no. in the field KennelNo?
Queries :: Finding Distinct Records In Table A That Are In Table B
Dec 19, 2013tbl_A has column "ID" with duplicates. tbl_B has column "ID" with distinct values.i want to find all the distinct IDs in tbl_A that are present in tbl_B.I've tried the following with no luck:
SELECT DISTINCT tbl_A.ID
FROM tbl_A LEFT JOIN tbl_B ON tbl_A.ID = tbl_B.ID
WHERE tbl_A.ID IN tbl_B.ID;
Finding Help?
Aug 21, 2007Hi all:)
I am looking for some advise, I work for a small service company and we our trying to computerize our service jobs through the use of access 2007, We have completed 80% of the database but are having a few problems completing, for example the search function on some forms etc, I have searched the web for help and have posted the problem on this forum a number of times but I am still unable to get it to work myself as I am new to access , the problem is I need the database completed asap before we take on a bigger contract and I am running out of time, so I was wondering if anyone knew of anyone / or any company out there that could have a look at the search form, I would obviously pay for it, I have tried to approach a few companies but they will not take on such a small job, they only want to take on the development of the entire database?
Anyone know of anyone?
Jackie :)
Finding #
Nov 4, 2005I'm searching through some text fields like the following:
Ann12 #1
Ann12 #2
Ann12 #3
Ann1 #12
Ann2 #14
ect...
I'm doing a like query
Like "*#*2*"
However, the # is the keyword for Date, so what do I use instead?
Finding ABC
Aug 17, 2006Hi
I hope someone can help with this as I can't seem to get this query to work.
I am trying to use a query to search for the highest value in a column but based upon a specific criteria.
i.e. in the column will be ABC1234, ABC1235, ABC1236 and BBC1234 and I want to get the highes 'ABC' value
I looked on the forum and found this code
Dim strSQL As String
Dim MyValue As DataType 'Replace the proper datatype of field
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tableName.ColumnName FROM tableName ORDER BY tableName.ColumnName DESC"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyValue = rs!ColumnName
rs.Close
Set rs = Nothing
I've modified the query to this
strSQL = "SELECT tableName.ColumnName FROM tableName WHERE tableName.ColumnName Like 'ABC*' ORDER BY tableName.ColumnName DESC"
This doesn't bring anything back but I was expecting 'ABC1236'
I'm new to using 'Like' so this could be the problem.
Your help is much appreciated.
Finding BAC-5, BAC-8 And BAC-12???
May 3, 2007Greetings all,
It's been a while since I've had to turn to you for help. I'm usually able to find what I need in searching through previous posts, but no luck this time around.
I am trying to write a query criteria that will return
BAC-5, BAC-8 And BAC-12
I can get the -5 and -8 with:
Like "BAC-[58]"
But I can't figure out how to get the -12...
The actual part numbers are quite a bit longer and there are hundreds where I only need specific dash numbers returned.
I'm sure this one will be simple, I just couldn't seem to figure out how to search for it.
Thanks in advance!
Finding Something Thats Not There?
Sep 27, 2007Hi guys
I have a DB for insurance permits.
tblSubtractors
ID
NAME
ADDRESS
PHONE
tblPolicy
ID
SubbyID
POLICYID
Insurer
PolicyNumber
ExpiryDate
tblPolicyTypes
ID
Name
Most subcontractors will have a policy number for each type of policy in tblPolicy. But is there a way I can run a query to show each subby that doesnt have one of the policies? As an example, the query would show me that ABCcontractors doesnt have Marine Transit insurance. XYZContractors doesnt have Public Liability, etc
I know how to show all the policies that have expired, but I'm looking to identify which ones dont have a policy at all
:confused:
Finding ID
Aug 6, 2006Hello,
I have this code that I use to update a record:
Code:<%Dim idid = session("loginID")Dim rsSaveSet rsSave = Server.CreateObject("ADODB.RecordSet")rsSave.open "log", conn, adOpenDynamic, adLockOptimistic, adCmdTablersSave.find "id=" & idDim Datumlogout, Uurlogout, loggedoutOKrsSave("Datumlogout") = datersSave("Uurlogout") = timersSave ("LoggedoutOK") = ("true")rsSave.updatersSave.closeset rsSave=Nothing%>
Can someone help me please, I would like to have the code changed to:
When the ID not excists, that there is a new record written.
Thanks in advance!