adding freight amount to total

[JMS]

Greetings
I am trying to add the freight from a purchase along with the unit price and
sales tax using Sum. When I add my freight amount, it multiplies the total
freight amount by the number of items in the purchase. Below is the
expression I am using in my query...can anyone please help...I have been
racking my brain. (Note: I'm using the ORDER ENTRY Template.)

Total:
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100)+Sum(CLng(nz([Freight])*100)/100)


TIA

 

[John W. Vinson]

Greetings
I am trying to add the freight from a purchase along with the unit price and
sales tax using Sum. When I add my freight amount, it multiplies the total
freight amount by the number of items in the purchase. Below is the
expression I am using in my query...can anyone please help...I have been
racking my brain. (Note: I'm using the ORDER ENTRY Template.)

Total:
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100)+Sum(CLng(nz([Freight])*100)/100)

Don't Sum it. Just remove the Sum() around the freight calculation; you want
to add it only once, not once per line item!

John W. Vinson [MVP]

 

[JMS]

Can you please show an example of what you mean. I am getting the error
about executing a query that does not include a specific expression.

Thank you

 

[John W. Vinson]

Can you please show an example of what you mean. I am getting the error
about executing a query that does not include a specific expression.

No; because I don't know the structure of your tables, the expression you're
trying to use, or the error message you're getting.

Please post the SQL view of your query and the actual error message you're
getting.

John W. Vinson [MVP]

 

[JMS]

No errors this way it just multiplies freight...
SELECT Orders.OrderID,
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100)+Sum(CLng(nz([FreightCharge])*100)/100)
AS [Gross Order Total]
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID;

 

[John W. Vinson]

No errors this way it just multiplies freight...

Change the Sum on freight to First to select just one instance of the freight:

No errors this way it just multiplies freight...
SELECT Orders.OrderID,
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100)+First(CLng(nz([FreightCharge])*100)/100)
AS [Gross Order Total]
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID;

This assumes that there is just one freight charge for an order, and that it's
the same in all the records. Note that I *STILL* don't know the structure of
your table, nor which table [FreightCharge] might be in.

John W. Vinson [MVP]

 

[JMS]

Sorry John, I just assumed that since I made a Note in my first post that is
was the Order Entry template.....you knew the structure already. HTH

No errors this way it just multiplies freight...

Change the Sum on freight to First to select just one instance of the
freight:

No errors this way it just multiplies freight...
SELECT Orders.OrderID,
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100)+First(CLng(nz([FreightCharge])*100)/100)
AS [Gross Order Total]
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID;

This assumes that there is just one freight charge for an order, and that
it's
the same in all the records. Note that I *STILL* don't know the structure
of
your table, nor which table [FreightCharge] might be in.

John W. Vinson [MVP]

 

[John W. Vinson]

Sorry John, I just assumed that since I made a Note in my first post that is
was the Order Entry template.....you knew the structure already. HTH

No. I'm not an employee of Microsoft; I'm a self-employed consultant. Just
because I answer questions about databases does not mean that I've downloaded
and studied every template that Microsoft (or anyone else) has made available!

John W. Vinson [MVP]

 

[JMS]

whoa whoa John calm down...I appreciate your help and just thought at some
point you may have checked it out and that it might be easier then a
non-access guru trying to explain. Seriously John, I'm sure this stuff is
old hat to you but I am not a MS access vet and am ultimately just trying to
get a little help form someone who knows better then I. Having said that, we
are getting closer to solving my problem but now my report is off by 1 - 3
cents. Please tell me what you need and I will post it, email it or
whatever.

 

[JMS]

I don't know if this makes any sense but it seems to add a penny or 2 when
the total sales tax of the order is somewhere between $297.73 and $297.81
Before that it is completely accurate.....HELP!!! ANYBODY!!

 

[John W. Vinson]

