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 Replies

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

Finding A Record Where 4 Criteria Must Match

Aug 13, 2005

How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name, description, type and date.

Thanks
J

View 6 Replies View Related

Still Need Help Finding A Record Where 4 Criteria Must Match

Aug 19, 2005

--------------------------------------------------------------------------------

How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name (text), description (text), type (text) and date (date). I would always search on all four fields.

Thanks
J

View 2 Replies View Related

Finding Records That Match Multiple Criteria

Nov 2, 2005

Hi, I have a personell DB an I vant do be able to select only the persons from a specific "city" with a specific " genre" and a specific "skill" and also i want the query to "ignore" one of the criteria if I press Enter or input all.
I tried with "query design " and I don't seem to make it work for more than one condition.

Thanks

View 2 Replies View Related

Count Consecutive Records That Match Criteria

Jan 25, 2006

I want to place a control in a report footer that will return the highest number of consecutive weeks that a profit was earned. The database has fields named WeekNo and Net. The Net field contains positive values (profit) and negative values (losses). The WeekNo field is an integer from 1 to 52 designating the week number.

For example if the figure in the Net field is a positive number I want the control to count the number of consecutive weeks that a profit was earned. I know how to get the value of total weeks that a profit was earned; this is not what I am looking for. The control must return the highest number of CONSECUTIVE weeks that a profit was earned over the year.

Any help would be appreciated.

View 1 Replies View Related

Queries :: Return Results That Exactly Match Criteria

May 9, 2013

I have 3 main tables: tblEmployees, tblJobs, and tblProcedures. (See attachment for relationship diagram and additional supplemental tables).A job can have multiple procedures and an employee can have multiple procedures too.

I need to write a query such that when searching by a specific job I can see all of the employees who are qualified for that job. This is done by seeing which employees have the procedures that belong to a job. But here's the catch: since a job can have multiple procedures, if an employee only has some of the procedures I don't want that particular employee to return as a search result. The employee must have ALL the procedures that belong to the selected job.

So for instance if I have:

tblJobs
Job1
tblEmployees
Emloyee1
Employee2

[code]...

If I search by Job1, I want only Employee2 to return as a result, NOT Employee1.I am at a lost for how to construct the SQL for something like that.

View 4 Replies View Related

Modules & VBA :: Filter To Match Any Part Of Criteria

May 3, 2014

I have a text box ( Supplier_Name) on the main form i would like to filter subform by any part of entered charactor on the feild (suplier_Name)

Code:
Private Sub Find_Click()
If Not IsNull(Supplier_Name) Then
Me.Suppliers_Details.Form.Filter = "[Supplier_Name] = '" & Me.Supplier_Name & "'"
.FilterOn = True
Exit Sub
End If
End Sub

this code is work fine but i have to enter all the characters of long name , but i would like to only insert few chars.

View 2 Replies View Related

Query For Finding A Match From Multiple Value Criteria?

May 2, 2013

I am designing a database which keeps track of door access levels at a college, using Access 2010. A door access level is programmed to a key card, which grants access to a number of different doors throughout the college.

So, what I have is an "LevelID" and a "DoorID", where each LevelID has zero to many DoorIDs associated with it, as well as each DoorID has zero to many LevelIDs associated with it.

example table:

LevelID DoorID
1 1
1 2
1 5
1 6
etc..

All the data has been inputted into Access 2010 successfully, however I am having difficulty in developing one of the main functions of the database..

What I want to do is have a checklist of each DoorID displayed on a form, and when any combination of DoorIDs are checked, Access will search to see if a LevelID is associated with that combination. This information can tell me whether a new LevelID is needed to be created.

View 14 Replies View Related

Delete Records That Match Multiple Criteria From Second Table

Jan 23, 2012

I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.

What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.

View 5 Replies View Related

General :: Find Records That Match Criteria Of Multiple Fields

Mar 12, 2013

Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.

My query shows:

field1 field2
apple a
apple b
banana a
carrot a
carrot b
dog b
elephant b

I would like my query to now display a third field and group field :

field1 field3
apple both
banana a
carrot both
dog b
elephant b

View 1 Replies View Related

Queries :: Search Box Result - Add Up List Of Dates That Match A Criteria

Jun 13, 2013

I am trying to add up a list of dates that match a criteria... a search box result.

I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)

But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.

Code:
Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant

[Code] .....

I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?

View 5 Replies View Related

Modules & VBA :: Stop Export To Excel When No Records Match Criteria

Sep 22, 2013

I have a sales sytem in Access 2010.New customers have to be imported to MYOB daily. I have a query that finds new customers and appends them to a table for importing at a later stage. I export the records in that table if the field "imported" is set to false. The results are exported to Excel so they can be imported into MYOB after some additional data is added. If there are no records to append to the import table I want to stop the export from happening. I am new to VBA and don't know how to express that the select query "000 Append New Customers...." should only run if the append query has records. If there are no records found, I want to put a message saying "no records found". Here is the code I have behind the command button:

Private Sub PrepCustcmd_Click()
'Turn warnings off
DoCmd.SetWarnings False
DoCmd.OpenQuery "000 Append New Customers to MYOB Customers", acViewNormal, acEdit

[code]....

View 2 Replies View Related

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 6 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

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

No-Match Query

Apr 17, 2006

Probably an easy one...I have two tables, each with a number field say "Cat-ID" and "Ref-ID". The query I am looking to write will show the outstanding values. For example

