Query results in duplicate data

[Guest]

Hello:
I have got a annoying problem. I ran a query that has columns named
ID, Sales Order No.,Selling Date, Product ID, Item description, Selling
Zone. In the table only Product ID has unique value others have multiple
values. Each time I ran a query some of the product ID starts showing
duplicate value. Though I selected unique value from properties the problem
still occurs. What can I do to delete duplicate values. I tested the query
several times and seen that if I add Selling date the problem shows up. If I
dont add selling date there is no problem. Any one please help.

Thanking you in advance

 

[Jamie Collins]

I have got a annoying problem. I ran a query that has columns named
ID, Sales Order No.,Selling Date, Product ID, Item description, Selling
Zone. In the table only Product ID has unique value others have multiple
values. Each time I ran a query some of the product ID starts showing
duplicate value. Though I selected unique value from properties the problem
still occurs. What can I do to delete duplicate values. I tested the query
several times and seen that if I add Selling date the problem shows up. If I
dont add selling date there is no problem.

Perhaps your DATETIME values have time elements other than midnight?

Compare the following two queries (hint: the only difference is the
position of the DISTINCT keyword) using Northwind (or other mdb with a
populated Products table):

Query 1:

SELECT FORMAT(DT2.datetime_value, 'yyyy-mm-dd') AS
datetime_value_formatted
FROM
(
SELECT DISTINCT DT1.datetime_value AS datetime_value
FROM
(
SELECT DATE() AS datetime_value
FROM Products
UNION ALL
SELECT NOW()
FROM Products
) AS DT1
) AS DT2;

Query 2:

SELECT DISTINCT FORMAT(DT2.datetime_value, 'yyyy-mm-dd') AS
datetime_value_formatted
FROM
(
SELECT DT1.datetime_value AS datetime_value
FROM
(
SELECT DATE() AS datetime_value
FROM Products
UNION ALL
SELECT NOW()
FROM Products
) AS DT1
) AS DT2;

Jamie.

--

 

[Guest]

Thanks. But one problem...I am a new comer in this vast field of MS Access. I
do not know how to use SQL command at all. Is there any other way? I can not
change date format it in the source file. As there are 8000 rows. Can you
guide me to query in the design mode so that duplicate data do not appear?
Please help.......

 

[Jamie Collins]

Can you
guide me to query in the design mode

Not I.

Jamie.

--

 

[John W. Vinson]

Hello:
I have got a annoying problem. I ran a query that has columns named
ID, Sales Order No.,Selling Date, Product ID, Item description, Selling
Zone. In the table only Product ID has unique value others have multiple
values. Each time I ran a query some of the product ID starts showing
duplicate value. Though I selected unique value from properties the problem
still occurs. What can I do to delete duplicate values. I tested the query
several times and seen that if I add Selling date the problem shows up. If I
dont add selling date there is no problem. Any one please help.

Jamie suggested that your [Selling Date] field might have a time component -
i.e. Selling Date might be #7/28/2007 11:43:12am# (even if you have it
formatted to display just the date).

Try removing Selling Date from the query grid, and replacing it with a
calculated field:

JustTheDate: DateValue([Selling Date])

and group by this field instead.

You will of course get one record for each day. If you just want to use the
date as a query criterion, use the "Where" operator on the Totals line - you
won't see the date in the query results but it can still be used with a date
or date range criterion.

If you're having problems please open the query in design view, and select
View... SQL from the menu; copy and paste the SQL string to a message here.

John W. Vinson [MVP]