I don't know if this makes any sense but it seems to add a penny or 2 when
the total sales tax of the order is somewhere between $297.73 and $297.81
Before that it is completely accurate.....HELP!!! ANYBODY!!

If you're using Double or Float datatypes change them to Currency (the
currency *datatype*, on the list with Text, Date/Time, Number etc).

If you're using Currency datatype be aware that a Currency field has four
decimal places - even if the format doesn't show them. Use Round() in the
sales tax calculation:

SalesTax: Round([ItemPrice] * [TaxRate], 2)

prior to summing.

John W. Vinson [MVP]

 

[JMS]

John:
I changed the datatype to currency in my table but I am not doing something
right in the query with the use of Round in the calculation.

SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRound])*100)/100)+First(CLng(nz([FreightCharge])*100)/100)
AS [Order Total]
FROM (Orders INNER JOIN [SalesTaxRound Query] ON Orders.OrderID =
[SalesTaxRound Query].OrderID) LEFT JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID;

----SalesTaxRound Query----
SELECT Round([SalesTaxRate],2) AS SalesTaxRound, Orders.OrderID
FROM Orders
GROUP BY Round([SalesTaxRate],2), Orders.OrderID;


Just wanted to thank you again for taking the time to help.

JMS

I don't know if this makes any sense but it seems to add a penny or 2 when
the total sales tax of the order is somewhere between $297.73 and $297.81
Before that it is completely accurate.....HELP!!! ANYBODY!!

If you're using Double or Float datatypes change them to Currency (the
currency *datatype*, on the list with Text, Date/Time, Number etc).

If you're using Currency datatype be aware that a Currency field has four
decimal places - even if the format doesn't show them. Use Round() in the
sales tax calculation:

SalesTax: Round([ItemPrice] * [TaxRate], 2)

prior to summing.

John W. Vinson [MVP]

 

[John W. Vinson]

John:
I changed the datatype to currency in my table but I am not doing something
right in the query with the use of Round in the calculation.

SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRound])*100)/100)+First(CLng(nz([FreightCharge])*100)/100)
AS [Order Total]
FROM (Orders INNER JOIN [SalesTaxRound Query] ON Orders.OrderID =
[SalesTaxRound Query].OrderID) LEFT JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID;

----SalesTaxRound Query----
SELECT Round([SalesTaxRate],2) AS SalesTaxRound, Orders.OrderID
FROM Orders
GROUP BY Round([SalesTaxRate],2), Orders.OrderID;

Change all your CLng to CCur for starters. You can use Round() rather than the
*100)/100 stuff - are you trying to round to integer dollars or what? You say
you're "not doing something right" - what's wrong? Are you getting error
messages, wrong results, or what???


John W. Vinson [MVP]

 

[JMS]

John
Gross Order Total and Sales tax Total are off by a couple cents. I changed
the Clng to CCur, changed the datatype to currency with 2 decimal places.
Still not sure how to use the Round Function in all this. Maybe with seeing
the structure below you can give me a little more insight. Thanks again.

----Order Details Table----
OrderDetailID - Primary Key - AutoNumber - Long Integer
OrderID - Text
ProductID - Text
Quantity - Number - Double - Decimal 0
UnitPrice - Currency - Format Currency - Decimal 2
CCSPrice - Currency - Format Currency - Decimal 2
Discount - Currency - Format Currency - Decimal 2


----Order Table----
OrderID - Text
CustomerID - Number - Long Integer - Decimal Auto
EmployeeID - Long Integer - Decimal Auto
OrderDate - Date/Time - Short Date
PurchaseOrderNumber - Text
ShipName - Text
ShipAddress - Text
ShipCity - Text
ShipStateOfProvince - Text
ShipPostalCode - Text
ShipCountry - Text
ShipPhoneNumber - Text
ShipDate - Date/Time - Short Date
ShippingmethodID - Number - Long Integer 0 Deciaml Auto
FreightCharge - Currency - Decimal 2
CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
ARDetailID - AutoNumber - Long Integer

