Comparing 2 Fields--Looking For Differences

Jun 1, 2005

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

View Replies


ADVERTISEMENT

Queries :: Comparing Data Differences Between Two Identical Access Tables?

Dec 10, 2013

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 Related

Modules & VBA :: Show Pop Up Message Depending On 2 Fields Value Differences

Oct 7, 2014

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

View 3 Replies View Related

Comparing Two Fields.

Dec 30, 2007

Hello,

I am a Beginner in MS Access and SQL and I need a query to compare two fields.

I am having two tables; T1 and T2, and I want to compare their
fields TF1 and TF2, and display the non matching fields in new
field.

Example:

Table T1
TF1

Location Louisville
Number 555345
Name Smith


Table T2
TF1

Location Louisville
Number 553450
Name Smythe


Thanks in Advance.:)

View 1 Replies View Related

Comparing Fields In Access

Apr 28, 2006

I have a database with a pre-surgery field and a post-surgery field. The use must enter the current procedure in the pre-field and then the actual surgery in the post field. I need to check to see if the fields match verbatim. The field is a memo field.

The users know how to copy and paste from the pre to the post fields. 90% of the time it is the same but not always.

Can anyone provide some help?

Thanks,
Deborah

View 2 Replies View Related

Comparing Two Fields In A Query?

Jun 18, 2007

I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?

Any help would be greatly appreciated.

View 14 Replies View Related

IIF Statement Comparing Two Fields?

Sep 28, 2012

The two tables are joined together by a primary and foreign key in the query, . I have not added the actual tables below , as data is senitive - i'm using an example. This join is made using the graphic relationship between the tables.

Table_1 with below column
Mode
6
6
6
6
6
4
4

Primary KEY
0001
0002
0003

[code]....

Initially the pseudo code i'm trying to get to work is;

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") ,

However this calculated field returns incorrect results, i.e. the query returns MATCH for rows that do not match , e.g. mode=6 and type=GL , Previously , when it was returning #Error when trying to match mode=6 and type=GL when it should ideally return "NO_MATCH", which is why I added Isnumeric.

Once I have sorted this out, I would like to achieve this as the calculated field

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH" or if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH

how I can do this?

View 6 Replies View Related

Comparing Fields Of Two Tables And Output To A Third

Jul 20, 2005

It has been a while since I last used Access, recently I've used mySQL and PHP.

First a little description of what I'm trying to accomplish:
I have three tables...

tblImport - has the fields: TestID(PK), i001, i002, i003, i004 ... i025
Note: i00# field contains the multiple choice answer (i.e. 1,2,3,4,5) as imported from a CSV file.

tblStudentAsr - has the fields TestID(PK), StudentID(PK), 001, 002, 003 ... 025.
Note: 00# containes the multiple choice answer (i.e. 1,2,3,4,5) as enterd by the student.

tblResult - TestID(PK), StudentID(PK), a001,a002, a003 ... a025.
Note: the fields a00# have the datatype set to "yes/no"

What I'm trying to do is compare the answers in tblImport to the answers in tblStudentAsr then output the result to tblResult .

Here is some dirty pseudocode:
if i001 = 001 then
INSERT 1 INTO tblResult
else
INSERT 0 INTO tblResult


The above example gets a little repetitive since I would have to do that for each question.
Is it possible to put the answer fields of each table into a recordset then compare them?
(I have heard that using rs's can be a little slow. Although there is only a max of 25 questions the number of students can be quite large)

Alternativly, can I accomplish this using only SQL statements?

What would be the best way to attack this?

Any suggestions (or alternate suggestions) would be greatly appreciated.

Thanks,

salmonman

View 6 Replies View Related

Comparing Two Fields In One Criteria Field

Aug 9, 2005

Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"

Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.

Is there a beter way to do this?

Thanks!
P.S. Sorry for my poor english. It's not my native language.

View 8 Replies View Related

Queries :: Comparing 2 Fields Query?

Mar 11, 2014

Trying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?

SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");

View 1 Replies View Related

General :: If Statement Comparing To Fields

Sep 27, 2012

query is refering to 2 tables

Table_1 with below column
Mode
6
6
6
6
6
4
4

Table_2 with below column

Type
06
GL
PL
04
16

Both tables are joined, and the pseudo code i'm trying to get to work is;

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

I have tried below, but doesn't work.

Expr1: IIf(IsNumeric([TABLE_2]![TYPE])="06" And IsNumeric([TABLE_1]![MODE])=6,"MATCH","NO_MATCH") , returns incorrect results.

Ideally would like this

if table_1.mode = 6 and table_2.type = 06 , then "MATCH" , else "NO_MATCH"

or

if table_1.mode = 4 and table_2.type = 04 , then "MATCH" , else "NO_MATCH

View 6 Replies View Related

Forms :: Comparing Combo Box Value In Fields

Jun 26, 2013

I have form where some or more field exist. I want to compare two field as :

1. cboBatchID As Combo box
2. txtBillNum As Text Box

Private Sub cboBatchID_AfterUpdate()
If Me.cboBatchID.Column(4) <= 0 Then
Me.txtBillNum = 1
Else
Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1
End If
End Sub

I mean, if cboBatchID.Column(4) <=0 then txtBillNum start from 1 automatically or cboBatchID.Column(4) >=0 then txtBillNum = cboBatchID.Column(4)+1

I'm already trying with the code above. But does not work. Generate run time error.

View 14 Replies View Related

Comparing Two Fields With Identical Input Masks

Aug 31, 2007

I have a form control with the input mask on it for a phone number. I am using an append query to append the information entered in the form to a table. I need to pull two fields from a linked table from another database. What I need to do is be able to compare the entered phone number in the form with the phone number from the table. The problem I am running into is that it won't match the phone numbers. The same mask is used in the table I am pulling from. Why can I not just put =[Table].[Phone_No] in the Criteria of the field where the number from the form is placed so that I can get the right record from the linked table?

View 4 Replies View Related

Modules & VBA :: Comparing Two Multi Valued Fields

Jun 22, 2013

I have two fields in a table that have multiple values. Example:

Field A:

CT, CA, PA

Field B:

CT, CA

I want to compare the two fields and indicate that there is a match because in this example CT and CA are in both fields. I would like to create a function. I'm not sure if I would have to use something with like or create a loop.

View 5 Replies View Related

IIF Statement - Calculate Value Based On Comparing Current Date To Dates In Fields

Jun 27, 2013

In my table I have the following 4 fields with the associated date field:

Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015

I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?

=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))

View 5 Replies View Related

Differences In Two Tables

Jan 31, 2005

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
:)

View 2 Replies View Related

Differences Between Two Tables

Feb 20, 2007

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

Differences Between UK VAT & US Tax Systems And DB Design

Dec 18, 2006

I'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 Related

Show Differences Between Tables

Oct 9, 2005

G'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

View 4 Replies View Related

Why Those Permission Differences Btw Access Files ??

Sep 1, 2005

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

View 3 Replies View Related

Identify Differences Between Table And Spreadsheet

Dec 19, 2006

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.

View 3 Replies View Related

Reports :: Show Differences Between Two Records

Aug 2, 2013

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'

View 7 Replies View Related

Queries :: Formula Identify Differences

May 22, 2014

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".

View 3 Replies View Related

Query Differences In Access 2000 & 2003 Using Vba

Feb 28, 2006

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

View 1 Replies View Related

General :: Automatic Calculation Of Date Differences

Sep 13, 2012

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.

View 4 Replies View Related







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