Duplication of information on some information

J

jrp444

When I run the below query I get duplicate information on some of the
accounts that it pulls up. Can anybody tell me what I need to do to stop
this duplication?

SELECT [RepoData Query].[C/O], [RepoData Query].LoanNumber, [RepoData
Query]![First Name] & ' ' & [RepoData Query]![Last Name] AS Name, [RepoData
Query]!Year & ' ' & [RepoData Query]![Vehicle Make /Collateral] & ' ' &
[RepoData Query]![Vehicle Model] AS Vehicle, [RepoData Query]!Date AS [Sales
Date], [RepoData Query].Auction, [RepoData Query].[Loan Bal], [RepoData
Query].[Floor Amt], [RepoData Query].[Sales Amount], [RepoData
Query].[Auction 1], [RepoData Query].[Auction Date 1], [RepoData Query].[High
Bid Amt 1], [RepoData Query].[Sold 1], [RepoData Query].[Auction 2],
[RepoData Query].[Auction Date 2], [RepoData Query].[High Bid Amt 2],
[RepoData Query].[Sold 2], [RepoData Query].[Auction 3], [RepoData
Query].[Auction Date 3], [RepoData Query].[High Bid Amt 3], [RepoData
Query].[Sold 3], [RepoData Query].[Floor Amt], [RepoData Query].[Vehicle
Value], [RepoData Query].Comments, [RepoData Query].[Dealer No], [RepoData
Query].Mileage, [RepoData Query].[Vehicle Condition], Dates.Date, Dates.Date1
FROM Dates, [RepoData Query]
WHERE ((([RepoData Query].[Sales Amount]) Is Not Null) AND (([RepoData
Query]![Date]) Between [Dates]![Date] And [Dates]![Date1]))
ORDER BY [RepoData Query].LoanNumber;
 
J

John Spencer

Try adding DISTINCT to the query.

SELECT DISTINCT [RepoData Query].[C/O], ...

If that doesn't work, post back with an explanation as to the reason you are
using the Date table in your query. I'm guessing that it contains multiple
date ranges, but that is a guess. If it does contain multiple date ranges,
do any of the ranges overlap? If so, you could get duplicates returned
because a record falls into multiple ranges. Or you could have two ranges
entered that are identical in your Dates table.

If you have a RepoData record with a Date of 12/1/2005 and your Dates table
has two records with date and date1 fields of
11/1/2005 to 12/1/2005 and 12/1/2005 to 1/1/2006, that would cause the Repo
data to be returned twice for the record with a date of 12/1/2005
 
J

jrp444

I added DISTINCT as you suggested, but still I get some duplications in the
data. The Date that you see in the query sets up a range. Date is the
beginning date and Dates1 is the ending date. I use it in my macros so they
can reset the date range of the information that they want.

John Spencer said:
Try adding DISTINCT to the query.

SELECT DISTINCT [RepoData Query].[C/O], ...

If that doesn't work, post back with an explanation as to the reason you are
using the Date table in your query. I'm guessing that it contains multiple
date ranges, but that is a guess. If it does contain multiple date ranges,
do any of the ranges overlap? If so, you could get duplicates returned
because a record falls into multiple ranges. Or you could have two ranges
entered that are identical in your Dates table.

If you have a RepoData record with a Date of 12/1/2005 and your Dates table
has two records with date and date1 fields of
11/1/2005 to 12/1/2005 and 12/1/2005 to 1/1/2006, that would cause the Repo
data to be returned twice for the record with a date of 12/1/2005


jrp444 said:
When I run the below query I get duplicate information on some of the
accounts that it pulls up. Can anybody tell me what I need to do to stop
this duplication?

