By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Aug 8 '07 #1
Share this Question
Share on Google+
2 Replies


P: 65
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
Aug 8 '07 #2

P: 2
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
Expand|Select|Wrap|Line Numbers
  1. SELECT EasyVic.*, Vic_Entities_Active_2007Aug01.SVID
  2. 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)
  3. WHERE (((Vic_Entities_Active_2007Aug01.Phys_Add1)=[EasyVic].[Address_Line_2]));
  4.  
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?
Aug 8 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.