Access :: Matching Query

Queries :: Query Based On Table Matching Another Query?

Jan 13, 2014

Been working on this for a while now and can't get it to work how I'd like after trying a few things.

I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.

I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:

Quote:

SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration,
DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration,
DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration,

[Code] ....

Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.

View 14 Replies View Related

Need Help With A Query For Finding Matching Records

Aug 5, 2005

I have two tables Table 1 and Table 2. Table 1 has 6 fields 175 records and Table 2 has 4 fields and 330 records. The first three fields in both the tables are the same. I need to find all records in the first table that have a matching record in the second table based on those first three fields. How can I accomplish this? Any help would be greatly appreciated.

Thanks

View 3 Replies View Related

Queries :: Query Data Not Matching?

Apr 19, 2013

I have a table that holds course information for students. I have a parameter query linked to this prompting the user to enter a course code thus displaying all students who have that course. However, I would like to do the opposite i.e prompt the user to enter a course code and then the result to be a list of students who do not have that course.

View 8 Replies View Related

Need Count Query To Output 0 (zero) When There Are No Matching Records

May 21, 2007

I have a form where a user reviews information input by another user, once they have done this they sign it off by selecting their name from a drop down list. On this form there is a scrolling message which tells the user how many un-signed entries there are. This works fine until there are 0 (zero) entries to be signed off.

The scrolling message is linked to a count query which basically counts any records that does not have a name entered in the required field. I have done this with ' Not Like "*" '

Like I say it works when there is one or more entries to count, but as soon as there are none the query does not output a zero it is just blank. This is the problem.

I need some sort of statement to say if there are no matching records please display a zero.

Any help would be much appreciated!

View 1 Replies View Related

Queries :: Matching Users And Status In Query

Jun 19, 2015

I have a database where there are 3 tables. table "d_cases", table "d_user" and table "d_status"

The table "d_cases" has the users and statuses only with a number. Peter has ID 2, John ID 1. There is status ID 1 that is New, ID 2 Started and so on.

How can I run a query that will show me the names and status instead of the numbers?

I have attached the database (2003 format) ....

View 2 Replies View Related

Queries :: Matching Year Field In 1 Query

Feb 5, 2015

Using Access 07

This is my query

SELECT QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation, Sum(QryReserveTotals.SumOfAddtlAllocationAmt) AS SumOfSumOfAddtlAllocationAmt
FROM QrySiteAlloc LEFT JOIN QryReserveTotals ON QrySiteAlloc.Site = QryReserveTotals.Site
GROUP BY QrySiteAlloc.Site, QrySiteAlloc.AllocYr, QrySiteAlloc.SiteAllocation;

In the QryReserveTotals is a datereceived field.

I need the year of the date received field to match the QrySiteAlloc.AllocYr that will be selected by a parameter..

what would be the criteria to achieve this?

View 3 Replies View Related

Queries :: Query That Returns No-matching Records

Jul 15, 2013

Suppose I have two tables with fields as follows:

Assigned Courses (Courses that employees should do)Employee number
Assigned Course Name

Fulfilled Course (Courses that employees have done)Employee number
Fulfilled Course

Now I need to do a query that tells me which employees haven't done. Is there anyway to do that that is not an exclude query?

View 4 Replies View Related

Reports :: Date Range From Query Not Matching

Jun 19, 2013

When I run my query that has a MINDATE and MAXDATE column, i can see the full range of call dates. The earliest date is 06/04/2013 and the latest date is 06/19/2013.

When I run a report with text fields of MINDATE and MAXDATE off that query the range shows as 06/04/2013 to 06/04/2013...

What I am trying to accomplish is for the report to express the range of dates. the report asks the user to enter a beginning date and an ending date for their search and I want them to know the actual range. The data appears to be accurate but the dates don't work.

View 1 Replies View Related

Queries :: Create A Query For Non-matching Records

Jul 24, 2014

I am trying to create a query in Access 2010 for records that don't match based on the following criteria. I have two tables with identical ID's and I need to do a comparison on the amount field between both tables and only return the records from one table displaying all fields plus the amount fields that do not equal. I tried this in the wizard unsuccessfully.

View 13 Replies View Related

Queries :: Update Query Using Matching Criteria From 2 Tables

Oct 18, 2013

I have 2 tables that must be involved in this query. Both have the same fields; one is a temporary table created from an Excel import which will be deleted later, the other is the permanent table.

tblStoreProducts is the permanent table. It has a 2-field Unique index; there can only be 1 of any ProductKey for a StoreKey (there can be multiple ProductKeys within a StoreKey, and multiple StoreKeys for each product, but there can only be 1 entry for each unique combination of ProductKey/StoreKey).

tblImportToStoreProducts is the temporary table. It holds all of the ProductKeys for one StoreKey.

