I 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.
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.
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?
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.
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.
As 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!
Hi 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).
I'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?
I 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?
I 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.
I 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.
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?
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.
Hi 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
Hi 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
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 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".
As 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
I 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".
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.
We 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.
I 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.
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
