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
Hi everybody
It is my first post here :)
I have a simple (I hope :) ) question.
I have two tables in my database.
Tables are simple and the structure is the same
Tables contain some different and some the same records
I want to create a new table (or Raport maybe) which contains differences (different records which are not in both tables)
How can it be done in the easy way?
Sorry for such stupid question but I have tried to find this forum for similar issue with the easy solution but no luck.
Thx for the reading
:)
I have a Table named WorkUnit1234567-01-01-07. We'll say the info on this table is from 1 January. I will then have a Table named WorkUnit1234567-02-01-07. We'll say the info on this table is from 1 February. Both of these tables will have the same fields. What I would like to do is compare Table WorkUnit1234567-01-01-07 with WorkUnit1234567-02-01-07 and only show the differences between the first table and the last table. Any ides on how to accomplish this?
View 4 Replies View RelatedI'm building a number of systems that I'm trying to make compatible in the USA and UK I have sorted all The Date And Currency Formats so the system Can Now work Completely With Both.But At present The Systems Designed To Work With The UK VAT System Would anybody be able to give me some guidance with what I would need to make the tax system Correct for the USA As Well As The UK.Many thanks in advanceMick
View 14 Replies View RelatedG'day,
I am using ACCESS to compare roles for users on our SAP system before and after a QAS refresh. I have two tables, one listing roles before and one listing roles after the refresh, how do I have to relate these two tables and what type of query should I run to present me with a list of roles that appear in one table and not the other, since I have two tables there may be roles in table a that arent in table b, as well as roles in table b that arent in table a. I need to see both these. I also dont want to see any roles that appear in both tables.
any help?
Russ
Hello,
I have 2 fields that are supposed to have the same information for each record. I got the information from different sources, which is the reason for having 2 fields. I wanted to make sure that the information was accurate. Turns out, that some of it isn't accurate--all the fields don't match.
Here's a little example of what I have:
|___TITLE___|___ISSN 1___|___ISSN 2___|
|__TITLE 1__|__12345678__|__12345678__|
|__Title 2__|__22224444__|__23224322__|
|__Title 3__|__98765432__|__98765432__|
As you can see, the ISSN numbers for Title 1 and 3 match. There's no problem there. I want a query that would list all those like Title 2, where the ISSN numbers don't match. Then I'd have a list of all of the problem Titles, and I could look up the real ISSN numbers.
Thanks
-Siena
Hi accessors,
I have 1 permissions related Q.
If I create msaccess db by calling the app through its icon, i get a db with following permission items :
Administrators(compNameAdministrators)
System
user(compNameuser)
but if I create it through rightclick> new> ms_office_access_application, I get a db with:
Administrators(compNameAdministrators)
Everyone
System
user(compNameuser)
Users(compNameUsers)
VS Developers(compNameVS Developers)
Why /the hell/ those differences btw the same kind of files ? :mad:
greets
Here is the problem
I have a tblOrders that has 1800 records with 31 fields primary key tblOrder which is a number.
Periodically users export data from SAP into a spreadsheet, which are changes to the original data but may not contain the exact same fields as the master tblOrders. The data in this spread sheet identifies changes and may contain aprox half of the fields that are contained in the master tblOrders. The field names however (in the spreadsheet) will be of the same type as in tblOrders.
What I would like to do is identify differences between the tblOrders records and the records in the spreadsheet, and then update the tblOrders to show the changes.
how would you proceed? Maybe create a new table by importing the excel spreadsheet into access and then somehow querying the differences and updating the master. If anyone has done anything like this b4 please could you update me...
Cheers in advance.
I'm currently working on a report to show the differences between two records (in this case, Documents). Each Document contains a number of paragraphs which are separate objects within my database. The purpose of this is to narrow down which paragraphs require reading (i.e. remove/highlight which are duplicates).
I've set up the report to show only the paragraphs from two selected documents (I don't think there is need for more than two, but how to select two or more documents for the query), and currently I have set the 'Hide duplicates' which kind of works but not the result I was hoping for - it only hides the related field and leaves the original in tact.
I am looking for a method that hides both duplicate records (not just the duplicated fields) if possible, failing that a method that will conditionally format the remaining duplicate or all duplicates.
Looks like the Hide Duplicates hasn't quite worked either - I have two fields which are clearly different and it has hidden the longer one. Likewise another later paragraph is not duplicated and it has hidden another which isn't duplicated. If it makes any difference the field is of type 'Memo'
Basically I would like to populate column three "OUTCOME" according to the following logic:
if train A10 has multiple destinations (we see this in the second column) then in outcome I woudl like to have "multiple destinations" if the destination is only one (in case of trains A15 and A16) , then I would like to have that city in column 3 "OUTCOME".
Hi,
I'm just wondering if there is any differences in writing queries in vba in different versions of access. i'm developing a search function in access 2000, but it doesn't seem to be working. however, the same code in access 2003 works fine.
Here's an extract of the code when the search button is clicked (I'm getting table information, attribute/field information and condition they're specifying from the user. The result is then displayed in a subform):
Private Sub cmdFind_Click()
Dim searchSQL As String
Me.cmbTable.SetFocus
searchSQL = "select * from ENGINEERS INNER JOIN TEL_CABLING ON ENGINEERS.EngID = TEL_CABLING.EngID where " & Me.cmbTable.Text & "."
Me.cmbAttribute.SetFocus
searchSQL = searchSQL & Me.cmbAttribute.Text
Me.txtCondition.SetFocus
searchSQL = searchSQL & Me.txtCondition.Text
Me.subfrmTest.Form.RecordSource = searchSQL
Me.subfrmTest.Form.Requery
End Sub
The part in red is the part that is highlighted whenever i run it in access 2000. The error message is: " You entered an experession that has an invalid reference to the property RecordSource "
Can anyone tell my why it's like that and how i can fix it to work for access 2000?
Thanks I've also enclosed a picture of the form, so you can get a feel of what it looks like.
Kathy
The project I'm working on is an action list with the following tables;
itemnumber - autonumber PK
originator - text
dateentered - date/time
actiondescription - text
assignedto - text
targetdate - date/time
forecastdate - date/time
actual date - date/time
remarks - text
due - text (not sure if this will be correct)
Basically, when a new task is entered, all info will be manually filled in except last 3 fields. targetdate needs to be locked after entry, and forecastdate initially set to targetdate.
"due" should show how many days between dateentered and forecast date, and preferably update everytime the DB is opened. IF the current date is beyond the forecast date, "due" should read "OVERDUE".
Changing forecastdate would obviously reset the "due" field to show how many days left to complete. If a date is entered into actualdate, then "due" should show COMPLETE.
Reports will be generated based on what is coming due, what is over due, and not show what is already complete.
I have 2 fields on my form service interval combo box and vehicle mileage text box I m trying to create a pop up message on there values
if service interval is 12,000 and the mileage entered in the vehicle mileage is over 12,000 then show pop up message.
this works but I'm sure its wrong don't no why I need the -1 anyways here's what I have
Code:
If Me.Vehicle_Mileage.Value > Me.Cboserviceinterval.Column(1) - 1 Then
msgbox "test"
end if
Now my problem what I can't get it to work . I still want the pop up message, if say the service interval is 12,000 and mileage entered in the vehicle mileage is 1,000 miles below the service interval I still want the same pop message but if 2,000 or more below service interval then no pop up message .
I have tried a number of sequences with no avail....
I have two identical Access tables, for example tblDataYesterday and tblDataToday. The data from tblDataToday has updated data changes in it that are different from tblDataYesterday. I have a maximum of 100 fields to review. I want to compare the two tables and display in a query the differences between the data from both tables.
View 1 Replies View RelatedI am trying to count how many of the "same" and "differences", as well as calculate the percentages of the number of "same" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.
Also, I need to categorize these diagnosis by each type of diagnosis.
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....
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
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
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
Im trying to work something out on Access at the moment to score some brownie points with my boss and am hoping someone will be able to help me. Im relatively confident about using access but when it comes to tricky queries i get a bit confused. Basically my situation is that i need to do some matching. Using a PO number and a unique ID and updating a column in one of my files with the unique ID. I have done it this way so far....
Linked the the two files together and matched them on the PO number and then updated the field with the ID where they matched. This seems to work ok, but the problem is that about 10 of the PO numbers contain between 2 to 6 different ID's. Therefor how can i make it so that if there is a PO number 6 times in the file it will match with all the ID's. I dont think that this is to hard to do, but each line has a different cost and they have to be matched to the write one. The problem with this is the cost is normally different as it flucuates with the exchange rate. I can only think that the best way to do this is to use a function that looks at the cost and if its say $20.00 more or less then assume its that. But have no idea how to implement it.
Does that make sense? Is it likely that its going to be easy to do. Im relatively ok with SQL if it would be easier to use that.
If anyone has any suggestions it would be greatly appreciated...
Thanks :)
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:
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 RelatedI 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.
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
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
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