How to get a Distinct value for a field...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to get a Distinct value for a field...

Author

Topic

speedy_gonzalos
Starting Member

10 Posts

Posted - 2009-06-02 : 06:11:08

Hi,

I have the following query in Access 2000 Reports:

SELECT DISTINCT [Orders].[ShippingEnteredDate]
, [Orders].[DealerID]
, [Orders].[OrderID]
, [Orders].[OrderDate]
, [Orders].[PurchaseOrderNumber]
, [Orders].[CustomerRef]
, [Orders].[ShipName]
, [Orders].[ShipDate]
, [Orders].[TrackingNumber]
, [Orders].[MethodofPayment]
, [Orders].[Amount]
, [Orders].[CurrencyType]
, [Orders].[EnquiryID]
, [OrderDetails].[SerialNumber]
, [Orders].[SaleType]
, [Orders].[OrderDespatchedFrom] AS DespatchedFrom
FROM Orders LEFT JOIN OrderDetails ON [Orders].[OrderID]=[OrderDetails].[OrderID]
WHERE ((([Orders].[ShippingEnteredDate]) Between @startdate And @enddate) And (([Orders].[Cancelled])<>1))
ORDER BY [Orders].[OrderID], [Orders].[OrderDate] DESC;

I need to output the Total for each SaleType and then a Grand Total for the whole report.
The problem that i have is that the Amount field is like the total value for an OrderID, the above query outputs the Amount value for each order line item, which will always be the same for each OrderID. When i sum the Amount field it multiplies the Amount value by the number of order lines that OrderID has.

For example if an OrderID has 2 order lines, the total for that OrderID is the vlaue in the Amount field. So for this example, if i say the Amount value is 200, then when i sum the Amount in the report it will output 200*2 = 400 for that OrderID. BUT the answer should be 200 not 400.

If i do distinct it doesnt work as the serial number is not unique for each OrderID. If i take the serial number field out then it works fine, however i need this field in the report as it outputs this field.

I have been going round and round in circles, so please any help would be most appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 06:26:36

But how will your report show more than one SerialNumber?
If records are repeated for every matching OrderDetails - is it possible not to take the amount from Orders and instead get the amount by sum() each value in OrderDetails?

No, you're never too old to Yak'n'Roll if you're too young to die.

speedy_gonzalos
Starting Member

10 Posts

Posted - 2009-06-02 : 07:06:12

In the report there are several Groups:
1- Sale Type
2- Shipping Entered Date
3- OrderId

The 3rd group, OrderID is displayed as follows:
OrderID Header - DealerID, OrderID, orderDate, EnquiryID, ShipDate, ShipName, MethodOfPayment, Amount
OrderID Details - SerialNumber, DispatchedFrom

So as you can see from the OrderID group, the Amount is only displayed once for each OrderID in the report.
Since i need to get a total for each SaleType, if i do sum(Amount), the query will sum the Amount field the number of times that OrderID is displayed in the query.
Since the OrderID can be displayed several times as there can be more than one orderline for any order, it returns an incorrect value.

Generally the Amount/Total of an order is a calculated field making it much easier. But in this case it varies from order to order and is dependant on the price that was agreed with the customer at the time the sale was made.

Hope this clarifies things abit...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources