Finding Predecessors Within A Table
Mar 11, 2015
I have a table of contracts. These contracts are renewed every year, and therefore many contracts have a version from the previous year, but some are new and have no predecessor. I want to write a query which lists all contracts on the table, together with a reference to any preceding contract with the same customer, or a null if there is none. For example, if the table fields are contract number, customer and year, and the table looks like this:
Contract Customer Year
271 Smith 2013
816 Brown 2013
101 Jones 2013
227 Smith 2012
564 Brown 2012
I want a query which returns:
Contract Customer Year Previously
271 Smith 2013 227
816 Brown 2013 564
101 Jones 2013 null
227 Smith 2012 null
564 Brown 2012 null
View Replies
ADVERTISEMENT
Dec 19, 2013
tbl_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;
View 3 Replies
View Related
May 11, 2005
Please note that I am self taught (90% of what I have learnt has been off these boards!). I did make this form with info I found on this forum.
I am having problems with a log in box for a database, the line of code is -
If Me.txtpassword.Value = DLookup("password", "customers", "[customerID] =" & Me.EbayName.Value) Then
This code is in a Form; "password" is the value in the table of "customers"; [customerID] is the primary key value I wish to remember; Me.EbayName.Value I assume is the value in the table that access is looking for.
Questions -
A) It is not looking up the value for [customerID] - what have I done wrong?
B) In the line of Me.EbayName.Value what does the 'Me.' part tell Access to do? I assume it's a pointer to it's own form? What part of the line do I need to change to make it point to another form (or table)?
C) When this is finished how do I get the program to remember the 'customerID' while the customer is fillling out other forms?
James
View 2 Replies
View Related
Feb 17, 2005
I have patients and their details all listed in onetable.
I want a message box to come up if a patient also has a family memeber somewhere else in this table. To do this i need to
match surname and mother's christian name.
Ani ideas?
View 1 Replies
View Related
Sep 22, 2011
is there an easy way to find out in wich form a specific table is used.
I have an access application and i need to find out if every table definded is used in the application and in wich form.
I can open each form, see wich tables are used and make a list. But i have a lot of forms and there is the possibility to miss one.
View 2 Replies
View Related
Oct 13, 2012
I am working on a DB for maintenance of medical records for use by medical mission groups in Central America. As a child's information is entered into the DB, I want to be able to display his growth progress percentile numerically instead of graphically as found on a standard CDC.gov weight vs height growth chart. The graph are nonlinear so I just can't use an expression.
I have the data for percentiles based on weight and height but I need to be able to find the CLOSEST value on this table to the child's observed values of height and weight.
How do I find the value on a table which is closest to a specified number?
View 6 Replies
View Related
Jul 29, 2013
I 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...
View 5 Replies
View Related
Jul 18, 2012
Product Name
Start Weight
End Weight
Product 1
379000
389000
Product 2
288435
226710
Product 3
699000
318882
Product 4
36800
50000
Product 5
89180
117280
Product 6
132000
132000
Product 7
18
18
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
View 3 Replies
View Related
Mar 25, 2015
I have one table with 250 fields and hundreds of records. Each field represents a computer we have and the records in that field represent the type of applications we have on that computer. So the table looks like this:
Computer 1..........Computer 2.......... Computer 3........ Computer 250
Microsoft...............Google.................... .Paint.....................Microsoft
Windows Hotfix......Security Updates.......Java ......................Google
Java.....................Mediaplayer.............. Google.....................Java
As you can see, most computers have the same applications, but some have applications that others do not. I just need the list of applications we have from all computers. Meaning each app is listed only once.
I just need each application listed once. So I was trying to come up with a way to delete all the duplicates or use a Totals query to group the apps but since my table has 250 columns I'm not really sure how to go about it. What kind of query would be best to make this work?
View 3 Replies
View Related
May 18, 2005
Hi All,
I have one table where ID field is autogenerated, and another field orderno which is not primary key. Now it has 2 duplicate values in it so when I try to make field Orderno as primary key, it is not allowing me to do so.
Can any one help me how to find the duplicate values using query.
Thanks in advance,
Jigs
View 1 Replies
View Related
Jan 5, 2014
I have a table where there is information stored about certain workorders.. like location, description, etc.
The thing is that location is descripted as "03-LZ-.." where the 03 part stands for the departement. I have another table with all the numbers and departements and now I'm looking for a way to link the two. So I have to find a way to look at the first to digits of the "location" in my workorder table and link that number with the table where numbers and departements are stored.
View 2 Replies
View Related
Dec 17, 2004
I have been working on quite a few multi user Access projects in the past few months with a backend and a front end which has linked tables.
I would like to know if there is any way to find out the source of a table linked to the front end and where would its location be on the network.
View 3 Replies
View Related
Aug 7, 2014
There 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.
View 3 Replies
View Related
Jun 19, 2013
From what I have read, I understand you can't use the seek command on a recordset from a linked table from another database. Is that true? If so, what is the alternative to find a specific record in the table using an indexed field?
View 4 Replies
View Related
Jul 19, 2007
hi,
I linked a table (front to back) yesterday. I had a user enter information into this table. It's a login table. The login screen of the asp page is not able to find the record, even though I can see it in both tables. I am not sure what's wrong, since both tables show the same information. Does anybody have any ideas?
thanks in advance.
View 2 Replies
View Related
May 2, 2014
I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.
Let me try to explain better.
example:
I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.
Quote:
Name DOB
bob 19800201
Sam 19761211
Jim 19800201
The output I would like is to have Name = bob & Jim DOB = 19800201.
I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.
View 1 Replies
View Related
Aug 21, 2007
Hi 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 :)
View 2 Replies
View Related
Nov 4, 2005
I'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?
View 1 Replies
View Related
Aug 17, 2006
Hi
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.
View 6 Replies
View Related
May 3, 2007
Greetings 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!
View 3 Replies
View Related
Sep 27, 2007
Hi 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:
View 7 Replies
View Related
Aug 6, 2006
Hello,
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!
View 1 Replies
View Related
Aug 8, 2007
I have a customer concerns database that contains the dates for when the concerns were reported and tyhe dates for when the concerns were resolved. I am trying to make a query that finds the average of how long it takes for the concerns to be resolved. How can I do this?
View 1 Replies
View Related
Feb 29, 2008
Now this is not like me, posing a question, but I thought its about time I codes the app correctly.
My app opens up any given spreadsheet. The first task is to establish the last row used in the spreadsheet. Normally you would loop until there is nothing in a cell in column A, whatever.
However the way the spreadsheets are provided there may be pleanty of blank rows. I have tried using the Edit GoTo Special LastCell and converting it to a macro
But when I run this VB it does not like it for some reason.
I am sure someone out there has come up with a solution by now.
CodeMaster::cool:
View 4 Replies
View Related
Nov 2, 2005
I have a table, with 4 records in it. 2 records have the "date" field for 01/11/2005, and 2 dates are for 02/11/2005.
Is there a way to just query the "current" date? Weather it be in a pop-up box once the query is selected, asking for the date? or is there a restricted word within access2003 that lets you find the current date?
I would essentially like a command button within a form to allow the user hit teh button, and it will automatically find those records within the DB with the current date(set to system clock) and export it to excel(i have this portion, the exporting to excel complete)
Any help would be greatly appreciated.
Connor
View 7 Replies
View Related
Jul 21, 2006
ok ihopefully i wont make this too complicated to under stand.
i ahev a form which will populate a form from a query. the query is pulling info out a table included int hti sinfo is the date when it was entered. in the criterai of the query i hve added this code
Between [Forms]![Boxes Reviewed]![txtFromDate1] And [Forms]![Boxes Reviewed]![txtToDate1]
whcih means that the date must be between the two differnt dates that must be entered on the form.
the problem is, is that a value can be entered more that once with different dates. i woul dwant the report just to shouw the most recent date. i considered doing a query which requested all max dates from the table and then having a query calling forma query. but i do not know enough on how to get just the most recent date.
so if i wrote a query which held
box num, shelf num, resp_person, and date. what would i have to put int he criteria to only bring back the entery witht he most recent date.
sorry if i have rambled and look forward to your help
View 1 Replies
View Related
