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 Replies


ADVERTISEMENT

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

Finding Inexact Matches With WHERE

Sep 14, 2006

I have a combo box that is filtering a form. The combo box contains employee's last name. However, the field that it is looking up can contain more than one name (e.g., possibilities include just Smith, Smith & Johnson, Smith & Jones & Johnson, etc.).

Thus, when I click on the combo box, I want all records containing "Smith" to show up. However, I have only been able to bring up exact matches. Here is my code. Thanks in advance.


Dim strSQL As String
Dim strSQLSF As String

Status_CB = Null

strSQL = "SELECT DISTINCT Pipeline.Status FROM Pipeline"
strSQL = strSQL & " WHERE Pipeline.[Lead_Person(s)] = '" & S_MD_CB & "'"
strSQL = strSQL & " ORDER BY Pipeline.Status;"

Status_CB.RowSource = strSQL

strSQLSF = "SELECT * FROM Pipeline "
strSQLSF = strSQLSF & "WHERE Pipeline.[Lead_Person(s)] = '" & S_MD_CB & "'"

Me!Data_Entry_SF.LinkChildFields = "[Lead_Person(s)]"
Me!Data_Entry_SF.LinkMasterFields = "[Lead_Person(s)]"
Me.RecordSource = strSQLSF
Me.Requery

View 3 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

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

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

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

General :: Match Equivalent In VBA?

Oct 16, 2013

I have got a combo box with a product code in it, and depending on which code is selected I want the data to come from one of two different tables. Is there any way I can do something like a match formula in Excel to see if the stock code is contained in a table?

View 3 Replies View Related

Match Max Value In A Row Of Fields With Row Header

Feb 25, 2014

I am trying to setup a table that tracks the progress of an item from a cradle to grave process with 26 steps in between. I would like a calculated status column that is calculated by finding the max value (Date) in a row and returning the "header" that is associated with the max value.

View 11 Replies View Related

Number Of Records Doesn't Match

May 3, 2005

(this might be more appropriate in Forms; apologies if I'm putting it in the wrong place.)

My company runs a home repair program and we keep track of our clients in a database I made a few months ago. We use almost exclusively a form drawing from 3 tables. We just hit 800 records (according to the form) and I was checking some things, and found that there are 807 records in each of our 3 tables.

I did a preliminary check, and the primary keys match as best I can tell, so I'm trying to figure out how 7 records can be missing from our form.

Any ideas?

Thanks in advance.

will

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

Match Field Based On Table

Jan 4, 2006

Hello all,

Happy New Year.

Hope you ca help with the following.

I have 2 tables in access. One is a table with the us state abbreviations.

I have another table, one of the fields is an address field, e.g.

SOQUEL CA 95073
SOUTHAVEN, WA 98671
SOUTHBURY, CT 06488
SPENCER IA 51301
SPOKANE, WA 99201
SPRINGFE VA 22150

I would like to create a query, joining these two tables together so that the query can give me the 2 state abbreviation e.g.:

Address field/ Abbreviation field
SOQUEL, CA 95073/ CA
SOUTHAVEN, WA 98671/ WA
SOUTHBURY, CT 06488/ CT
SPENCER, IA 51301/ IA
SPOKANE, WA 99201/ WA
SPRINGFE, VA 22150/ VA

Is this possible?

Thanks for your help,

View 1 Replies View Related

Query - Access - Match Names

Mar 16, 2006

Quick question… I am trying to accomplish something that I believe it is fairly simple. At least in Excel, it is. I have two tables with names. I am trying to create a formula that will compare name in table 1 with a name in table 2. If the names match (exact matches only) it will let me know. Something like =IF (NAME1=NAME2,”TRUE”,”FALSE”) in Excel. I have trying using Iif, but it returns that I cannot divide by zero. I have linked the tables, so if I query NAME 1 and NAME 2, it will only display names on table 1 that are found on table 2.
Any help would be appreciated.
Thanks,

View 4 Replies View Related

Query To Display Data That Does Not Match

Jun 12, 2006

Hi,
I have two tables of data, one is a customer information (membersdata) table and the other is information recived from a bank (bankdata). Each customer has a 'bank description' field in its membersdata table and the bankdata table also has field 'bank description'.
The query I have at the moment gives me back the data that both tables have a matching 'bank description'. The query I want is one that will give me the data from the bankdata table that does not exist in the membersdata table.
So simply put the query I want is the opposite of the one I made with the wizard.

I hope that makes sense?

Any help would be fantastic!

Cheers
Phill

View 9 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

How To Match Patterns Typed Into A Textbox

Oct 3, 2006

I have a form based on a table called checkwriter. Right now it is comprised of multiple dropdowns that work great but now I need a textbox where the user can type in a few characters and have the form display the results which match the first few characters from the field "memo" from the checkwriter table. Im so a newbie to this and not sure whether I need 2 write a seperate query for this or write a simple procedure for that text box. Any help to get me started would be greatly appreciated.

View 4 Replies View Related

Need To Match Data In All Fields In 2 Tables

Apr 14, 2008

Hi,

I need to match the data in all the fields between 2 tables.

eg.
Field 1 (Table 1) = Field 2 (Table 2)
Field 2 (Table 1) = Field 2 (Table 2)
so on......

Using the wizard, I'm only able to match 1 field at a time and this is very time consuming as I have 45 fields to match. Any advise? Can this be done via SQL?

View 2 Replies View Related

Update Record If Unique Match

May 11, 2006

This is probably dead simple, but I am brain dead today.

I have two tables:
Requests with fields (ID, Cust, Amount, Ref, Date)

and

Actuals with fiels (ID, Cust, Amount, Ref, Date)

Now,
if Requests.Ref is null, then update Requests.Ref = Actuals.Ref and Requests.Date = Actuals.Date if and only if
there is only one record in Requests and only one record in Actuals where Requests.Cust = Actuals.Cust and Requests.Amount = Actuals.Amount.

Currently, I am just doing an inner join between the two tables, but if there are two requests with a given cust/Amount, but only one Actual, then both Requests will get the same Actual Ref and Date.

So how do I structure this SQL?

Thanks,
David

View 3 Replies View Related

Performing A Match Query Between Two Tables

Oct 25, 2006

Hi,

I recently got a new job and am trying to learn access for it. I have two tables. Both of them have client id numbers. They are both supposed to have the same client id numbers. However, Table 1 has more client id numbers than Table 2. I want to do a match query that selects the client id numbers in Table 1 that do not have a corresponding match in Table 2. How would I go about doing this?

Also, I want to do a simple select query where I select the client id numbers in Table 1 whose first two numbers are "88." How would I do this in the query or SQL form.

Do you have any recommendations about the best way to learn Access for practical applications like this? I'll also need to get good at making Forms which seem fairly complicated.

Thank you for your time.

Rajiv

View 1 Replies View Related

Queries :: Two Fields Don't Match Query

Jan 24, 2015

Is there a way to have a query return only records where the text in two fields doesn't match?

View 1 Replies View Related







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