Morning
I'm not very good at this forum thing! I am still learning tho. I have good news I have worked out how to do what I requested. I 'normalised' my database from the advice give by mshmyob. I then thought about what I wanted. I entered "=sum([Quantity])*([Price]") into the form footer on the subform, this calculated the total and then on the main form entered "=(order details subform)!Grand Total" All is working order...
Sandretto
Last Activity: Apr 27 '11, 09:06 AM
Joined: Jul 2 '10
Location:
-
Apologies for the lack of information. I have attached a screen print from design view of form.
I need to add up a grand total from the 'Total' column of the subform. On the screen print you can see the attempt I made at the sum, but this doesn't work.
SQL for Main form -
...Code:SELECT [ORDER NO].[TO:], [ORDER NO].[ORDER NO], [ORDER NO].[ORDER DATE], [ORDER NO].[DELIVERY ADDRESS], [ORDER NO].[REQUIRED DATE], [DELIVERY ADDRESS].NAME,
Last edited by NeoPa; Apr 20 '11, 12:58 AM. Reason: All code must be posted in the CODE tags. Also made pic visible.Leave a comment:
-
Thanks for your help I'll have a read..I do have relationships attached screen dump, showing tables/relationships.
[imgnothumb]http://bytes.com/attachments/attachment/5072d1303222290/relatonships.jp g[/imgnothumb]...Leave a comment:
-
How do i get a total from subform on purchase order?
I have created a database to create and store purchase orders. My tables are as follows -
Tbl Order details (Qty, desc,price,tota l),
Tbl Order no (ON, date, date required, supplier)
Tbl Supplier (Supplier name, address)
I have created the Purchase order in form with subform. I am having difficulty getting the form to create a final total. I have tried '=sum([total]). -
Apologies for not responding correctly. I've corrected problem, it was due to location field. Thanks again for all your help and sorry againLeave a comment:
-
Thanks all working now...why when I search query by product code does it show 235 copies of same record??Leave a comment:
-
Thanks for your help!! My query is working but the sum of product out column seems to be multiply records by 2? and I can't work out why!!! Thanks AgainLeave a comment:
-
Erm sorry bit rusty with access!! i've tried code below
...Code:SELECT Sum([Product IN].[Qty Added]) AS [SumOfQty Added], Product.[Product Description], Sum([Product OUT].[Qty Removed from Warehouse]) AS [SumOfQty Removed from Warehouse] FROM (Product LEFT JOIN [Product IN] ON Product.[Product Code] = [Product IN].[Product Code]) LEFT JOIN [Product OUT] ON ([Product IN].ID = [Product OUT].ID) AND (Product.[Product Code] = [Product OUT].[Product
Leave a comment:
-
Sandretto started a topic Stock Database working out current stocking level sum of 2 tables and subtractin AccessStock Database working out current stocking level sum of 2 tables and subtract
I'm setting up stock database for warehouse I've got 3 tables-
tblproduct
tblproduct in
tbl product out
Relationship is done with Product ID
I'm trying to set up query, to working out current stock levels. So has to show product in, product out and product ID. Below is SQL coding but it doesn't work...
...Code:SELECT Sum([Product IN].[Qty Added]) AS [SumOfQty Added], Product.[Product Description], Sum([Product
-
Creating delivery note in access
Hi
At the moment we currently hand write our delivery notes, was wondering if it possible to let access write them? Currently have tables -
tblOrders (this is where order info is stored and works no is assigned)
tblDeliveries (Contains qty delivered, how it delivered)
tblproducts-(contains all product info)
tblCustomer- (Contains all customer info)
I've tried creating a form but i can't get... -
Thanks I'll have a look, do you have to use outlook?Leave a comment:
-
Sending information from database over email at end of day
I've created a database for orders on microsoft Access, the company is now splitting into 2 locations, 1 for production the other for accounts/admin. Ideally the delivery details need to be sent to accounts on daily basis, instead of individually typing an email was wondering is it possible to automate a system? All information is in 1 table called deliveries. For example sending delivery details from that day.
Thanks in advance for... -
Thanks for your help all working as it should nowLeave a comment:
-
SQL View -
...Code:SELECT Customer.[Customer Code] ,Orders.[SK Works no] ,Orders.[Order No] ,Product.[Product Description] ,Orders.[Qty Ordered] ,[Total Delivered on work no].[Line Item Totals] ,[How Many outstanding qry].Outstanding FROM Customer INNER JOIN (((Product INNER JOIN Orders ON Product.[Product Code]=Orders.[ProductLast edited by NeoPa; Jul 2 '10, 02:12 PM. Reason: Tidied SQL to read + Please use the [CODE] tags provided.Leave a comment:
-
How to create a query showing values that are 0
Hey
I've made an outstanding order database for the company I work for. I've created a query which calculates how many are delivered on each job number and how many are outstanding. Using tables -
tblOrders ( This contains order information and assigns the job num which is primary key)
tblDeliveries ( Contains all delivery information linked to orders through job no)
When I run the query only the...
No activity results to display
Leave a comment: