Repetitive Records (1 Viewer)

[jaytheguru]

Hello! i have created three tables and inserted different records in them. I want only "genes" records from table 1,2 and 3 to be shown. The problem I am having is that when I design the query and ran it, it shows me all the possible combinations of records instead of showing only 1 record.

Table 1 - Number - Population - Genes
Table 2 - Number - Genes
Table 3 - Number - Genes

Table 1 - Records
==============
Number 1 - India - AB48
Number 1 - Pakistan - AB35
Number 1 - South korea - AB48

Table 2 - Records
=============
Number 1 - BD34
Number 1 - GF45

Table 3 - Records
=============
Number 1 - HG65
Number 1 - LK98

SQL
=====
SELECT Table1.Number, Table1.Population, Table1.genes AS Table1_genes, Table2.genes AS Table2_genes, Table3.genes AS Table3_genes
FROM Table3, Table2, Table1;

Now the question is that either I need to use Criteria or Filter to show the each record from the table. The problem I am having is that when I run this query it shows me all the possible combinations of each record from the table.

I want to show only the records which are in those fields and nothing more or less.

Could you please help.

SIZE="1"][Please note: The database I have shown here is an example and does not reflect my personal database however I have made an example to show what i am trying to achieve here][/SIZE]

Many Thanks

Jay

 

[Rabbie]

Thats the way queries are resolved unless you have JOINs defined. Access will do this for you if you have defined relationships between the tables. You can also have criteria which will limot the selections.

It would be helpful if you could post the results you want from your sample data.

 

[jaytheguru]

Thats the way queries are resolved unless you have JOINs defined. Access will do this for you if you have defined relationships between the tables. You can also have criteria which will limot the selections.

It would be helpful if you could post the results you want from your sample data.

OKi, I need to show the records using a query.

The query should show the results from all three tables.

Query results should be
=================
Number - Population - Table 1Genes - Table2 Genes - Table3 Genes
Number 1 - India - AB48 - BD34 - HG65
Number 1 - Pakistan - AB35 -GF45 - LK98

etc

BUT query shows all the combinations of records instead of each record, it duplicates the records from each table.

The records are duplicated 9 times each when I run the query. This needs to show only once not 9 times.

Many Thanks

Jay

 

[Rabbie]

Thanks for your reply. You need to tell Access why some records in table2 only go with some records in table1 and not with others and so on.

Have you read up on normalisation? Remember Access can only tell you what you have told Access.

What is the difference between table 2 and table3. They seem to be the same to me in structue. Why aren't they combined. Help us to help you

 

[Pat Hartman]

Open your query in QBE view and draw join lines between the columns of each table to define the relationships.

Although you can limit the resultset of a cartesian product (what you have now), the result will not be updatable whereas a query with the specific joins may be (other things impact this).