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 a front-end/back-end database that another programmer has done some work on for me. He worked on his own copy which wasn't linked to our data. Now I'm ready to integrate his work. What I would like to do is take his improved front-end and link it to the existing back-end tables. All the table names are the same, and none of the fields have changed. Is there an easy way to do this?
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.
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.
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?
Once external tables have been linked to a database can the links ever get dropped accidentally.
ie should I test for missing links and recreate them programatically or assume that it will never happen?
It is easy enough to identify links which are present but any code which loops through the tabledefs will only identify links which are present.
I think the best approach, if this is necessary, would be to count the number of external links, compare the answer to what it should be, and refresh all the links if theree is a difference.
SELECT pendingdetailsDrum.ID, DateValue([orderDate]) AS DatePortion FROM pendingdetailsDrum WHERE DateValue([orderDate])=DateValue('1/2/2006')
is giving me Internal OLE Automation Error, however when I remove the Where clause it works.
Can someone explain why? OR...
The reason I am using DateValue is because the orderDate field has a date portion and a time portion. When I put a date in the criteria it is not found because of the time portion. I guess I could run an update query and change all the date + time to just date, however , I would rather use the SQL Statement using the DateValue function.
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
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 have an Access db in a 3 person multi-user environment on a Windows network.
There is a "PRODUCTION" db and a "DEVELOPMENT" db.
Let's call them PROD and DEV.
PROD has the most current data, shipping records, item master, customer data, sales, etc. - but not the most current structure.
DEV has the most current struture - all the front end stuff - forms, functions, modules, etc. - but not the most current data.
The way I've handled this in the past (it seemed to work) was to take the PROD db and rename it to PRODX. Then take the DEV db and rename it PROD. Then open DEV (now called PROD) using the usual shortcut to PROD.
So now that opens fine, and I have to update all the tables from PRODX - and I mean ALL of them - since I don't really know what data has changed since the last update.If I try to delete records I get blocked by access because of all the related records (I don't have cascade delete set on every relationship). So I delete the whole table - ALL of them (this requires me to also delete the relationships). Then I IMPORT all the tables from PRODX (these have the current data). But now the relationships are all gone. It seemed at first that the relationships were back and intact - but when I last looked they were gone. So this is my problem.
HOW do I COMPLETELY empty ALL of the tables - or even delete them - and then restore them or repopulate them from an exact copy of the db but with current table data - and WITHOUT affecting the relationships?
Obviously any back end structural changes have to be handled differently. Usually by manually making the same change on the PROD db that I had in the DEV db - because deleting the table will cause the structural changes to be lost.
One last thing - I've been working this way on an un-split db, and now I'm in the process of splitting it - which SHOULD make updates much easier.
