Having Query display all values.

[tyrollean9]

I am trying to set up a query for reporting purposes. I have a table with activity codes that has two fields [FieldA] and [FieldB] which are linked to one other table each that has rates associated with [FieldA] or [FieldB]. I ran a query by just pulling in the table with activity codes and it gave me the first activity code repeating an infinite amount of times....can you explain why?

Then I brought in the other two tables associated with [FieldA] and [FieldB] and added the rates field associated with each into the query design fields. When i ran the query now it provided me only those activity codes that had [FieldA] and [FieldB] populated.

My goal is to have all activity codes display in the query results whether they have [FieldA] populated or [FieldB] populated. If they are populated the query will bring in the associated rate. If they aren't the query will bring in a blank under rate. The activity codes table should not require that [FieldA] or [FieldB] be populated.

My guess as to why only results with both fields being populated is because of blanks vs. null values.

Thanks.

 

[Micron]

which are linked to one other table each

First, that doesn't sound right. Why doesn't tblRates have 1 field for ID, 1 for ActivityID and 1 for Rates? As for why it repeats, no clue with the information given. Maybe if you posted the sql (please use code tags).
To get all records from a table when there are no records related to the joined fields, an outer join is required. Try putting the table you want all records from on the left, the other on the right, and change the join property so the arrow points to the right. If you get too many results, you can try playing with the query Unique Values and Unique Records property (but you can only say yes to one or the other at a time). If you still get too many records, it's likely that you have data repeating in other tables where you shouldn't, or one or more fields you bring into this query fall into that trap. You may have to restrict the number of fields you include to get the bare minimum number records you need, and use that query as a table in another query that brings in the fields you need. Often, all this run around is due to improper table design.