----Main Report Query----
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100
AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net
Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS
[Sales Tax Total], Orders.FreightCharge,
Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total],
Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax
Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing,
Orders.OrderDate,
Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100
AS Profit
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge,
Orders.OrderDate
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]));

In the Main Report Query, each record for Gross Order Total is showing 4
numbers to the right of the decimal.


----Main Report----
Record Source - Main Report Query
Filter on - No
Order By On - No
Sorting and Grouping - OrderDate - Ascending
Group Header - No
Group Footer - No
Group on Each Value
Group Interval - 1
Keep Together No

In Report Detail I have Gross Order Total and Sales Tax Total which seem to
calculate fine...I think. The calculations are incorrect when I Sum those 2
in the Report footer. This is where I am seeing it off by 1 or 2 pennies.
=Sum([Gross Order Total]) - Running Sum - No
=Sum([Sales Tax Total]) - Running Sum - No

John:
I changed the datatype to currency in my table but I am not doing
something
right in the query with the use of Round in the calculation.

SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CLng(nz([Quantity]*[UnitPrice])*100)/100)+Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRound])*100)/100)+First(CLng(nz([FreightCharge])*100)/100)
AS [Order Total]
FROM (Orders INNER JOIN [SalesTaxRound Query] ON Orders.OrderID =
[SalesTaxRound Query].OrderID) LEFT JOIN [Order Details] ON Orders.OrderID
=
[Order Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID;

----SalesTaxRound Query----
SELECT Round([SalesTaxRate],2) AS SalesTaxRound, Orders.OrderID
FROM Orders
GROUP BY Round([SalesTaxRate],2), Orders.OrderID;

Change all your CLng to CCur for starters. You can use Round() rather than
the
*100)/100 stuff - are you trying to round to integer dollars or what? You
say
you're "not doing something right" - what's wrong? Are you getting error
messages, wrong results, or what???


John W. Vinson [MVP]

 

[JMS]

Gross Order Total and Sales tax Total in Main report are off by a couple
cents.


----Order Details Table----
OrderDetailID - Primary Key - AutoNumber - Long Integer
OrderID - Text
ProductID - Text
Quantity - Number - Double - Decimal 0
UnitPrice - Currency - Format Currency - Decimal 2
CCSPrice - Currency - Format Currency - Decimal 2
Discount - Currency - Format Currency - Decimal 2


----Order Table----
OrderID - Text
CustomerID - Number - Long Integer - Decimal Auto
EmployeeID - Long Integer - Decimal Auto
OrderDate - Date/Time - Short Date
PurchaseOrderNumber - Text
ShipName - Text
ShipAddress - Text
ShipCity - Text
ShipStateOfProvince - Text
ShipPostalCode - Text
ShipCountry - Text
ShipPhoneNumber - Text
ShipDate - Date/Time - Short Date
ShippingmethodID - Number - Long Integer 0 Deciaml Auto
FreightCharge - Currency - Decimal 2
CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
ARDetailID - AutoNumber - Long Integer

----Main Report Query----
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100
AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net
Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS
[Sales Tax Total], Orders.FreightCharge,
Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total],
Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax
Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing,
Orders.OrderDate,
Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100
AS Profit
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge,
Orders.OrderDate
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]));

In the Main Report Query, each record for Gross Order Total is showing 4
numbers to the right of the decimal.


----Main Report----
Record Source - Main Report Query
Filter on - No
Order By On - No
Sorting and Grouping - OrderDate - Ascending
Group Header - No
Group Footer - No
Group on Each Value
Group Interval - 1
Keep Together No

In Report Detail I have Gross Order Total and Sales Tax Total which seem to
calculate fine...I think. The calculations are incorrect when I Sum those 2
in the Report footer. This is where I am seeing it off by 1 or 2 pennies.
=Sum([Gross Order Total]) - Running Sum - No
=Sum([Sales Tax Total]) - Running Sum - No