Help building a simple query from two tables, limited by the records in one table?

[melchior]

Hi,

I have searched and searched without much luck. I feel like I am doing the right thing, just not getting the right result, so here goes.

I have 2 tables. One has about 100,000 records which includes some unique and some repeated and some null data.

The other table about 136 records with similar data.

The only field that matches between the two tables is "Physical Address 1" from Table 1, and "Address Line 2" from table 2.

What I want is to create a query that shows me the 136 records from Table 2 plus the matching data from Table 1, but only for those records with the same address as specified in those fields.

I have made a relationship between the two fields and the result is I get 100,000 records with the data from Table 2 repeated again and again. Not helpful and confusing the heck out of me. I've tried different relationships, and my other databases with relationships have worked the way I expect. Sorry, but I've searched these forums and google and nothing seems to show me how to do this simple thing.

I'm using access 2002 on XP and both databases as flat. When I tried to use the analyzer on Table 1 I got an error about MaxLocksPerFile and had to stop. Could this be the issue, that it's not normalised?

Thankyou, I hope you can help

[abolos]

Hi,

I have searched and searched without much luck. I feel like I am doing the right thing, just not getting the right result, so here goes.

I have 2 tables. One has about 100,000 records which includes some unique and some repeated and some null data.

The other table about 136 records with similar data.

The only field that matches between the two tables is "Physical Address 1" from Table 1, and "Address Line 2" from table 2.

What I want is to create a query that shows me the 136 records from Table 2 plus the matching data from Table 1, but only for those records with the same address as specified in those fields.

I have made a relationship between the two fields and the result is I get 100,000 records with the data from Table 2 repeated again and again. Not helpful and confusing the heck out of me. I've tried different relationships, and my other databases with relationships have worked the way I expect. Sorry, but I've searched these forums and google and nothing seems to show me how to do this simple thing.

I'm using access 2002 on XP and both databases as flat. When I tried to use the analyzer on Table 1 I got an error about MaxLocksPerFile and had to stop. Could this be the issue, that it's not normalised?

Thankyou, I hope you can help

Hi,
use the SELECT SQL command.

SELECT <field> from <table1> where Address line 1 =physical address 1

abolos

[melchior]

Ok, this is helping thanks. The express is right if the values were exactly the same. But I am finding they are not.

Here is what I have

SELECT EasyVic.*, Vic_Entities_Active_2007Aug01.SVID
FROM EasyVic INNER JOIN Vic_Entities_Active_2007Aug01 ON (EasyVic.Address_Line_1 = Vic_Entities_Active_2007Aug01.Phys_Add1) OR (EasyVic.Address_Line_2 = Vic_Entities_Active_2007Aug01.Phys_Add1) OR (EasyVic.ID = Vic_Entities_Active_2007Aug01.ID)
WHERE (((Vic_Entities_Active_2007Aug01.Phys_Add1)=[EasyVic].[Address_Line_2]));
 

Is there a way to return a value in the same manner as "Like" or a fuzzy/partial search? Obviously, I'm working with street addresses, ignoring suburbs.

thanks

[edit]
I'm all over the place and confused. I keep getting multiple records again and again and now I can't use th design view any more...


****

To clarify what I want is to show all the records from EasyVic, plus any data that matches from EasyVic.Address_Line_1 OR EasyVic.Address_Line_2 with Vic_Entities_Active_2007Aug01.Phys_Add1 OR Vic_Entities_Active_2007Aug01.Phys_Add2, but NOT the records from Vic_Entities_Active_2007Aug01 that don't have a match.

More help please?