SELECT [RepoData Query].[C/O], [RepoData Query].LoanNumber, [RepoData
Query]![First Name] & ' ' & [RepoData Query]![Last Name] AS Name,
[RepoData
Query]!Year & ' ' & [RepoData Query]![Vehicle Make /Collateral] & ' ' &
[RepoData Query]![Vehicle Model] AS Vehicle, [RepoData Query]!Date AS
[Sales
Date], [RepoData Query].Auction, [RepoData Query].[Loan Bal], [RepoData
Query].[Floor Amt], [RepoData Query].[Sales Amount], [RepoData
Query].[Auction 1], [RepoData Query].[Auction Date 1], [RepoData
Query].[High
Bid Amt 1], [RepoData Query].[Sold 1], [RepoData Query].[Auction 2],
[RepoData Query].[Auction Date 2], [RepoData Query].[High Bid Amt 2],
[RepoData Query].[Sold 2], [RepoData Query].[Auction 3], [RepoData
Query].[Auction Date 3], [RepoData Query].[High Bid Amt 3], [RepoData
Query].[Sold 3], [RepoData Query].[Floor Amt], [RepoData Query].[Vehicle
Value], [RepoData Query].Comments, [RepoData Query].[Dealer No], [RepoData
Query].Mileage, [RepoData Query].[Vehicle Condition], Dates.Date,
Dates.Date1
FROM Dates, [RepoData Query]
WHERE ((([RepoData Query].[Sales Amount]) Is Not Null) AND (([RepoData
Query]![Date]) Between [Dates]![Date] And [Dates]![Date1]))
ORDER BY [RepoData Query].LoanNumber;
Click to expand...
Click to expand...
 
J

John Spencer

As a trouble shooting technique, remove the dates table from the query and
hard code the range. Do you still get duplicates? If so, then you need to
figure out what data is in RepoData that is causing the duplicate records.

Did you check and make sure you only have one record in the Dates table?


jrp444 said:
I added DISTINCT as you suggested, but still I get some duplications in the
data. The Date that you see in the query sets up a range. Date is the
beginning date and Dates1 is the ending date. I use it in my macros so
they
can reset the date range of the information that they want.

John Spencer said:
Try adding DISTINCT to the query.

SELECT DISTINCT [RepoData Query].[C/O], ...

If that doesn't work, post back with an explanation as to the reason you
are
using the Date table in your query. I'm guessing that it contains
multiple
date ranges, but that is a guess. If it does contain multiple date
ranges,
do any of the ranges overlap? If so, you could get duplicates returned
because a record falls into multiple ranges. Or you could have two
ranges
entered that are identical in your Dates table.

If you have a RepoData record with a Date of 12/1/2005 and your Dates
table
has two records with date and date1 fields of
11/1/2005 to 12/1/2005 and 12/1/2005 to 1/1/2006, that would cause the
Repo
data to be returned twice for the record with a date of 12/1/2005


jrp444 said:
When I run the below query I get duplicate information on some of the
accounts that it pulls up. Can anybody tell me what I need to do to
stop
this duplication?

SELECT [RepoData Query].[C/O], [RepoData Query].LoanNumber, [RepoData
Query]![First Name] & ' ' & [RepoData Query]![Last Name] AS Name,
[RepoData
Query]!Year & ' ' & [RepoData Query]![Vehicle Make /Collateral] & ' ' &
[RepoData Query]![Vehicle Model] AS Vehicle, [RepoData Query]!Date AS
[Sales
Date], [RepoData Query].Auction, [RepoData Query].[Loan Bal], [RepoData
Query].[Floor Amt], [RepoData Query].[Sales Amount], [RepoData
Query].[Auction 1], [RepoData Query].[Auction Date 1], [RepoData
Query].[High
Bid Amt 1], [RepoData Query].[Sold 1], [RepoData Query].[Auction 2],
[RepoData Query].[Auction Date 2], [RepoData Query].[High Bid Amt 2],
[RepoData Query].[Sold 2], [RepoData Query].[Auction 3], [RepoData
Query].[Auction Date 3], [RepoData Query].[High Bid Amt 3], [RepoData
Query].[Sold 3], [RepoData Query].[Floor Amt], [RepoData
Query].[Vehicle
Value], [RepoData Query].Comments, [RepoData Query].[Dealer No],
[RepoData
Query].Mileage, [RepoData Query].[Vehicle Condition], Dates.Date,
Dates.Date1
FROM Dates, [RepoData Query]
WHERE ((([RepoData Query].[Sales Amount]) Is Not Null) AND (([RepoData
Query]![Date]) Between [Dates]![Date] And [Dates]![Date1]))
ORDER BY [RepoData Query].LoanNumber;
Click to expand...
Click to expand...
Click to expand...
 
