Ask Your Question
0

Query with multiple tables displays duplicate records. [closed]

asked 2014-08-14 11:02:14 +0200

LoveLibre gravatar image

I have a database with 5 tables. First one contains names only and is in 1:n relationship with every other table. Table 1 has 1 record. Table 2 has 6 records.

When making Query with Names Table, Table 1 and Table 2, the Query displays the only record from Table 1 six times. Is there a way to avoid this?

Thanks in advance for your help with this.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-02 00:03:36.417689

1 Answer

Sort by » oldest newest most voted
0

answered 2014-08-15 00:26:39 +0200

doug gravatar image

With the very limited info provided, two issues. Joining and visible columns.

The query presumably works by "joining" the two tables. It is possible that your query is incomplete in this respect. If so, in the query builder click on the field that is supposed to match in Table1 and drag it to the matching Table2 field. This will create a relationship in the query. Right mouse click the resulting line to change the relationship from 1:1 (inner join) to 1:n (right/left join). You will see a dialog box. (You also may be able to enter criteria in a column to make the join, in the following format, e.g., for Table1, column1: [Table2].[column1] ).

After doing this, if you just want to make the columns from Table1 invisible, click the check box in the query builder for "visible" on the name columns. When the query is executed, those columns will not be visible.

edit flag offensive delete link more

Comments

Thank you Doug, but it didn't work. I tried but when I join (inner, left or right) Table 1 and 2, and run the query, nothing is displayed.

I am guessing that the record from Table 1 is displayed multiple times because it is, as all other records in Table 2, related to the same Name in the Names Table. But I would need it there only once because when creating a report it is (the record from Table 1) in there duplicated as well.

I'll try to describe this one more time with more details:

  • Table Names contains 1 record
  • Table 1 contains 1 record
  • Table 2 contains 2 records

  • Table Names is in 1:n relationship with Table 1

  • Table Names is in 1:n relationship with Table 2

The records from Table 1 and from Table 2 are entered via form and attributed to John ...(more)

LoveLibre gravatar imageLoveLibre ( 2014-08-15 05:00:21 +0200 )edit

It would help if you posted your query, and explained your database schema more fully. It's very hard to give you more detailed feedback without this info.

frofa gravatar imagefrofa ( 2014-08-16 23:55:41 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-08-14 11:02:14 +0200

Seen: 1,093 times

Last updated: Aug 15 '14