Query and relationships

Query and relationships

I have two tables. The first table (table1) has four colums A, B, C and D. 
There are 10 records. In the second table (table2) there are 3 columns E, F & 
G.  There are four records in the second table. 
I need a query, that would contain all the ten records from the first table 
and add the data from column F of the second table, based on a relationship 
(columns A from table 1and Column E from table2 are identical type of data). 
Note that not all records of the second table has a value in column F. 
When i create a query, i get more than 10 records. There is some 
duplication. The actual data I am working has more than 5000 records making 
it more difficult to idenitfy the problem. Any help is apprecitaed

date: Mon, 18 Aug 2008 05:09:01 -0700 author: KUMACCESS

RE: Query and relationships

Create a query with both table1 and table2.  Right click on the line that 
shows the relationship.  Select Join Type, and then select "Show all records 
from table1 and only records from table2 that match".  Add all columns from 
table1 and only column F from table2.
-- 
Please remember to mark this post as answered if this solves your problem.


"KUMACCESS" wrote:

> I have two tables. The first table (table1) has four colums A, B, C and D. 
> There are 10 records. In the second table (table2) there are 3 columns E, F & 
> G.  There are four records in the second table. 
> I need a query, that would contain all the ten records from the first table 
> and add the data from column F of the second table, based on a relationship 
> (columns A from table 1and Column E from table2 are identical type of data). 
> Note that not all records of the second table has a value in column F. 
> When i create a query, i get more than 10 records. There is some 
> duplication. The actual data I am working has more than 5000 records making 
> it more difficult to idenitfy the problem. Any help is apprecitaed

date: Mon, 18 Aug 2008 06:19:01 -0700 author: Ryan

RE: Query and relationships

Ryan, 
Thanks for the info. I did try that and it shows more records intead of 10 
records of table 1. That's where the problem is. Not sure, if I explained 
well in the first instance.

"Ryan" wrote:

> Create a query with both table1 and table2.  Right click on the line that 
> shows the relationship.  Select Join Type, and then select "Show all records 
> from table1 and only records from table2 that match".  Add all columns from 
> table1 and only column F from table2.
> -- 
> Please remember to mark this post as answered if this solves your problem.
> 
> 
> "KUMACCESS" wrote:
> 
> > I have two tables. The first table (table1) has four colums A, B, C and D. 
> > There are 10 records. In the second table (table2) there are 3 columns E, F & 
> > G.  There are four records in the second table. 
> > I need a query, that would contain all the ten records from the first table 
> > and add the data from column F of the second table, based on a relationship 
> > (columns A from table 1and Column E from table2 are identical type of data). 
> > Note that not all records of the second table has a value in column F. 
> > When i create a query, i get more than 10 records. There is some 
> > duplication. The actual data I am working has more than 5000 records making 
> > it more difficult to idenitfy the problem. Any help is apprecitaed

date: Mon, 18 Aug 2008 06:41:01 -0700 author: KUMACCESS

RE: Query and relationships

There is a wizard in access that will help you in identifing your duplicates. 
 Go to the queries, and then hit the new button at the top.  One of the 
wizards is a find duplicate query wizard.  You will need to identify and 
remove duplicates before you do anything else.  Try out the wizard and let me 
know if that helps.
-- 
Please remember to mark this post as answered if this solves your problem.


"KUMACCESS" wrote:

> Ryan, 
> Thanks for the info. I did try that and it shows more records intead of 10 
> records of table 1. That's where the problem is. Not sure, if I explained 
> well in the first instance.
> 
> "Ryan" wrote:
> 
> > Create a query with both table1 and table2.  Right click on the line that 
> > shows the relationship.  Select Join Type, and then select "Show all records 
> > from table1 and only records from table2 that match".  Add all columns from 
> > table1 and only column F from table2.
> > -- 
> > Please remember to mark this post as answered if this solves your problem.
> > 
> > 
> > "KUMACCESS" wrote:
> > 
> > > I have two tables. The first table (table1) has four colums A, B, C and D. 
> > > There are 10 records. In the second table (table2) there are 3 columns E, F & 
> > > G.  There are four records in the second table. 
> > > I need a query, that would contain all the ten records from the first table 
> > > and add the data from column F of the second table, based on a relationship 
> > > (columns A from table 1and Column E from table2 are identical type of data). 
> > > Note that not all records of the second table has a value in column F. 
> > > When i create a query, i get more than 10 records. There is some 
> > > duplication. The actual data I am working has more than 5000 records making 
> > > it more difficult to idenitfy the problem. Any help is apprecitaed

date: Mon, 18 Aug 2008 07:22:08 -0700 author: Ryan

Re: Query and relationships

On Aug 18, 8:41 am, KUMACCESS 
wrote:
> Ryan,
> Thanks for the info. I did try that and it shows more records intead of 10
> records of table 1. That's where the problem is. Not sure, if I explained
> well in the first instance.

If you create the query manually - add both tables to the grid and
join the two tables on the shared field.  Then right-click the line
joining the tables and select the second or third option, depending on
which table has the records you want to keep.  Sounds like you picked
the wrong table.  Once you get that working, you can filter the second
table (the "outer" one - the one with the arrow pointing at it) by
dragging it to the grid and filtering.  Where the join field (in Table
B) Is Null (or Is Not Null), depending on what you need.

date: Mon, 18 Aug 2008 10:06:49 -0700 (PDT) author: unknown