J

jrp444

It was the Date table that was causing the problem. I had more than one date
in the table. For some reason it had not cleared itself out during the
delete process. Thanks, I do appreciate all the help.

John Spencer said:
As a trouble shooting technique, remove the dates table from the query and
hard code the range. Do you still get duplicates? If so, then you need to
figure out what data is in RepoData that is causing the duplicate records.

Did you check and make sure you only have one record in the Dates table?


jrp444 said:
I added DISTINCT as you suggested, but still I get some duplications in the
data. The Date that you see in the query sets up a range. Date is the
beginning date and Dates1 is the ending date. I use it in my macros so
they
can reset the date range of the information that they want.

John Spencer said:
Try adding DISTINCT to the query.

SELECT DISTINCT [RepoData Query].[C/O], ...

If that doesn't work, post back with an explanation as to the reason you
are
using the Date table in your query. I'm guessing that it contains
multiple
date ranges, but that is a guess. If it does contain multiple date
ranges,
do any of the ranges overlap? If so, you could get duplicates returned
because a record falls into multiple ranges. Or you could have two
ranges
entered that are identical in your Dates table.

If you have a RepoData record with a Date of 12/1/2005 and your Dates
table
has two records with date and date1 fields of
11/1/2005 to 12/1/2005 and 12/1/2005 to 1/1/2006, that would cause the
Repo
data to be returned twice for the record with a date of 12/1/2005


When I run the below query I get duplicate information on some of the
accounts that it pulls up. Can anybody tell me what I need to do to
stop
this duplication?

SELECT [RepoData Query].[C/O], [RepoData Query].LoanNumber, [RepoData
Query]![First Name] & ' ' & [RepoData Query]![Last Name] AS Name,
[RepoData
Query]!Year & ' ' & [RepoData Query]![Vehicle Make /Collateral] & ' ' &
[RepoData Query]![Vehicle Model] AS Vehicle, [RepoData Query]!Date AS
[Sales
Date], [RepoData Query].Auction, [RepoData Query].[Loan Bal], [RepoData
Query].[Floor Amt], [RepoData Query].[Sales Amount], [RepoData
Query].[Auction 1], [RepoData Query].[Auction Date 1], [RepoData
Query].[High
Bid Amt 1], [RepoData Query].[Sold 1], [RepoData Query].[Auction 2],
[RepoData Query].[Auction Date 2], [RepoData Query].[High Bid Amt 2],
[RepoData Query].[Sold 2], [RepoData Query].[Auction 3], [RepoData
Query].[Auction Date 3], [RepoData Query].[High Bid Amt 3], [RepoData
Query].[Sold 3], [RepoData Query].[Floor Amt], [RepoData
Query].[Vehicle
Value], [RepoData Query].Comments, [RepoData Query].[Dealer No],
[RepoData
Query].Mileage, [RepoData Query].[Vehicle Condition], Dates.Date,
Dates.Date1
FROM Dates, [RepoData Query]
WHERE ((([RepoData Query].[Sales Amount]) Is Not Null) AND (([RepoData
Query]![Date]) Between [Dates]![Date] And [Dates]![Date1]))
ORDER BY [RepoData Query].LoanNumber;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL or maybe a hardware issue for finding lowest Repair order date or mileage 4
SQL or maybe a hardware issue for finding lowest Repair order date or mileage 1
Sum a count total 4
Help with Duplicate Output Alias Error 'Exp1' 0
Find duplicates query 4
Problem Sequentially Numbering Records 4
Transpose data in a report-Field values broken down to separate co 9
Last Date Query 1

Top