By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Access order entry database totals haywire

P: 38
HI all I have downloaded Microsofts Order managment database.
I have spent hours trying to get the thing to come up with the totals of my new orders as a sub total so I can add VAT.
I have tried making the VAT field in the Database table stick to 17.5% but it likes to shoot upto around 4800 ish instead of 17.5, I did set the default value to 17.5 aswell.

I have a form within a form that takes order details but when i finish adding my relevent order details all i get in the Sub total for the whole order all i get is the line total value.

basically I aint got a clue i tried dsum but it may not be in the right spot
Any help so I can resolve this and go to sleep would be great
I am working with Office 2003 Pro
Thanks
Mark
Mar 12 '07 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
HI all I have downloaded Microsofts Order managment database.
I have spent hours trying to get the thing to come up with the totals of my new orders as a sub total so I can add VAT.
I have tried making the VAT field in the Database table stick to 17.5% but it likes to shoot upto around 4800 ish instead of 17.5, I did set the default value to 17.5 aswell.

I have a form within a form that takes order details but when i finish adding my relevent order details all i get in the Sub total for the whole order all i get is the line total value.

basically I aint got a clue i tried dsum but it may not be in the right spot
Any help so I can resolve this and go to sleep would be great
I am working with Office 2003 Pro
Thanks
Mark
Percentages are funny in Access. Set your percentage to .175 and see what happens.
Mar 12 '07 #2

P: 38
Percentages are funny in Access. Set your percentage to .175 and see what happens.
Thanks I will try that.
Just wonder can you help me with getting my "Sub total" & Total field to add up as it where. Im new to this and been here 40hrs ish. I need in very easy to understand pointers how I can get the "total cost" of the "item Description* "QTY" line(S) of my order entry database.

of if you know where I can download a better database ikm using this one from Microdoft home download called order managment database.

Thank you agin for responding.
Mar 13 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks I will try that.
Just wonder can you help me with getting my "Sub total" & Total field to add up as it where. Im new to this and been here 40hrs ish. I need in very easy to understand pointers how I can get the "total cost" of the "item Description* "QTY" line(S) of my order entry database.

of if you know where I can download a better database ikm using this one from Microdoft home download called order managment database.

Thank you agin for responding.
Open the query in Design view and then change the query to SQL view and copy and paste it in here. Tell me exactly what field you want to total and subtotal.

Mary
Mar 13 '07 #4

P: 38
Hi here is that info, thanks for your help.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Order Details].OrderID, Customers.CompanyName, Products.ProductName, Products.UnitPrice, [Order Details].Quantity, [Quantity]*[Order Details]!UnitPrice AS SubTotal, [Order Details].Discount, [SubTotal]*(1-[Discount]) AS [SubTotal With Discount], Orders.SalesTaxRate, [SubTotal With Discount]*(1+[SalesTaxRate]) AS [Total Price], Customers.CustomerID
  2. FROM (Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID) INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID=[Order Details].ProductID) ON Orders.OrderID=[Order Details].OrderID
  3. ORDER BY [Order Details].OrderID, Products.ProductName, Orders.SalesTaxRate;
  4.  
I need to get my line totals to add up ie (Quantity * Cost) *17.5%.
then there are fields which show Sub total and total. These are in the parent form rather than in the order details form.

Well hope this helps get me sorted. thanks again.

Regards
Mark
Below is total price of order query
Expand|Select|Wrap|Line Numbers
  1. SELECT [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, Sum([Total Price of Order Details].[Total Price]) AS [SumOfTotal Price], [Total Price of Order Details].CustomerID
  2. FROM [Total Price of Order Details]
  3. GROUP BY [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, [Total Price of Order Details].CustomerID
  4. ORDER BY [Total Price of Order Details].CompanyName;
  5.  
the database i have is available from microsoft and is callled an order managment database. i
Mar 13 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
If you change SalesTaxRate to .175 does this work OK?
Expand|Select|Wrap|Line Numbers
  1. SELECT [Order Details].OrderID, Customers.CompanyName, Products.ProductName, Products.UnitPrice, [Order Details].Quantity, [Quantity]*[Order Details]!UnitPrice AS SubTotal, [Order Details].Discount, SubTotal*(1-[Discount]) AS [SubTotal With Discount], Orders.SalesTaxRate, [SubTotal With Discount]*(1+[SalesTaxRate]) AS [Total Price], Customers.CustomerID
  2. FROM (Customers INNER JOIN Orders 
  3. ON Customers.CustomerID=Orders.CustomerID) 
  4. INNER JOIN 
  5. (Products INNER JOIN [Order Details] 
  6. ON Products.ProductID=[Order Details].ProductID) 
  7. ON Orders.OrderID=[Order Details].OrderID
  8. ORDER BY [Order Details].OrderID, Products.ProductName, Orders.SalesTaxRate;
  9.  
What do you want to do exactly to change this? That is what is it not giving you now that you need?

Expand|Select|Wrap|Line Numbers
  1. SELECT [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, Sum([Total Price of Order Details].[Total Price]) AS [SumOfTotal Price], [Total Price of Order Details].CustomerID
  2. FROM [Total Price of Order Details]
  3. GROUP BY [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, [Total Price of Order Details].CustomerID
  4. ORDER BY [Total Price of Order Details].CompanyName;
  5.  
Mary
Mar 13 '07 #6

P: 38
Within my order taking database i have a form that allows users to place orders.
within this form there is another form for tracjing the order details. ie item cost* Quantity.
This then gives me a sub total, Total order Cost, in the first form.
This is the part that isnt working.
When I enter the order item it comes up with the cost of that specific item, but the sub total field & total field in the main form dont work. it does not multiply the order quantity x cost. or show me the totals just "error".

I have changed over the value for the vat field to 0.175 but still working on this.
I would like to be able to print an invoice for ordered items via a button link within my form aswell.

Thanks again

Mark
Mar 13 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Check the datatype of the Quantity, UnitPrice and Discount fields. Are they all numbers? If they are make sure the defaults are all set to 0.

Mary
Mar 13 '07 #8

P: 38
Hi I have now changed this over but still got errors. is there anyway i could email the databse its 152kb im in a real pickle here.
thanks
mark
[num removed] if you wanna chat or i could call you?
Mar 13 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Mark

I've removed the number. Always use PM's to send this kind of information for privacy.

I'm sending you one now.

Mary
Mar 13 '07 #10

P: 1
hey guys did you manage to solve the subcost and total cost problem??
I am currently working on a similar problem, but with no VAT fields. What i have is a subform that calculates the order subcost, but when i go to calculate the total cost, it just gives me a blank record. I do this by =SUM([Subcost]) but it doesnt work. I also want to reference this total cost onto the main parrt of the order form so that it shows on the main form. If anyone can help, i'd be greatful!
Mar 19 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
hey guys did you manage to solve the subcost and total cost problem??
I am currently working on a similar problem, but with no VAT fields. What i have is a subform that calculates the order subcost, but when i go to calculate the total cost, it just gives me a blank record. I do this by =SUM([Subcost]) but it doesnt work. I also want to reference this total cost onto the main parrt of the order form so that it shows on the main form. If anyone can help, i'd be greatful!
You can't calculate a value based on another calculated value.

If you have a field called cost and two group headers like Order and Customer. Then in the group footer of Order you could have a textbox with

Expand|Select|Wrap|Line Numbers
  1. =Sum([cost])
which would subtotal each order and in the footer of customer you could have exactly the same textbox which would sum the total of all orders per customer.
Mar 20 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.