Comparing 2 Tbles And Then Inserting New Only

Sep 17, 2005

I have two tables named datadistint and links
and i need to insert only some values in five fields of links from datadistint but before inputting values we need to ensure they are already not there in links.

Insert Into links (LinkTitle,LinkURL,LinkDescription,maincat,cat)
Select 1,3,2,4,5 From datadistint Where
1 Not in (Select LinkTitle From links) and 3 Not in (Select LinkURL From links)
and 2 Not in (Select LinkDescription From links)
and 4 Not in (Select maincat From links)
and 5 Not in (Select cat From links)

i ran the querry as above but it doesn't work what should i do or what's wrong with this querry,
because i need to compare all five fields and then insert only new values.

View Replies

Comparing Tables

Aug 17, 2006

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

View 3 Replies View Related

Comparing Tables

Jul 12, 2005

Hi,

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

View 2 Replies View Related

Need Help Comparing Tables

May 17, 2005

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

View 1 Replies View Related

Comparing To Columns

Jun 15, 2006

If I have a date column and another column with another consistant date, Can I make a criteria to compare one to the other. Ex:

END_DATE Today's_DATE
06 Feb 05 15 Jun 06
12 Feb 05 15 Jun 06
01 Jan 06 15 Jun 06
26 Mar 06 15 Jun 06
18 Aug 06 15 Jun 06
28 Dec 06 15 Jun 06

Can I make a criteria that will only show the END_DATE data that is before Today's DATE? Does it need to be a new column or can I place it in the criteria of one of these column.

View 1 Replies View Related

Comparing Records

Aug 16, 2006

Hi,

I need to compare records in the same table. I know that this is typically accomplished by joining the table with itself. However, this is not entirely suitable for my needs. I need to compare the first record to every other record - excluding itself - and then compare the second record to every other record excluding itself AND the first record. I want to continue this pattern for all the records. Is there any way I could go about doing this?
Any help would be much appreciated.

Edit: Each record has an autonumber generated key

View 12 Replies View Related

Comparing Two Strings

Jan 29, 2007

Hey Guys,

Just wondering how I would go about comparing two strings, and spitting the results out a similarity percentage?

E.G. (String 1) Postal Address: "11 John St"
(String 2) Street Address: "11 John Street"
(Output): 80%?

Cheers

View 5 Replies View Related

Comparing Two Databases

Apr 10, 2007

Hi

what i want is, if i update one database to new database then
i want to compare tables in these two databases if any table is not matched it should be retrieved,so please tel me the solution for this.

View 3 Replies View Related

Comparing Two Datbases

Apr 11, 2007

Hi

how to compare one table of one msaccess database to another table in another msacess database. please tel me the steps to do this.

thanks
sri

View 1 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 Data

Jan 31, 2005

Hi all.
I have a Quotation (Table), Quotation Details (Table), with One to many Relationship. Quotation Details(Table)
selects info from Products(Table). Then I have an Order (Table) connected with Quotation (Table) and Order Details
(Table) for ordering products.
What I want to do is to compare, when i make an order, the Quotation Details with the Order Details.
For example in a a quotation the customer is quoted with 4 piesces of modem. Then the next day he comes back with the
order but now he wants 3 modems. I would like to display a warning to the user or smt like that, inform him
that the quotation details are different from order details.
Is it possible??
Thank you in advance.

View 1 Replies View Related

Comparing Values

Oct 28, 2007

i want to know if it is possible to compare values (a string) stored in a field with a field of another table?
for example, i have a table named Courses, it has field Course ID...and Course Requirements.
the field Course requirements can accept only 3 value: A,B and C.
i have used a combo for this.
then, i have Student qualifications table. i have also designed a form to input student qualifications.
my problem is that i have to compare qual. of students with the req of the course, to allocate them a specific course. if both values meet, the db automatically allocates the course to the student.

View 1 Replies View Related

Comparing Data

Dec 31, 2007

I have a database where I need to take the sum of all income and categorize it to find the commission rate according to what the sum is. For example, if the sum is between $0 and $7,500, the level is "A" and commission rate is 5%. If it's between $7500.01 and $15000, the level is "B" and commission rate is 4.5% and so on. I have this working with a switch() statement, and it finds the correct level exactly as I want it to.

Here's where I'm having a problem: if the sum of all income is $9,000, $7500 of that needs to be level "A" at 5% and the other $1500 needs to be "B" at 4.5%. How do I get that to split up?

Any suggestions would be appreciated.

Thanks.

View 1 Replies View Related

Comparing Two Tables.

Dec 24, 2007

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

View 5 Replies View Related

Comparing Two Tables

Aug 22, 2011

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

View 11 Replies View Related

Comparing Tables/files

Feb 14, 2007

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!

Any help is appreciated

View 3 Replies View Related

Comparing Records In A Table

Jun 2, 2006

I have been searching on here and have not found an answer, could be due to me not seeing it or its not here. I have a multi part question.

Anyways, here is what I have. I am getting data from a time clock, which dumps the data into an access database. It dumps the data as EmployeeNumber (Number), DateTime(text). I use the format command and make a new table to give me EmployeeNumber(Number), Date(text), Time(text).

Question 1:How can I convert these to date and time fields instead of text fields?

After I do that I need to be able to compare the records within the table, such as: I need to check to see if the date is the same between one record and the one above it (assume that I have sorted this correctly) if the date is the same then I need to subtract the first record from the second record to give me the number of hours between the two records.

Question 2: How would I go about comparing those records in the same table?

Thanks in advance to those who can help. Even ways not to do this would be good.

View 5 Replies View Related

Comparing And Replacing Data

Oct 17, 2006

Hi, I have 2 tables with similar data. However I need to compare this particular field called CompanyName in both tables. Reason being human error/exposition data errors. For example, Chef Kitchen Holdings Limited, in one table it is Chef Kitchen Holdings Ltd while the other table is Chef Kitchen Holdings Pte Limited, they both are the same but Access recognise them as 2 distinct datas.

Ｔｈｅｒｅｆｏｒｅ　Ｉ need help in comparing datas between these 2 tables for this particular field. So long as there is 75% similarities, one of the 2 tables will have the data replaced.

Is that possible using Access? if not, how about excel? please suggest.

thanks.

View 4 Replies View Related

Comparing 2 Tables Of Data

Feb 28, 2007

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

Can anyone help me?

Cheers

View 1 Replies View Related

Comparing 2 Lists - How To Setup

Jun 14, 2007

I would really appreciate if someone could help me with the following.

I have one list (3 million records)

Table = DNC
Field 1 - Area code
Field 2 - Phone number

I have another list that has say 1000 records

Table = Phone list
field 1 - area code
field 2 - phone number

What type of relationship do I need to setup in order for me to see which records in the Phone list table are not in the DNC table?

thanks for any help

Josh

View 1 Replies View Related

Query: Comparing Two Tables

Mar 22, 2006

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?

View 3 Replies View Related

Query Comparing Two Other Queries?

Mar 24, 2006

I want to compare two queries as follows: Select all the records in query1 where the value in fieldA does NOT also appear in any record in fieldA of query2. Is there an easy way to do this using the query tools or will this require a vba function?

View 3 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 Rows In A Query

Aug 26, 2006

is it possible to compare values in 2 different rows? for example if the query returns this:

1 | $5.00
2 | $6.00
3 | $20.00
4 | $30.00

i want to find out the cost difference between 1 and 2 which will be $6-$5 and then 2 and 3 which will be $20-$6 and so on. how can i make this happen in SQL? any suggestions?

View 6 Replies View Related

Comparing Two Tables In Msaccess

Apr 12, 2007

Is there any way to compare two tables and write out the records that don't match to a third table?

thank you.

View 1 Replies View Related

Comparing Current Time

Apr 13, 2007

I have a table with a list of events and times at which they occur. I would like to compare these times with the current system time, and display perhaps the next 10 events chronologically. Does anyone know how to do this?

Thanks

View 1 Replies View Related