Cat-ID: 100, 200, 300, 400, 500
Ref-ID: 100, 200, 500

Query Result: 300, 400

Any ideas on how to do this? I'm stumped.

View 2 Replies View Related

Inexact Match

Jul 26, 2006

Hi,

I have two tables of software names.
A count is needed of those names that are in both tables, as follows :

Table1.software name Count1 Table2.software name Count2

matching on software name.

The problem is that names match only partially, for examle:

Table 1 :

Acrobat Writer 6.0 std

Table 2:

Adobe Acrobat standard edition 6.0

This is the same software, but how do I make Access recognize that?
can't enter every parameter manually using wildcards, because
1) tables are too large to ask for each name separately
2) Sofware names matches on different parts, not only a first or second word.

Does anyone know how to do that ?

thanks

View 2 Replies View Related

Select First Match

Jan 24, 2007

hi

how can i make query that selects the first match of a criteria, or multiple criteria. lets say list of members first record in CA only then first record in OR only .... basically select first record from list of criteria.

View 6 Replies View Related

Query To Match

Apr 17, 2008

I have a table with two fields:
Field1 Field2
S11.1 111001
S13 130001
S11 110001
S13.1 120001
I need to query to find where Field2's first three digits does not match the numbers in field1. Giving the sample: 120001 would be listed in the results because it does not match the numbers in S13.1 130001(2nd record) does because if there is no "."; it defaults to zero.
I don't know to set up the criteria or what to ask so it would give me the results needed.
Can anyone help please.
Thanks

View 1 Replies View Related

I Need To Match 3 Fields For Duplicate

Sep 26, 2005

I am exporting an Oracle report to excel, and using an Access macro to import into a table.
The records contain some duplicate info that I do want to capture, but I don't want to import the same records. (I import the file on one day, and someone comes in and imports the same file later).
This would work if I could get the table to not duplicate if three of the fields are the same as an existing record. I tried using the index, but it reconizes any/all of the fields that duplicate.
Any help would be appreciated.

View 3 Replies View Related

Query For Inexact Match

Sep 19, 2005

I have converted an Old FileMaker Pro DB to Access.
There is a record for each of the 22,000 sheets of engineering project documents issued over a period of years.
One of the main fields we query by is the "Description" field.
If I have the following Criteria:
[Enter Document Description]
Then save and exucute the query Access seems to look for an exact match to the value I provide.
I need a query to return ANY record with the criteria value in it.
For example If I entered "Fire" I would like to return anything with "Fire" in the description:
B Building FIRE alarm Install
Building C FIRE sprinkler Demolition
A Building FIRE Exit Signage
Site FIRE Protection Main
etc.
How do I phrase a query to return records that contain the value I provide within the text string in the "Description" field?

I realize this is a dumb, rookie qiestion. I tried Access Help and
I tried a "search" in this Forum, I probably didn't do a good job describing my problem.

View 4 Replies View Related

Trying To Match Forecasts With Actuals

Oct 2, 2006

I am attaching a pic of my relationships page to show the table structure I am dealing with. What I need to do is match forecasted values with actuals based on the same date range, sales_num and unit.

The two tables that hold this data after downloading from our systems are

ForecastOE_T
ActualOE_T

The other tables are crossrefs for additional information and aggregation.

I have been able to query successfully each of these separately based on my desired date range, and successfully aggregate based on salesrep number and unit... but, I don't know how to properly write the query joining them.

Here are my issues:
Not all reps forecast all units.
Not all reps sell all units.

So, when I try to join these values (forecast and actual), the query I write gives me only those rep/unit combinations that BOTH a forecast was made and an actual appears (we don't get a download of zero activity). What I need is output that shows the rep/unit combination for all entries that have EITHER the forecast or the actual, with zeroes defaulted wherever appropriate. This has been done in excel in the past, but the file sizes are prohibitive now... and I'm trying to help out, but am stumped here.

ANY HELP IS GREATLY APPRECIATED!!

Tom

View 1 Replies View Related

Exact Match In Search

Jun 17, 2005

How can I get an exact match in a search. If I search for say C1 I get results showing everything with C1 i.e C1, C11, C12, C13 etc. I just want to show C1's
This shows my query at present.

Like [Forms]![Search]![BoxNo] & "*"

View 4 Replies View Related

Exact Match In Array

Apr 3, 2008

Hi all

I'm using an array with the Instr function. The array (or the Instr function) doesn't seem to be case sensitive... which is a pain because its picking words within words where the case doesn't match!! I'd like the vales in the array to match exactly when using the Instr function

Any idea how I can get around this??

Thanks

Damon

View 3 Replies View Related

SSN Format How To Match Records

Mar 29, 2007

Hello,

My database has two tables:
Master and History

Both tables have a SSN field with the following format:

000-00-0000XX (xx stand for two additional characters)

I have a search form with a textbox that I use to type criteria to open a form. The text box has the following SSN input mask: 000-00-0000CC;0;_
This allows me to return data even if I have a part of the SSN

I have now placed a button which verifies if the SSN I will type in the textbox already exists in table History. I am having problems with the SSN format as I have to type the whole SSN while I would like to type part of it (like the textbox input mask).

If Not IsNull(DLookup("[SSN]", "History", "[SSN] = '" & Me![txtbox] & "'")) Then

DoCmd.OpenForm ("popupssnsearch")

Is there a way I can do this?
THanks.

View 1 Replies View Related