Duplicate records generated in query

[Try Hard]

I have a query based on another query and a table, when I run the new query
it provides the information I asked for, but it duplicates that record. Why
is this and can this be overcome.

Thanks for anyones help.

 

[John W. Vinson/MVP]

I have a query based on another query and a table, when I run the new query
it provides the information I asked for, but it duplicates that record.
Why
is this and can this be overcome.

Presumably because you're joining one table (the one where you're seeing the
duplicates) with another table which has two records for each value of the
joining field.

Since we know nothing about the structure of your tables or the nature of
the query it's hard to say anything other than "fix the query". If you would
like help doing so please open the query in SQL view, post the SQL text here
and indicate which fields are duplicated.

 

[Try Hard]

John
Thanks for your comments, i have inculded the SQL view of the query below:

SELECT [WI Competency Table].[Position Name], [WI Competency Table].[WI
Description], [WI Competency Table].[Employee Name], [WI Competency
Table].[Date Trained], [Work Instruction].[Position Name], [Work
Instruction].[WI Description]
FROM [WI Competency Table] INNER JOIN [Work Instruction] ON [WI Competency
Table].[WI Description] = [Work Instruction].[WI Description];

In the WI Competency table the records are correct as entered in the form.

The date field has been duplicated for any Employee Name and Position which
has the same Work Instruction allocated to it.

Hope this helps.

 

[John W. Vinson/MVP]

John
Thanks for your comments, i have inculded the SQL view of the query below:

SELECT [WI Competency Table].[Position Name], [WI Competency Table].[WI
Description], [WI Competency Table].[Employee Name], [WI Competency
Table].[Date Trained], [Work Instruction].[Position Name], [Work
Instruction].[WI Description]
FROM [WI Competency Table] INNER JOIN [Work Instruction] ON [WI Competency
Table].[WI Description] = [Work Instruction].[WI Description];

In the WI Competency table the records are correct as entered in the form.

The date field has been duplicated for any Employee Name and Position
which
has the same Work Instruction allocated to it.

That is precisely how relational queries are designed to work.

Each record in [WI Constancy Table] will be combined with every matching
record in the [Work Instruction] table. Every field you select from the
first table will be displayed as many times as there are matching records.
If there are five matching records on [WI Description] you will see it five
times, once for each [Work instruction] record.

I'm wondering if the Description field is appropriate! How are the tables
related in the Relationships window?

 

[Try Hard]

John
The relationships are as follows;
WI Competency tables field WI Description is joined with the Position tables
field WI Description.

What I would like is to only show those records that i have entered a date
in, and those I have not entered a date should still be there but the date
field will be blank.

John
Thanks for your comments, i have inculded the SQL view of the query below:

SELECT [WI Competency Table].[Position Name], [WI Competency Table].[WI
Description], [WI Competency Table].[Employee Name], [WI Competency
Table].[Date Trained], [Work Instruction].[Position Name], [Work
Instruction].[WI Description]
FROM [WI Competency Table] INNER JOIN [Work Instruction] ON [WI Competency
Table].[WI Description] = [Work Instruction].[WI Description];

In the WI Competency table the records are correct as entered in the form.

The date field has been duplicated for any Employee Name and Position
which
has the same Work Instruction allocated to it.

That is precisely how relational queries are designed to work.

Each record in [WI Constancy Table] will be combined with every matching
record in the [Work Instruction] table. Every field you select from the
first table will be displayed as many times as there are matching records.
If there are five matching records on [WI Description] you will see it five
times, once for each [Work instruction] record.

I'm wondering if the Description field is appropriate! How are the tables
related in the Relationships window?>

 

[John W. Vinson]

What I would like is to only show those records that i have entered a date
in, and those I have not entered a date should still be there but the date
field will be blank.

You may want to change the INNER JOIN - which requires a record in both tables
- to a LEFT JOIN, which will show all the records but will leave NULLs if
there is no matching record:.

SELECT [WI Competency Table].[Position Name], [WI Competency Table].[WI
Description], [WI Competency Table].[Employee Name], [WI Competency
Table].[Date Trained], [Work Instruction].[Position Name], [Work
Instruction].[WI Description]
FROM [WI Competency Table] LEFT JOIN [Work Instruction] ON [WI Competency
Table].[WI Description] = [Work Instruction].[WI Description];

This won't stop duplicate records, and it's still not clear to me which
table's records are being displayed in duplicate.

I'm leaving town for a family trip tomorrow so may not be able to reply - if
any other volunteers want to jump in please do, or you might want to repost
with sample data.