What type of Query Should I use so My Data don’t Duplicate?Please Help

[TJVFree]

I'm wondering what would be the correct way to combined queries that have two columns that are different, but need to be added to the same report

I'm trying to link four tables together.
In one table I'm going to be using all the fields [Feild1],[Feild2],[Feild3],[feild4]

In the other 3 queries I would like to all a column and put one row of data from each other query's [Feild1],[Feild2],[Feild3],[feild4] adding [FieldA],FieldB]

This shows just a select query in access, putting the tables together

[SQL]
SELECT Wholesale_Rank_qry_2.OMNI_Number,
Wholesale_Rank_qry_2.branch,
Wholesale_Rank_qry_2.branch_name,

Wholesale_Rank_qry_2.[Wholesale_Rank>],
Table_Rank_qry_2.[Table_Rank>],
Purchase_Rank_qry_2.[Purchase_Rank>],
CUSB_Rank_qry_2.[CUSB_Rank>]

FROM CUSB_Rank_qry_2 RIGHT JOIN (Purchase_Rank_qry_2 RIGHT JOIN (Table_Rank_qry_2 RIGHT JOIN Wholesale_Rank_qry_2 ON Table_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number) ON Purchase_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number) ON CUSB_Rank_qry_2.OMNI_Number = Wholesale_Rank_qry_2.OMNI_Number; [/sql]


The main table with all the data in it is Wholesale_Rank_qry_2. <-This table will have all of the Wholesale_Rank field's populated.

Now the queries below will only have the rank field populated if they have a rank from there original table

Table_Rank_qry_2.[Table_Rank>],
Purchase_Rank_qry_2.[Purchase_Rank>],
CUSB_Rank_qry_2.[CUSB_Rank>]

Thanks for taking the time to help me learn more about what queries I can us

TCB

 

[lameid]

I am confused, you describe the problem one way with example field names and then show a select statment that does not describe the problem that way and has 4 tables instead of 2.

In general, you should understand how the underlying data relates. Is it a one to one relationship or a one to many?

If it is one to many, you would expect the one side data to repoeat for every record on the many side.

That is assuming your joins are appropriate to the relationships of the tables. Otherwise you could end up with all kinds of repeated data.

 

[randy700]

Perhaps something like this?

SELECT Wholesale_Rank_qry_2.OMNI_Number, 
Wholesale_Rank_qry_2.branch, 
Wholesale_Rank_qry_2.branch_name, 
Wholesale_Rank_qry_2.[Wholesale_Rank>], 
[red]iif(Table_Rank_qry_2.[Table_Rank>] = "", "", Table_Rank_qry_2.[Table_Rank>] AS SomeField[/red]

Randy