My query needs to identify any ProductKey in the tblStoreProducts that does not exist in the tblImportToStoreProducts for that StoreKey, and then it must set the MaxUnits field to 0.

Basically, the temporary table is this years' inventory for a particular store. If a product was in that store last year but it isn't there this year, the quantity must be set to 0 because the ultimate aim is reporting hazardous materials.

View 1 Replies View Related

Forms :: Open Query Based Report On Form With Matching Record

Nov 25, 2013

This is my data:

Table: "Facility Info"
Data in the table: "facility", "city", "date", etc.
Query: "Q Facility"
Report: "R Facility"
Form: "Main Form" is where the data is entered that goes into the "Facility Info" table.

In the "Main Form" there is a dropdown box where I can select the "facility".I would like to add a button to this form that opens my report "R Facility". But this report is a collection of all the facilities and I would like it to just report the ones for the facility that I selected from the dropdown box on my "Main Form".

View 9 Replies View Related

Fuzzy Matching

Mar 4, 2006

Hello All,

For those who are interested in Approximate String Matching or those who could use these algorithms; I have a complete suite of Approximate String Matching algorithms written in Visual Basic in an Access database.

In 2004 I decided to jump into the world of Fuzzy Matching with both feet.

As it is, I am working for a company that deals with names, addresses, etc. very intensely. It is a fair sized company that

uses Access on a grand scale. Since I am an Access programmer, I work in an Access gold mine!

I knew that if I could get a good handle on Fuzzy Matching, that when I hit the right person at the right time, the company could greatly benefit from my research on Fuzzy Matching. The right time and the right person are not here yet.

Nevertheless, since I have reaped much free source code and information from the Web, it is now time to return the favor.

I developed a package that is sort of a demo/tutorial on Approximate String Matching algorithms in Access that is very
robust in Fuzzy Matching. It would overtax the post in this forum for me to include it in a post.

To summarize, it works with the basic name - Last, First, and Middle. It has a user interface that allows a user to type in
what would be a good name and what would be a questionable name to resemble the good name. The weighted results of all the various algorithms can be chosen, or an individual algorithm can be chosen to display how closely the names match.

In addition, it has a table of 17,295 known good names with unique ID numbers as a reference table, and table of 1200
morphed names that are typical of names entered in a database with no input conventions. These morphed names have typos, transpositions, variations on maiden names, etc. 1200 good names were selected for alteration and the unique ID of each original good name was stored in the table with the altered names to determine the accuracy of the matching process.

The morphed names were compared to the known good names in a query with an approximate join using the suite of algorithms to determine match percentage. The altered names, the ID number of the original good name, the ID number of the name it matched to, and the match percentage were stored in a results table to determine the results of the matching run.

These tables were used to test and tweak the algorithms by comparing the morphed names with the known good names. The results of 1322 names were saved to a results table with match scores.

The matching process was executed in a query with an approximate join using the suite of algorithms.

The match results:

Total Approximate Matches: 1188
(Recall) Precision Pct: 99.00%

Total Unmatched Names: 12
Unmatched Pct: 1.00%

Total Other Matches: 134
Other Matches Pct: .77%

The tables are accessible in the database, so anyone can run their own tests. The interface is set up to accommodate this
as well.

The algorithms used: Dice coefficient as a threshold algorithm, Levenshtein Distance algorithm, Longest Common Subsequence, and the DoubleMetaphone. The names were passed to the algorithms by way of the bigram model.

I will email it to anyone who requests it.

It is in two platforms, Office 97 and Office 2000 as FuzzyMatching97.zip (692 KB) and FuzzyMatching2k.zip (721 KB).
The zip files include ApprxStrMatchingEngine97.pps or ApprxStrMatchingEngine2k.pps respectively, StrMatching97.mde or StrMatching2k.mde respectively, IEEESoundexV5.pdf, and VBAlgorithms.txt.

IEEESoundexV5.pdf is an abstract about Approximate Sting Matching that fired my curiosity about the subject, and pertains to the package.

VBAlgorithms.txt contains the entire suite of algorithms in Visual Basic extracted from the MDB modules.

The PowerPoint presentations describe the workings of the MDE and give a good overview of Fuzzy Matching.

To match is divine....

View 10 Replies View Related

Matching Records

Dec 22, 2004

A very elementary question - but I'd be grateful for an answer.

I have two tables (or perhaps two queries) each with a key field. If all is well, there should be complete correspondence between the two sets of records. That is, if there's a record with key 12345 in one table, there should also be a record with key 12345 in the other table.

I'm looking for the simplest way of checking whether or not this is the case, and, if it's not, detecting which records in one table are unmatched by any record in the other.

Will

View 1 Replies View Related

Matching Data Help!!!!

Sep 7, 2005

Hello,

I am using this query to get matching data:

