473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access order entry database totals haywire

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
11 2272
MMcCarthy
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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

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

Similar topics

3
by: C. Homey | last post by:
I have an Access 2000 database (42,000+ records) for which I need a count of records by state (AL=320, AK=92, NY=1,932, etc). Obviously, I don't want to (and probably can't...) do the count...
20
by: Allen Browne | last post by:
To help you avoid some of the more common and long-standing bugs in Access, the page: http://allenbrowne.com/tips.html contains a new section entitled, "Flaws in Access". The section currently...
7
by: Doug Vogel | last post by:
Hi All - I have a client for whom I developed an Access 2000 database. The database is split into 2 files - front-end (forms, queries, reports), and back-end (tables). An .mde file has been...
8
by: David Kistner | last post by:
I'm fairly new to Access (I've worked with Oracle and MySQL in the past). I was asked to build an application for a small office and told that I had to use Access 2002. I was VERY uncomfortable...
0
by: David G. | last post by:
The keyboard type ahead buffer does not seem to work in Access 2003 in certain situations. We would like some help with this. Here are the details. We have a large program that was developed...
13
by: royaltiger | last post by:
I am trying to copy the inventory database in Building Access Applications by John L Viescas but when i try to run the database i get an error in the orders form when i click on the allocate...
12
by: AllYourSpam | last post by:
I work for a small company just over a year old. I have them sold on the idea of a database for tracking their sales and order entry (plus many other duties). We are going to develop our own...
2
by: peter1234 | last post by:
I am trying to produce study notes for ACCESS, and for many days I am stuck trying to produce totals and subtotals for table ‘Order Details’ from ‘Northwind Sample Access Database’. The fact that I...
1
by: resqtech | last post by:
I am having an issue with an Access Database that worked at one time and after a Windows Update that screwed up the profile it stopped working. The following is what VB is stating causes the error. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.