Comparing Two Tables In Msaccess
Apr 12, 2007Is there any way to compare two tables and write out the records that don't match to a third table?
thank you.
ADVERTISEMENT
Comparing Tables
Aug 17, 2006I have two tables: -
1) Sales data
2) Claims data
I want to anaylse Premium (contained in the Sales Data Table) against Claims (contained in the Claims Data Table). The result I want to achieve is to show a loss ratio (Premium / Claims as a %). I need to show this on a per policy basis (each Table has a Policy Number Column).
Im new to Access and do not know how to go about this.
Comparing Tables
Jul 12, 2005Hi,
I have imported data from an ERP system into an Access table. This big table contains information about invoices processed last month. 2 of the columns are currency code and payment batch code.
Actually, there are rules that accountants should follow, but they can make errors. We would like to find those error using this Access database.
One rule is that only predefined payment batch codes should connect to a
currency code.
My idea was to create another table, where controllers are able to type in these rules like
EUR CIE
USD CIU
HUF BKH
This table has only 2 columns: currency code and payment batch code and no primary key has been defined.
The 2 tables were joined with the currency code and a query printed only those transactions where the payment batch code from the big table <> the payment batch code from the new table. This shows where accountants made an error.
This is the query:
SELECT [475 Master].*
FROM Tbl_Pmt_Btc INNER JOIN [475 Master] ON Tbl_Pmt_Btc.Cur_Code2 = [475 Master].Currency
WHERE ((([475 Master].Pmt)<>[Tbl_Pmt_Btc].[Pmt_Btc_Code]));
475 Master is the big table, Tbl_Pmt_Btc is the new table that can be modified by the controller.
This worked until more than one payment batch code were assigned to a currency code.
EUR CIE
EUR CIU
It seems that Access can use only one of them.
Can you please advise what to do in order that Access takes both rows into
account and the query prints those transactions booked in EUR where the payment batch code are neither CIE nor CIU.
Is there a simple solution without any programming?
Thanks,
Peter
Need Help Comparing Tables
May 17, 2005Hello - I am new to Access and don't know VB.
I am trying to compare 2 tables that each contain the following information:
Fields:
Document Number
Date
Changes (Amendments, etc.)
I would like to see the following on a report:
A - If a document number is on Table 2, but not on Table 1, show these on a "missing documents" report.
B - If a document number exists on both reports, however the dates do not match. Example: Document 123 is on Table 1 with a date of 1/1/1999 in the date field. Table 2 contains the same document in the document number field, however the date field contains 2/15/2005. SHow these on a "Date Differences" report.
C - If a document matches on number and date, however the changes field does not match.
Ultimately, I would like to add other fields to compare as well.
Hope this makes sense to someone out there!
Thanks!
Comparing Two Tables.
Dec 24, 2007Hi Guys,
I have made a database for a mobile phone retail store. The purpose of the database is to sort out network (e.g. Orange, AT&T) payments and compare them to our records. Our manager will enter in details into one table, including the different commisions that should be paid, plus the phone number. The other table is an imported spreadsheet that the commisions from the networks are shown. For each table, I have totalled the commisions.
My next step is to compare the two tables against the phone number, and commision totals, so that deals that have not been paid, or underpaid are highlighted. I know this involves a couple of loops, but my knowledge of Access does not go that far.
I have been thinking that I need to write code that gets one number in the first table and compare it against ALL records in the second table until it gets to the one with the same number, compare the values of commision total, and move on to the next record.
Can anyone help?
Cheers!
Comparing Two Tables
Aug 22, 2011I need to create a query that will compare both tables and just send out the records that dont match to a report.
Table1 asset#,Serial#
Table2 asset#,Serial#
query that checks that in Table1 the asset OR the Serial match the what is in Table2. and if it does not find a match to send it to a report.
Comparing Tables/files
Feb 14, 2007As part of my job I am constantly comparing files from a production environment against those from a UAT environment. The checks are generally the generic before I reconcile the monetary values I check that the static data population is the same. Basically I import the files into access and then do a like for like comparison on the number of blanks per field. Eg.
Field Blanks_UAT Blanks_Pro Diff
Coustomer_Number 10 2 8
Is there any way of doing this programmable so I can reuse it or are there any tools out there that do this work for me. Some of the files contain a large number of fields so the work can become rather cumbersome!
Any help is appreciated
Comparing 2 Tables Of Data
Feb 28, 2007Hi guys, i'll try and explain this as best i can....
I have 2 tables:
CurrentMonth
PreviousMonth
Both tables hold financial information by policy number. What i am doing is working out financial movement on a month on month basis. For example, if in previous month the financial position was £100 but in current month the finances had moved to £150 then the movement is £50 for the month. This i can do no problem by linking the tables by policy number and extracting the financial position and subtracting one from the other to give me a movement. However, in some instances there will be some policies that appeared in PreviousMonth but have since been cancelled back to policy inception so is not appearing in CurrentMonth.
Problem: I need to be able to bring in the policies on the previous month that are not appearing on the current month (due to cancellation).
Can anyone help me?
Cheers
Query: Comparing Two Tables
Mar 22, 2006I'm trying to write a report which initially I didnt think would be hard to do, but when it came time to write the underlying query I realized it was a bit above me.
I cant give away too much of the database structure here, due to legal issues, so sorry for being vauge. Hopefully I can give enough information that someone can get me started.
I have three tables that will be used in this query. One is called NAMES and has all of personal details of contacts. The only fields that are needed from this table are the primary key and citizenship field.
The other two tables are virtually the same, and they are the two being compared. One is REGISTRATION and the other APPLICATION. Essentially what I want here is to be able to compare the number of who applied to the number who actually registered.
Here are the pseudo-tables to give you a better picture:
NAMES
nID
FName
LName
DoB
Citizenship
REGISTERED
programCode
startDate
endDate
nID (foreign key)
APPLIED
programCode
startDate
endDate
nID (foreign key)
I've tried a few different approaches which worked in my head, but when it came time to run the query the results were different. Any suggestions on how I should tackle this?
Comparing 4 Tables With A Query
Oct 13, 2004I have 4 tables (one for each quarters data) each with the same fields:
Fund
FundFamily
Value
I have a query for each table that sums the values for each FundFamily (if there are multiple funds with the same FundFamily, it only shows the sum for the Family).
I am wanting a query that will show all of the different FundFamily fields from each table, but only once (say there is a Value in one quarter, but not the next). How is the best way to go about this?
Does what I'm trying to do here make sense?
-GorillaBoze
Comparing 2 Tables Or Queries
Nov 8, 2004I have 2 tables. Quotation Details and Order Details. As you fill in the Order details Form I wish to compare fields from the
Quotation Details Table. Is this possible? Please advise.
Thank you.
Help Comparing And Matching Tables
Dec 18, 2004I have 3 tables that all contain Car registration numbers.
Table 1 contains just Reg numbers. Table 2 contains Registration numbers with an additional 2 columns of data. Table 3 also contains Registration numbers with an additional 2 columns of data.
I need to compare the reg numbers in Table 1 with Tables 2 and 3 and where the same Reg number appears in either of Table 2 or 3 display the results in a new table / query.
ie, Table1 Reg, Table2 data , Table3 data Note there are some Reg numbers that will appear in all 3 tables.
any help appreciated.
Malc
Crosschecking/Comparing Two Tables
Jul 24, 2007I am currently trying to crosscheck/compare two tables. One table is part of a larger Access database, and contains a record of household information such as addresses (split into multiple columns, such as House Number, Road Name, County etc). The other table is an Excel file that I’ve imported, which contains similar data for a particular set of households, also split into multiple columns (though the column types are slightly different). The vast majority of the listings in the Excel spreadsheet should be matchable to a specific entry in the far larger Access table, though a few will not. The matches won’t be particularly straightforward however, as the data entry is not entirely consistent between the two tables, so each match will involve checking combination of different columns for close matches. Ultimately, I want a query that will check each of the Excel table’s entries against the Access table’s entries, and then create a new table listing all the entries that had matches, and the unique id of the matched entry in the Access database.
I am wondering what most straightforward approach to solving this task is. Do I need to use SQL? If it matters, I am using Access 2000.
Thanks in advance.
Tables :: Comparing One Record To Another?
Mar 31, 2015Let's say table looks like this.
Name Type Amount Percent Bonus
John DCP 5000 10
John Sales 4000 5
I need to look at record one and if John's DCP Amount is >=4000 AND his Sales are >=4000 his Bonus amount for DCP would be 5000 * 10 and his Sales Bonus would be 4000 *5 but if the DCP was not >=4000 he would get no bonus for DCP and no bonus for Sales because DCP drives his bonus.
So even if Sales were >=4000 because DCP was not the result would be 0 for both. Make since?
Is there any way to evaluate this without using VBA?
Comparing Fields Of Two Tables And Output To A Third
Jul 20, 2005It 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
Update Query Comparing 2 Tables?
Dec 8, 2005Hi
I have such situation:
i have tables [k] and [r] with street and city field.
I would like set on field[dubel] in the second table [r] in the rows where the street and the city are the same for the both tables.
There could be one to many relations. It means many fields in [k] could have the same as in [r]
I've tried with this query but it marks all the fields....
UPDATE r SET dubel=1 where EXISTS ( SELECT r.str, cit
FROM k, r
WHERE (([k].[str]=[r].[str]) AND ([k].[cit]=[r].[cit])));
when i'm using just select part, it gives me good results.....
Can Anyone help ME?
THANKS
Update Query Comparing 2 Tables?
Dec 8, 2005Hi
I have such situation:
i have tables [k] and [r] with street and city field.
I would like set on field[dubel] in the second table [r] in the rows where the street and the city are the same for the both tables.
There could be one to many relations. It means many fields in [k] could have the same as in [r]
I've tried with this query but it marks all the fields....
UPDATE r SET dubel=1 where EXISTS ( SELECT r.str, cit
FROM k, r
WHERE (([k].[str]=[r].[str]) AND ([k].[cit]=[r].[cit])));
when i'm using just select part, it gives me good results.....
Can Anyone help ME?
THANKS
Comparing Tables - Internal / External
Sep 10, 2007Dear Forum,
please let me ask a second question. I have two tables tbX and tbY, which both contain a column called "LastName". What I am looking for is a query or macro, which compares the columns "LastName" of tbX and tbY and then tells me, which items are contained in tbX but not in tbY.
To make it more complicated I have two cases. In the first case both tables are located in the same database. However, in the second case one of the two tables is not part of my database but located in a different Access-file in a different directory.
I would be happy for any suggestions.
Thank you.
Diana
Modules & VBA :: Comparing Data In Two Tables
Jul 20, 2015I have got 2 access tables named Table1 and Table2. There is one field common in both the tables and that is "Reference" . So I need to match the data in Reference field in both the tables using VBA.
steps to perform:
1. Check each Reference in Table1 against all the values in "Reference" field of Table2.
2. if match occurs then store all the details of that Reference in Table3.
There could be more than 1 match of that Reference in Table 2 and we need to check the whole Table2 for each "reference" value in "table1".
Queries :: Comparing Two Tables Across Months
Sep 16, 2013As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.
I would like to compare two similar tables for different months. I am looking for:
+ loan id's that are new in table(t), i.e. not exist in table(t-1)
+ loan id's that exited: that is exist in table(t-1) and not in table(t)
+ loan id's that stayed in both
How do I solve this in the easiest way?
Modules & VBA :: Comparing Two Tables To See If There Is Any Match?
Jul 7, 2015I have two access tables named as "DestructionTBL" and "AnnutiesTBL". Now I have to check each PolicyNumber of AnnutiesTBL with PolicyNumbers in "DestructionTBL". If match is found then add that PolicyNumber into "NODestructionTBL" and delete that PolicyNumber record from "DestructionTBL".
View 14 Replies View RelatedComparing Similar Columns In Multiple Tables.
Jun 1, 2007Hi Everyone,
I am trying to compare/relate two columns in two tables to each other in access, where the results shoud return similarities among both colums from both tables.Even if one column has some parts of it.
Example;
T1 T2
Name Members
John Johnson.kay
mike mike
Daniel Danielson.mic
Richard Richardson
I tried; "like[T1.Name]*" in the criteria section of Members.Need Help pls.
Comparing 2 Tables To Find Duplicate Values
May 9, 2006Hey all
Im rather new to access but have to use it at work. Ive been asked to see if there is a way to delete duplicate records from a table.
Now, I have 2 tables. The 1st table (tbl_list) contains records of various customer details. This list is old.
The second table (tbl_new_list) contains new customer details.
We have found that we have the same customer details in tbl_new_list that are in the old table.
Im looking for a way to compare these 2 tables so that the values in tbl_list are not in tbl_new_list.
I have tried numerous methods using append queries but nothing seems to work.
Thanks in advance people
P.S the data will be compared through a telephone number.
General :: Comparing Data From Multiple Tables
Nov 5, 2013We have an "item" table that contains information specific to an item (the item number is unique); and multiple "production line" tables. There is a relationship between the Items table and the Production Line tables (all 8 of them). Each item can be entered on 1 or more production line tables because one item may be produced on multiple lines and the settings vary based on the line they're produced. When entering an item in the Production table we MUST make a selection from the Item table.
Now, what I want to do is identify which item number(s) from the Items table does not have a specific field entry in ANY of the Production Line tables.
Modules & VBA :: Comparing Rows / Columns In 2 Tables
Jun 25, 2013I have 2 tables and 1 query. Table dbo_RepOrderItem includes columns:
RepId
OrderNumber
Item
ShipDate
SerialCardID
Table tbl_LBP Sales Location Num includes columns:
Location ID
Rep Region Code
What I've been doing so far through my code is working through a query 'CalculateTotal' which looks up a structure number (Which I enter through an InputBox) from table dbo_RoicStructure, gets the SerialCardID from the same table and collects the associated RepId,OrderNumber,ShipDate from table dbo_RepOrderItem and checks that the OrderNumber(s) returned exist in column Location ID in table tbl_LBP Sales Location Num. It also checks that Rep Region Code is not equal to 'INT' nor 'inte'.
My code for all of this works just fine. What I am trying to do now is simplify my results. I want to only return rows that do not have the same Item, OrderNumber, and RepID.
Fore example:
OrderNumber: Item: RepID:
11 3 1
12 3 1
11 4 1
11 3 1
14 7 4
16 8 8
It would now count 5 existing RepID(s) rather than 6 because it would have deleted the duplicated row. This should only be deleted in the user's table not the actual table.
And speaking of the user's table. What I have been doing so far is only returning the results (right now it returns 6, which as I explained above is incorrect) but I also want to return a table or query that would should the user the work behind the returned number.
This is my code:
Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
[Code] ....
Comparing Data From Two Tables And Appending Into 3rd Table
Dec 8, 2012I have 2 tables as below
Table 1
ID RID StartM EndM
1 xyz 1100 040.0935 040.1254
2 xyz 1100 029.0110 038.1003
3 xyz 1100 029.0110 038.1010
Table 2RID
StartM
EndM
[code]...
Now I need to create 3rd table based on above two tables
for table1.XYZ 1100 table1.StartM>Table2.StartM then table1.ID=2or 3 and
for table1.xyz 1100 table1.EndM< Table2.EndM then table1.ID=1