SELECT NewMyEstartChild.yordob, NewMyEstartChild.firstname, NewMyEstartChild.surname, NewMyEstartChild.postcode
FROM NewMyEstartChild INNER JOIN For2003 ON (NewMyEstartChild.postcode = For2003.POSTCODE) AND (NewMyEstartChild.yordob = For2003.MyDOB);

but It's giving me more data so to minimise data I was thinking to add another filed. which is Firstname field. but some children's name are spell incorrectly.

How can I match data with first letter only ? I have tries following query but it doesn't work. Please help!!!!!!!!

SELECT NewMyEstartChild.yordob, NewMyEstartChild.firstname, NewMyEstartChild.surname, NewMyEstartChild.postcode
FROM NewMyEstartChild INNER JOIN For2003 ON (NewMyEstartChild.postcode = For2003.POSTCODE) AND (NewMyEstartChild.yordob = For2003.MyDOB) AND (NewMyEstartChild.Firstname = For2003.Firstname);

In last part of this query (NewMyEstartChild.Firstname = For2003.Firstname); How can I get the name of children whose firstname's letter is similar.

Thank you
Viral

View 3 Replies View Related

Matching Data

Nov 15, 2005

My problem is this:

I have a large table with about (8000+) records and a smaller table (2000+) records.
The large table has been exported from an ACT! database.
The smaller table has 4 fields that i need to add to the larger table, and then i need to import the updated records back into the Act! database.

I created a simple select query and matched the tables with the only 2 criteria that match the 2 tables, this was "Company" and "PostCode".

This should have been ok, but instead of updating 2000+ records it only updated 1000. The reason for this is because some of the company names weren�t an exact match, "company ltd" and "company limited" etc.

If i just linked "postcode" to "postcode" there are quite a few different companies that have the same postcode.

Is there a way of trying to match just the first 5 characters of the company name but leaving the "company" field intact?

Or is there a different way to go about this?

Thanks

Darren

View 3 Replies View Related

Matching Fields

Jun 19, 2007

Hi All,

Wondering if you can help I have a table called "example" which has field "a" "b" "c" "d"...

I then create another table called "importtable" with field "a" (imported in from Excel)...

I would then like to create a query which matches any and shows all the records which I imported in from Excel to my current table called "example" I looked at joining via "relationships" using a Select Query but it doesn't quite show what I'm after...

Should add I would like Field "a" in both tables to show but only if the number exists in the "importtable" if not then don't show..

Any tips :confused:

View 1 Replies View Related

Matching Problem...

Aug 6, 2007

We have two databases that I am tryin to match it one variable, we get it to to match and take from the first database and enter it in the second but I was wondering how I can get a report on the ones that didnt match. Im sorry if this is a simplistic problem but I am kind of new to Access and didnt know where else to turn to help.

View 2 Replies View Related

Matching Case

Oct 24, 2007

I have two tables that have fields set to a text so that the ClientID is their name.

When I query, my queries don't take into account the case. So "K Smith" is the same as "K SMITH" as "k sMIth"

I am trying to write an unmatched query between two tables based on this ClientID but it will turn up no unmatched because it is not taking into account the case.

Any suggestions on how to match the cases, othere then changing the table?

Thanks.

View 1 Replies View Related

Matching Name Differences

May 3, 2005

Hi. I am in the process of loading nursing license numbers into my database. The spreadsheet that I am importing from does not use the exact same names as the ones in my database, i.e. Smith, Deb in my database is Smith, Debora in the spreadsheet and I can't figure out the code or procedure to use to tell the database that these names are actually for the same record. Is it possible to do this and if so, how?

Thank you!

KellyJo

View 3 Replies View Related

Matching Dates

Feb 23, 2007

Hey all

I have a function to display the opening hours for the next 7 days for a client. I have also included the functionality whereby you can create a "special day" with non - standard opening times which are stored in the database.

I have a simple SQL to check to see if the current day is a special day:

Code:SELECT * FROM SpecialDays WHERE SpecialDate = #" & currentDate & "#"

I have run into this problem tho. There is a special date in the database for 03/01/2007 (UK date format 3rd January) but this is being retrieved for the 1st of March (01/03/2007)

therefore this query:
Code:SELECT * FROM SpecialDays WHERE SpecialDate = #01/03/2007#

retrieves the record with the date 03/01/2007

Any ideas?

Thanks

View 1 Replies View Related

Fuzzy Matching In Access - How To Use

Jan 7, 2007

Many have had questions on how to use this for their own purposes.

Link to the original post:
http://www.access-programmers.co.uk/forums/showthread.php?t=103279

Download at: http://www.kdkeys.net/forums/thread/6450.aspx

Here is how you can use it - I provide this example:

Tables and queries can be created in the MDE database.

Create a table with known good reference strings. I created this one - REF_LIST.

