query returns duplicated fields from the table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of DVD titles. When I run a query using the fields DVDID and
DVDName just a list of DVDs is returned. If I add tblLoans and include the
date of issue and return and whether they have been returned the original
names duplicate themselves three more times, so instead of 40 records I get
160 returned.
 
KG said:
I have a table of DVD titles. When I run a query using the fields DVDID and
DVDName just a list of DVDs is returned. If I add tblLoans and include the
date of issue and return and whether they have been returned the original
names duplicate themselves three more times, so instead of 40 records I get
160 returned.
Click to expand...


That just means that some DVDs have been loaned out more
than once. The result dataset will have a record for each
matvhing record in the Loans table (that I assume has 160
records).

Your description of the tables is probably(?) adequate, but
when you have a querstion about a query, please post a
Copy/Paste of the query's SQL statment.
 
Thanks for your help - in answer to your question - the database is for a
pupil I teach - it has only 4 DVDs on the tblLoan. One has been returned. I
will try to post a copy of the SQL statement.

If it helps, I have used New Query in design view. The pupil has
successfully created a query to show only those DVDs with are overdue. That
was based on tblLoan (for the date and return) and tblCustomer for the
person's name and telephone number to contact them if they had an overdue DVD.

When I tried to help her set up a query to produce a report of which DVDs
are available I hit this snag. Using tblDVD alone was fine - as soon as I
added tblLoan to the grid in design view only the four DVDs on that table
showed when I ran the query. Eventually it did run, but as I said everything
was exactly duplicated three further times, giving all the information 4
times in total. I wonder whether there is something wrong in the way the
pupil has set up the tables. (Certainly none of the forms worked.) I have
looked at the design view of the tables, field lengths and types match. This
is now getting a bit complicated to explain, so will finish there.
Many thanks for your help.
 
Ahh Ha! The number of records in each table is the key clue
here. 4 times 40 is 160, which clearly implies the you are
using a cross product type Join. You probably have no idea
what that is, but it is represented in the query design grid
by the lack of a line connecting the primary key field in
the DVD table and the foreign key field in the Loans table.
(This kind of query will join every record in one table to
every record in the other table.)

You can create the line by dragging the mouse from one of
those fields to the other. This will create an INNER JOIN
where only records where the two fields match are returned.
(This is also called the intersection of the two tables).

If you should want all the records from one table and any
matching records from the other table (LEFT or RIGHT JOIN),
then right click on the line to pop up a Join Properties
dialog box where you can select the type of Join you want to
use.
 
Thank you so much for your help - I'm fairly sure I understand what you mean.
I will have another look at it in the morning and try incorporating your
advice.

Eventually, I remade the tblLoan, though and the queries and have not been
able to replicate this 160 record phenomenon. I created a Primary Key in
tblDVD which then became my Foreign Key in tblLoan. I then found that only
those 4 DVDs on the loan table would appear on the query when tblLoan was
present in the Select query in design view, regardless of whether any fields
were selected from it or not. I overcame this problem by adding all the DVDs
to tblLoan, but not dates for their issue and/or return and then the query
showed all the DVDs, when I added the dates and whether they were loaned out
or returned.

This is sufficient for the pupil's needs. Access continues to be a program
which stretches the mind...

Many thanks - KG

Marshall Barton said:
Ahh Ha! The number of records in each table is the key clue
here. 4 times 40 is 160, which clearly implies the you are
using a cross product type Join. You probably have no idea
what that is, but it is represented in the query design grid
by the lack of a line connecting the primary key field in
the DVD table and the foreign key field in the Loans table.
(This kind of query will join every record in one table to
every record in the other table.)

You can create the line by dragging the mouse from one of
those fields to the other. This will create an INNER JOIN
where only records where the two fields match are returned.
(This is also called the intersection of the two tables).

If you should want all the records from one table and any
matching records from the other table (LEFT or RIGHT JOIN),
then right click on the line to pop up a Join Properties
dialog box where you can select the type of Join you want to
use.
--
Marsh
MVP [MS Access]

Thanks for your help - in answer to your question - the database is for a
pupil I teach - it has only 4 DVDs on the tblLoan. One has been returned. I
will try to post a copy of the SQL statement.

If it helps, I have used New Query in design view. The pupil has
successfully created a query to show only those DVDs with are overdue. That
was based on tblLoan (for the date and return) and tblCustomer for the
person's name and telephone number to contact them if they had an overdue DVD.

When I tried to help her set up a query to produce a report of which DVDs
are available I hit this snag. Using tblDVD alone was fine - as soon as I
added tblLoan to the grid in design view only the four DVDs on that table
showed when I ran the query. Eventually it did run, but as I said everything
was exactly duplicated three further times, giving all the information 4
times in total. I wonder whether there is something wrong in the way the
pupil has set up the tables. (Certainly none of the forms worked.) I have
looked at the design view of the tables, field lengths and types match. This
is now getting a bit complicated to explain, so will finish there.
Many thanks for your help.
Click to expand...
Click to expand...
 
By adding those fields (with a name that Access can deduce a
relationship), you stimulated Access to create that Join
line for you. So it automatically did the first part of
what I was trying to explain before.

Your new issue is caused by the fact that Access
automatically created an INNER JOIN when it appears that you
want an Outer Join. It tried to explain about selecting a
different joint type by right clicking on the join line.
Try it before you make a bigger mess of the Loans table by
adding more irrelevant records.
 
OK Many thanks - I'll try it tomorrow - bit busy today.
KG

Marshall Barton said:
By adding those fields (with a name that Access can deduce a
relationship), you stimulated Access to create that Join
line for you. So it automatically did the first part of
what I was trying to explain before.

Your new issue is caused by the fact that Access
automatically created an INNER JOIN when it appears that you
want an Outer Join. It tried to explain about selecting a
different joint type by right clicking on the join line.
Try it before you make a bigger mess of the Loans table by
adding more irrelevant records.
--
Marsh
MVP [MS Access]

Thank you so much for your help - I'm fairly sure I understand what you mean.
I will have another look at it in the morning and try incorporating your
advice.

Eventually, I remade the tblLoan, though and the queries and have not been
able to replicate this 160 record phenomenon. I created a Primary Key in
tblDVD which then became my Foreign Key in tblLoan. I then found that only
those 4 DVDs on the loan table would appear on the query when tblLoan was
present in the Select query in design view, regardless of whether any fields
were selected from it or not. I overcame this problem by adding all the DVDs
to tblLoan, but not dates for their issue and/or return and then the query
showed all the DVDs, when I added the dates and whether they were loaned out
or returned.

This is sufficient for the pupil's needs. Access continues to be a program
which stretches the mind...

Many thanks - KG
Click to expand...
Click to expand...
 
Back
Top