Tables :: Finding Closest Value In A Table?

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 Replies


ADVERTISEMENT

Tables :: Finding Specific Record In A Linked Table

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

Queries :: Find Date In Table 1 Closest To Another Date In Table 2?

Mar 17, 2015

I have a table, tblVisits, holding patient's pre and post surgery visits:

Code:

PatientID VisitDate
1 1/5/12
1 3/10/12
1 9/1/13
2 ...

And another table holding patient's surgeries (each patient will have only one surgery)

Code:
PatientID SurgeryDate
1 4/1/12
2 ...

I need to compare these two tables and create a variable that indicates which pre-surgery visit date (i.e., VisitDate < SurgeryDate) is closest to the surgery date. In the above example, it would return:

Code:
PatientID VisitDate ClosestToSurgery
1 1/5/12
1 3/10/12 Yes
1 9/1/13
2 ...

I've tried various MIN and MAX approaches and can't seem to get it right.

View 2 Replies View Related

Closest Match Criteria

Dec 3, 2007

I need help writing an SQL statement in Access 2007 to select the closest date/time. I have Spot Time (the date/time commercials ran) and Call Time (the date/time we received calls).

I need to match these two fields so that I can tell which calls came in within 5 minutes before the Spot Time and which came in 15 minutes after.

Between DateAdd("n",-5,[SpotTime]) AND DateAdd("n",15,[SpotTime])

This gives me results within that 20 minute time range, but does not match each call up to the closest time.

For example,

SpotTime--------------- CallTime
6/30/2007 10:45 AM 6/30/2007 10:55 AM
6/30/2007 10:50 AM 6/30/2007 10:55 AM
6/30/2007 10:55 AM 6/30/2007 10:55 AM

In this example I would need the three Call Times to correspond with the 10:55 AM spot because the Call Time occurs very close to the Spot Time. I am unclear how to proceed.

View 3 Replies View Related

Select Closest Match Query

May 18, 2005

Greetings all!
I am working on something and it has me stumped. Basically I have a list of Eastings and Northings and I am trying to find the closest postcode centroid based on the PAF file (for those that do not know of the PAF file, it contains a list of postcodes and the easting and northing of the centroid).
The best way that I can see of doing this is:
For each record I am trying to match, calculate the distance from the Easting,Northing to every easting,northing in the PAF.
Select Min(Straight Line Distance) from the results.

However I do not know how to do this without matching each record that I am looking at individually! Can anyone give me an idea of how I can put this query together?
I am using MS Access :(

Thanks!
~Shiv

View 1 Replies View Related

Making A Query That Can Extract 2 Closest Column Values

Dec 20, 2006

Hello I have a query in MSACCESS that looks like this

N| Q | A | B | C | D | E | F |
X|..4.|..1.|..6.|..7.|..9.|...|...|

now my problem is that I want to have a query select the 2 closes values to Q from columns either A B C or D and put them into columns E and F

I am just so stuck here, if anyone can help or give me a steer in a right direction i would greatly appreicate it!

View 1 Replies View Related

Finding Unique Records Between 2 Tables

May 12, 2006

Dear all,

Many thanks for taking the time to read this.

I have a slight problem which seems like it should be easy to solve but I have no idea how to do it.

I have 2 tables (table1 and table2 for instance) Table2 contains more data than table1 but should contain everything in table1 as well as more.

How is it possible to return just records that are in table2 that are not in table1

Eg

Table1 contains:

IDab
112
222
322
422
511
611
711

Table2 contains:

IDab
112
222
322
422
511
611
711
833

The query should return just the line:
833
as this is unique.

thanks again

View 6 Replies View Related

Finding Duplicates In Two Tables- MS Access 2000

Dec 13, 2007

Hello,

I am just starting Access...
Or at least trying to learn it on a small project, and I need a jumpstart, if someone woild be so kind and help me out.

I need to organize my e-mail sendings.

In one table (Table Sent) I have e-mail addresses where I have already sent messages.
In an other (Table New) I have some other e-mail addresses, I am preparing to send out.

To make sure to not send mail to those who already got one, I need to compare the two tables, and delete from the Table New those addresses which are already present in Table Sent.

I guess that would be a 2-3 line SQL script...
All of my attempts so far produced no results

Any guidance would be great!

Thanks

Attila

View 14 Replies View Related

Queries :: Finding Distinct Records In Table A That Are In Table B

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

Finding Info In Another Table

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

Finding Records Within One Table - Msg Box

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

Finding Table In Form?

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

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 1 Replies View Related

Queries :: Finding Names That Appear More Than Once In A Table?

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

Finding Last Record In A Table / Form

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

Finding Unique Records In A Table

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

Finding Duplicate Values From The Table Having No Primary Key

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

Finding Digits In Text And Link Them With Value Of Another Table?

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

Finding The Source Of A Linked Table To A Front End Database

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

Queries :: Calls Table - Finding Last Contact Date

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

Problems With A Linked Table -not Finding Record In Asp Login Page

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

Modules & VBA :: Finding Duplicates In A Table And Recording Values In That Record

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

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

I have two tables of data, each relating to three business branches (branches A, B and C).

Table 1 shows the expenditure of each branch (by fuel, premises and wages).

Table 2 shows a number of units for each branch (mileage, floorspace and sales).

What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.

View 6 Replies View Related

Tables :: Joined Two Tables - Unable To Filter / Lookup On Second Table?

Feb 18, 2013

I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.

View 14 Replies View Related

Tables :: Populating Several New Tables With The Contents Of Original Table

Mar 29, 2013

I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved