How do we get a unique record from sql server relating to a primary key without any duplicate on the report, I have been using the code below but if the user creates two or three lines with the same product name , unit price and total only one record will appear instead of all. Why it does not follow the primary key? For example CustomerInvoiceID is unique so how do I make sure that the foreign keys with three lines created for the same product can be treated as unique so that those lines are not left out.
Users in many times tend to create 4, 5,8 relating to one thing especially when using the POS (point of sales) , and so with the code below it will only pick one line and considers others as duplicates which is not correct. Access allow to use unique record not just values or Distinct Row
Users in many times tend to create 4, 5,8 relating to one thing especially when using the POS (point of sales) , and so with the code below it will only pick one line and considers others as duplicates which is not correct. Access allow to use unique record not just values or Distinct Row
Code:
SELECT DISTINCT
tblCustomers.Company, tblCustomerInvoice.InvoiceID, tblCustomerInvoice.ShipDate, tblCustomerInvoice.Warehouse, tblEfdReceipts.InvoiceNumber, (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
(([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - ((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0),
(([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), ((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) * COALESCE ([Discount], 0)) - ((IIf((COALESCE ([RRP], 0) > [UnitPrice]),
((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))))) * [Quantity])) * [tblCustomerInvoice].[FCRate]) + ((([Quantity] * [UnitPrice])
* COALESCE ([Discount], 0)) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity]) * COALESCE ([Discount], 0))) AS Revenue, ((((IIf((RRP > UnitPrice), ((RRP * COALESCE (VAT, 0)) / (1 + COALESCE (VAT, 0))), ((UnitPrice * COALESCE (VAT,
0)) / (1 + COALESCE (VAT, 0))))) * Quantity)) * tblCustomerInvoice.FCRate) AS FinalVat, ((((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity]) - (((((([UnitPrice]) / (1 + COALESCE ([VAT], 0))) + (IIf([UnitPrice] > COALESCE ([RRP], 0), (([UnitPrice] * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0))),
((COALESCE ([RRP], 0) * COALESCE ([VAT], 0)) / (1 + COALESCE ([VAT], 0)))))) * [Quantity])) * COALESCE ([Discount], 0))) + (([Quantity] * [UnitPrice]) * COALESCE ([Discount], 0)))) - ((([UnitPrice] / (1 + COALESCE ([VAT], 0))) * [Quantity])
* COALESCE ([Discount], 0)) AS Totals, tblCustomerInvoice.InvoiceID AS SNR, tblCustomerInvoice.CreatedBy, ((IIf(((RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) > ((UnitPrice / ((1 + (COALESCE (VAT, 0)
+ COALESCE (TourismLevy, 0))))))), (RRP / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * (COALESCE (VAT, 0)), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * VAT)) * Quantity)
* COALESCE (tblCustomerInvoice.FCRate, 0) AS ExVATTax, (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) * Quantity) AS NetSales, ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))) AS UnitNetTax,
((IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity), (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))
* Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) * COALESCE (TourismLevy, 0))) AS FinTourism, ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))) AS SellingPrice, (RRP / ((1 + (COALESCE (VAT, 0)
+ COALESCE (TourismLevy, 0))))) AS RRPADjust, IIf((TaxClassA = 'I'), ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity),
(((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0))))) * Quantity) * COALESCE (tblCustomerInvoice.FCRate, 0))) AS ActualPrice, tblCustomerInvoice.FCRate, tblCustomerInvoice.Cashier, ((IIf((TaxClassA = 'i'),
((UnitPrice / (1 + Insurance))), 0)) * Insurance) AS InsuranceVDP, tblLineDetails.TaxClassA, ((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax,
0))) AS TaxableTOT, tblLineDetails.Quantity, tblLineDetails.TurnoverTax, (COALESCE (((COALESCE (((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), 0) * COALESCE (Quantity, 0)) / (1 + COALESCE (TurnoverTax, 0))),
0) * COALESCE (TurnoverTax, 0)) AS TOTTax, ((IIf((TaxClassA = 'BG'), ((Quantity * UnitPrice)), 0)) / (1 + Bettings)) AS GameVDP, tblLineDetails.UnitPrice, tblLineDetails.Bettings, ((((IIf((TaxClassA = 'BG'), ((COALESCE (Quantity, 0)
* COALESCE (UnitPrice, 0))), 0)) / (1 + COALESCE (Bettings, 0)))) * COALESCE (Bettings, 0)) AS BettingTaxes, ((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance)))))
* Quantity) AS InsuranceVPD, (((IIf((TaxClassA <> 'I'), ((UnitPrice / ((1 + (COALESCE (VAT, 0) + COALESCE (TourismLevy, 0)))))), ((UnitPrice / (1 + Insurance))))) * Quantity) * COALESCE (insurance, 0)) AS Premiums, tblLineDetails.Insurance,
((IIf((ExciseClass = 'E'), (((Quantity * UnitPrice) / (1 + duty)) / (1 + Vat)), 0)) * Duty) AS ExcisePayable, tblLineDetails.Duty, tblLineDetails.VAT, tblLineDetails.ExciseClass, tblLineDetails.Discount, tblLineDetails.TourismLevy, tblLineDetails.RRP,
tblCustomerInvoice.StatusOne
FROM ((tblCustomers INNER JOIN
tblCustomerInvoice ON tblCustomers.CustomerID = tblCustomerInvoice.CustomerID) INNER JOIN
(tblProducts INNER JOIN
tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN
tblEfdReceipts ON tblCustomerInvoice.InvoiceID = tblEfdReceipts.INVID;
Last edited: