+ - a number

[smash54]

I have a small db only 2 tables table1 table2 what im looking at is if table2 has a record that is the same as table1 it would show after i run the query. One or the fields is a number and i want whatever table2 number is it will look for that number and + and - 20.

 

[ByteMyzer]

Your request is rather vague. For instance, you do not state what other fields you are linking/comparing between the two tables besides the number field.

Could you provide some sample data/expected output?

 

[smash54]

There is about 5 fields first name last name dob and a document number the document number is about 7 digets long. what I want to do is I have a table with a list of 3000 records and when i import a list into the 2nd table i want to run a query that will give me all of the same records from 1 to the other. The other thing i want it to do is look in the imported table for records that match the document number plus look for documents + or - lets say 10.

So lets say table1
First Name.... .Last Name..... DOB......... Document Number
Joe ...............Smith .........19540101 ......123456789
Larry .............Walker .......19811202 .......223455667

Table2
First Name ......Last Name...... DOB .......Document Number
Joe .................Smith
Bill................... Fill........... 19560101 .......123456785

So I want to compare the 2 tables and it should bring up the first record in table 2 because the name matches. And it should also bring up the secound record because what i want to do is look for the doc number + or - 10.

 

[ByteMyzer]

Based on the Table/Field information provided, you could try:

SELECT T2.*
FROM Table2 AS T2
WHERE EXISTS (
      SELECT T1.*
      FROM Table1 AS T1
      WHERE (T1.[First Name] = T2.[First Name] AND
             T1.[Last Name] = T2.[Last Name])
      OR ABS(T1.[Document Number] - T2.[Document Number]) <= 20);

 

[smash54]

That did it I had to get rid of the join on the table and add <=20 and all works great thank you .

 

[smash54]

Let me ask just one more thing well this time when I run the query it gives me the whole record. What I would like to see is if its hitting on the first name, last name or document number I would like a indication on what one. maybe that field would be a diff color or something.

 

[ByteMyzer]

For that you would need to create a Form or Report with your query as its recordsource. From there you should be able to apply conditional formatting for the color indicator.

 

[smash54]

But how would I tell the form what field made the record

 

[ByteMyzer]

Revise your query thus:

SELECT T2.*, [B][I]Iif(T3.[First Name] Is Null, 'DOC', 'NAM') AS STAT[/I][/B]
FROM Table2 AS T2
[B][I]LEFT JOIN Table1 AS T3 ON T2.[First Name] = T3.[First Name]
                      AND T2.[Last Name] = T3.[Last Name][/I][/B]
WHERE EXISTS (
      SELECT T1.*
      FROM Table1 AS T1
      WHERE (T1.[First Name] = T2.[First Name] AND
             T1.[Last Name] = T2.[Last Name])
      OR ABS(T1.[Document Number] - T2.[Document Number]) <= 20);

...then use the STAT fiield for your conditional formatting in your Form or Report, using the value of the field (DOC or NAM) to determine the formatting.

 

[smash54]

Ok so is t3 the table that is made from the query and im looking for a match to table t2.

 

[smash54]

What my problem is when the first query runs and looks for same in t1 to t2 i need to know what it hit on was it the doc or name. but also show the rest of the record so the name could have multiple hits but diff doc numbers. but i need it to show the other doc numbers but let me know it hit on the name.

 

[smash54]

I want to say thanks for all the help so far the reason im doing this is the more fields that match i want to rank. if a records fields match in more then one field that would give it a higher ranking.

 

[ByteMyzer]

Ok so is t3 the table that is made from the query and im looking for a match to table t2.

No, T3 is another alias instance of Table1. Within the query, T1 and T3 are alias instances of Table1 and T2 is an alias instance of Table2. I recommend that you spend some time studying table aliases in a query, as well as the use of the EXISTS clause.

What my problem is when the first query runs and looks for same in t1 to t2 i need to know what it hit on was it the doc or name.

Expression STAT from the modified query returns a value of 'NAM' if the match is made on the Name, and a value of 'DOC' if the match is made on the Document.

I want to say thanks for all the help so far the reason im doing this is the more fields that match i want to rank. if a records fields match in more then one field that would give it a higher ranking.

I think I've given you more than enough information at this point to be able to figure this out for yourself.