It has one field, REF_STRING (Text) with a length of 50, and indexed (No Duplicates). The field length can be set to a length that suits your requirements.

This is the content:

REF_STRING
Claw Hammer
Cold Chisel
Monkey Wrench
Nail Gun

Create another table with strings to match. I created this one - TEST_LIST.

It has one field, TEST_STRING (Text) with a length of 50, and indexed (Either No Duplicates or Duplicates Ok depending on the data). The field length can be set to a length that suits your requirements.

This is the content:

TEST_STRING
Claw Hamer
Claw Hammr
Clew Hammer
Clw Hammer
Cold Chisil
Cold Chisle
Cold Chissel
Cole Chisel
Monkey Wrnech
Monkie Wrench
Monky Rench
Nail Gn
Nail Gunn
Naill Gun
Nial Gun

Then create another table for the results. I created this one - RESULTS.

It has four fields, REF_STRING (same properties as in table REF_LIST), TEST_STRING (same properties as in table TEST_LIST), MATCH_VALU (Single, Fixed, 2 decimal places), and GOOD_MATCH (True/False).

This is the content from the results of the Match_Lists query.

REF_STRINGTEST_STRINGMATCH_VALUGOOD_MATCH
Nail GunNial Gun0.92No
Monkey WrenchMonky Rench0.94No
Monkey WrenchMonkie Wrench0.94No
Claw HammerClew Hammer0.94No
Cold ChiselCold Chisle0.94No
Cold ChiselCold Chisil0.94No
Cold ChiselCole Chisel0.94No
Nail GunNail Gn0.95No
Monkey WrenchMonkey Wrnech0.95No
Nail GunNail Gunn0.96No
Nail GunNaill Gun0.96No
Claw HammerClaw Hamer0.96No
Claw HammerClaw Hammr0.96No
Claw HammerClw Hammer0.96No
Cold ChiselCold Chissel0.97No

SQL from the Match_Lists query:

INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU )
SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, IsSimilar([REF_STRING],[TEST_STRING]) AS Expr1
FROM REF_LIST, TEST_LIST
WHERE (((IsSimilar([REF_STRING],[TEST_STRING]))>0.79));

Using this example you can populate the two tables, REF_LIST and TEST_LIST with strings that you need to compare and run the Match_Lists query.

The GOOD_MATCH field in the RESULTS table is for you or another human to determine if anything questionable is a good match for your purposes.
If it is found that any match with a value of at least .95 is a good match then an update query could be created to update the GOOD_MATCH field with true for all those with a value of >= .95.

Then a select query could be created to look at those matches that do not have a GOOD_MATCH to determine if they may be good matches.

Naturally the two tables may need a unique ID for the strings for better tracking and comparing.

If so, create them and have them appended to the RESULTS table as well in the Match_Lists query.

OpnSeason

View 4 Replies View Related

Matching Values From 2 Tables

May 4, 2007

I have three tables: tblProducts1, tblProducts2 and tblProductSales.tblProducts1Code CostABC 20BVC 35ABC 30tblProducts2Code CostABC 10BVC 55ABC 20tblProductSalesCode RevABC 70BVC 25ABC 20BVC 15DCC 33I want to produce a query that looks like this:Code Rev Cost ProfitABC 90 80 10BVC 40 90 -50DCC 33 0 33How can I do this?Thanks,Jon

View 1 Replies View Related

Two Tables - Data Matching

Oct 21, 2007

Hi,I have two tables with these set of data:Table1Filed1 Field21000 A1001 B1002 C1003 D1004 ETable2Filed1 Field21000 A1002 C1003 D1005 F1006 GI need to create 3 Tables with following out put.1. Data that are common to both Table1 & Table22. Data that are in Table1 but do no exist in Table23. Data that are in Table2 but do not exist in Table1 Can anyone help me to find the answer please.CheersBud

View 1 Replies View Related

Return All Matching Records

Aug 6, 2005

I have a database called LettersDatabase this databse holds all the letters that have been made including the path to the doc. I use SSN to ID the letters to customers on the Contacts Database.

Contacts database also uses the SSN to id the contacts

I have a form that creates new letters for customers in this form I have listbox that queries the LettersDatabase for all matching records based on the forms contact SSN to see how many letters have been made for that customer.

The problem is that my listbox only shows the first record matching that SSN but there are more records in that LettersDatabase with the same SSN that I need to have diplayed on the listbox as well.

I may be writing the query incorrectly.
Here what I have for the query on the listbox

Like[Forms]![LetterMaker]![txtSSN]

I try adding (&"*") to the end of the query but that does not help.

If anyone out there has the solution to this problem it would be greatly appreciated

Thank you

View 3 Replies View Related

Delete Matching Records

Feb 27, 2006

Hi can anyone please help me out. How can I delete records from one table, where matching in a second table?

View 1 Replies View Related