Duplicate Info in Query

[Joy Rose]

How do I delete duplicate info in a query? i.e. -- query lists Order date,
Customer #, Customer Name, Customer Address, Customer Invoice #, Product ID,
Customer Sales Dollars, customer payments.


If the customer purchased more than one product, all info is listed as
below:

10/9/04(Order Date) #7(Customer # John Smith(Customer Name) PO 222 South
Forks, ID(Address) #10(Invoice #), Product 601DV(Product ID), $50,000.00
(Sales), $2000.00 (payments)

10/9/04(Order Date) #7(Customer # John Smith(Customer Name) PO 222 South
Forks, ID(Address) #10(Invoice #), Product 999(Product ID), $50,000.00
(Sales), $2000.00 (payments)

10/9/04(Order Date) #7(Customer # John Smith(Customer Name) PO 222 South
Forks, ID(Address) #10(Invoice #), Product XXX(Product ID), $50,000.00
(Sales), $2000.00 (payments)

The way it appears now looks as if the customer ordered $150,000.00 (50,000
X 3 line items) and made $6000 (2000 X 3) in payments instead of $50,000
total sales and $2000 total payments.



What I would like to appear is:

10/9/04(Order Date) #7(Customer #) John Smith(Customer Name) PO 222
South Forks, ID(Address) #10(Invoice #), Product 60lDV(Product ID),

Product 999

Product XXX
$50,000.00 (Sales), $2000.00 (payments)


Can this be done with a query? I am using query as this needs to be
exported to excel.

Thank you,

 

[SteveS]

You don't want to delete info, just hide duplicate info.
Check help for DISTINCTROW. Read about DISTINCT and DISTINCTROW Predicates
to decide which one to use.

In SQL view of the query, try changing 'Select' to 'Select Distinct' (or
it might be Select Distinctrow) and run the query.

HTH
Steve

 

[Joy Rose]

I looked at info on DISTINCTROW and DISTINCT; however, when I attempted to
insert DISTINCT Order.OrderID after SELECT, I get error message that it is
not a valid SQL statement and that it was expecting something else or if I
insert ; I get "there are characters after the SQL statement."

When I put a , after DISTINCT Order.OrderID, I get a parameter value field
when running query. I input a number (representing an invoice #) and this
returned another field labeled Orders.OrderID with the number listed after
each of the orders. Thus, I created another field instead of eliminating
duplicates.

I have many many fields in the query.

What am I missing?

Thank you

 

[SteveS]

Would you post the SQL of your query?

If the query is giving you the results you want, you *should* just have to
add DISTINCT after SELECT to eliminate duplicat lines.

Steve

 

[Joy Rose]

SELECT Orders.OrderDate, Customers.[Account#], Products.[Contract#],
Orders.PurchaseOrderNumber, Orders.ShipName, Orders.ShipAddress,
Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipPostalCode,
Orders.ShipCountry, Orders.ShipPhoneNumber, Orders.ShipDate,
Orders.FreightCharge, Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.Country,
Customers.ContactTitle, Customers.PhoneNumber, Customers.FaxNumber,
Customers.Notes, [Order Details].LineItem, [Order Details].Quantity,
Products.ProductCode, Products.ProductName, [Order Details].[Serial#],
[Order Details].UnitPrice, [Order Details]!Quantity*[Order
Details]!UnitPrice AS [Total Price], [Shipping Methods].ShippingMethod,
[Shipping Methods].[Shipping#], [Shipping Methods].ShippingAccount,
[Shipping Methods].ShippingType, [Shipping Methods].ShippingCity, [Shipping
Methods].ShippingState
FROM (Customers INNER JOIN ([Shipping Methods] INNER JOIN Orders ON
[Shipping Methods].ShippingMethodID = Orders.ShippingMethodID) ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
ORDER BY [Order Details].LineItem;


I am getting the results, but with duplicates.

Thank you.

 

[SteveS]

I didn't read close enough last night. :-( It looked like one line was
repeating three times but I now see that there is one field (product ID) that
is different. So the line that are returned is already 'Distinct'.

Easy if this was for a report, just use a group header for the customer
info, the Detail section would hold the Product IDs and the group footer
would have the Sales and Payments.

Maybe a crosstab or pivot query would work???

Since you want the data to go to Excel, if I (now) understand you right, you
want (all of) the customer info, then all of the product IDs, then the Sales,
then the payments; all on one line.

Or else you want (all of) the customer info on one line, then a new line for
each of the product IDs, then a line for the Sales and Payments.

In either case, the only way I can think of to do this is to use code to
create a recordset using the query, looping thru the recordset to create a
text string (adding a tab char between each field), output the string to a
text file.

Maybe one of the SQL wizards would be able to modify the query to do what
you want???

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

sanfu at techie dot com

SELECT Orders.OrderDate, Customers.[Account#], Products.[Contract#],
Orders.PurchaseOrderNumber, Orders.ShipName, Orders.ShipAddress,
Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipPostalCode,
Orders.ShipCountry, Orders.ShipPhoneNumber, Orders.ShipDate,
Orders.FreightCharge, Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.Country,
Customers.ContactTitle, Customers.PhoneNumber, Customers.FaxNumber,
Customers.Notes, [Order Details].LineItem, [Order Details].Quantity,
Products.ProductCode, Products.ProductName, [Order Details].[Serial#],
[Order Details].UnitPrice, [Order Details]!Quantity*[Order
Details]!UnitPrice AS [Total Price], [Shipping Methods].ShippingMethod,
[Shipping Methods].[Shipping#], [Shipping Methods].ShippingAccount,
[Shipping Methods].ShippingType, [Shipping Methods].ShippingCity, [Shipping
Methods].ShippingState
FROM (Customers INNER JOIN ([Shipping Methods] INNER JOIN Orders ON
[Shipping Methods].ShippingMethodID = Orders.ShippingMethodID) ON
Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID) ON
Orders.OrderID = [Order Details].OrderID
ORDER BY [Order Details].LineItem;


I am getting the results, but with duplicates.

Thank you.

Would you post the SQL of your query?

If the query is giving you the results you want, you *should* just have to
add DISTINCT after SELECT to eliminate duplicat lines.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

sanfu at techie dot com