Query returning many duplicate records

[Roberto]

Hello. I have set a simple query based on one table. The problem is, when I
add a second table to choose from on the query design, and run the same
query, it returns the same set of records over and over and over. Does
anybody know why?

 

[Ryan]

If there is not a relationship between the two tables, then the query will
evaluate each table seprately, and show every record in table one for every
record in table two. If there is a realtionship, then you can right click on
the line that joins the two tables and define how you want to see the data.
You can choose to see all the data from one table and only records that match
in the other table, or you can see records where both tables have a match.
If you provide more information about your tables, ie if they have a
relationship, we could better assist you.

 

[Roberto]

Ryan,

I really appreciate your prompt response. I have tables with exactly the
same fields, the only difference is that the data pertains to different
years. For example, I've got a table with 1998 data and a table with 1999
data. When I design the query, I only show the 1998 table and set my
criteria. When I run the query it works fine. However, when I show table 1999
and change the table to reflect 1999 table data rather than 1998 and run the
query, it returns 1999 data but duplicated (if it returns three records, it
repeats the three records over and over again). In other words, apparently
the way I have designed my query messes up the recordset when I show more
than one table.

Hope this is clearer.

roberto

 

[Ryan]

Why not just create a query for each year? That should solve the problem.
The only reason to add more than one table to a query is to find information
that relates from one table to another table and to show that data in one
place. What you are tying to do is show just whats in the table, so just
make a query for each table.

 

[Roberto]

Hey Ryan,

Thanks, I did think about that. However, I still don't understand why it
does that. A temporary solution is to limit the number of returns. But, when
I don't know the amount, then it gets interesting and I need to manually
purge the duplicated records. For example, I am trying to query the number of
establishments of a particular industry (County Business Patterns) for nine
counties. But, I do not know if there are establishments under those specific
industries in the counties I am interested, therefore I cannot calculate the
number of returns.

 

[Ryan]

If you wanted to calculate the number of establishments in a particular
industry, and all the data was in one table, you could make a query based on
that table. Then, in the query you would add the Industry column, and the
Establishments column. Now right click in the the query and click on Totals.
This will add another property to your query. In the totals for
Establishments, select count, and in the totals for Industry, select group
by. This will select all industries and count how many Establishments they
have. If you only want to see one industry, in the criteria under Industry
just add the name of the industry you want to see.

 

[NuBie via AccessMonster.com]

I have tables with exactly the same fields, the only difference is that the data pertains to different

years.

it looks to me as poor database design. why keep two tables with "exactly the
same fields"


you can merge these two tables into one. add date/time field for the year.
you can pass <year> argument when querying specific date range.

Ryan,

I really appreciate your prompt response. I have tables with exactly the
same fields, the only difference is that the data pertains to different
years. For example, I've got a table with 1998 data and a table with 1999
data. When I design the query, I only show the 1998 table and set my
criteria. When I run the query it works fine. However, when I show table 1999
and change the table to reflect 1999 table data rather than 1998 and run the
query, it returns 1999 data but duplicated (if it returns three records, it
repeats the three records over and over again). In other words, apparently
the way I have designed my query messes up the recordset when I show more
than one table.

Hope this is clearer.

roberto

If there is not a relationship between the two tables, then the query will
evaluate each table seprately, and show every record in table one for every

[quoted text clipped - 9 lines]

 

[beccadawn0622]

Ryan,

I am having a similar problem as Roberto. My tables are different though. I
have built the necessary relationships but when I run the query I will get
four to 8 replicas of the same record. Any suggestions?

Rebecca