I need to make an order purchase form like i have customer table and product table, i want to make a form which creates order for a specific customer and I can add as much items as i want and than calculate auto the total price, and an option to make a report for it.
View RepliesI have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#31/07/2014#"),0)
Nz (dsum ("pqty","purchasequery","product=" & [sales.product] & "and clerancedate<=#" & [forms!salesm!sdate] & "#"),0)
But it is not working
Sdate is date and it is on main form
Ok so here is my problem:
I have a form that is realted to four differnt tables with the names: CUSTOMER, ORDER, PRODUCT AND ORDER LINE. With-in the table Order-Line there is a entity called, line_item_cost whcih is the total amount of the order with respect to Quanity_Ordered which comes from the ORDER LINE table and Price which comes from the PRODUCT table and Discount (%) which comes from the CUSTOMER table. When the users goes to change the Quantity_Ordered I need the form to automaticly update the line_item_cost which would calculate [Price]*[Quantity_Ordred]-[Price]*[Discount] . I tried to build a code in VBA to do this, Here is the Code:
Dim a, b, c, d
a = Me.[Quantity_Ordered]
b = Me.[Price]
c = Me.[Discount]
d = a * b - b* c
Me.[Line_Item_Cost] = d
VBA Complies the program but it does not show up in my form.
I think I might be updating it at the wrong point.
Any Suggestions.????
This query tell me what quantity I am short of to make a part
diffqty: [StockQty]+[OnOrderQty]+[PendingQty]-Sum([OrderQty]*[ProductQty])
This will return a negative quanity iE -200
what I then would do is copy that to a purchase order but remove the negative sign can this can the formula be changed in anyway to give the positive number .
I am working with a database that I downloaded and am trying to modify to fit my needs.
This is an inventory database. The products table contains a description and pricing. I want the description and pricing to populate in the Purchase Order form, so I added Dlookup fields in the Purchase Order form. I was happy.
However, the pricing information is not populating to my Inventory Transactions Table from the Purchase Order form by way of this Dlookup feature, and therefore will not show on my report, and in turn does not show in my Total of my Purchase Order report.
As a work around, I tried creating a calculation in the purchase order report, of =[UnitsOrdered]*[Products.UnitPrice], and the pricing totals show fine on my report, but the subtotal doesn't work.
I was unable to upload my file...so a few notes of info...
There are no queries set up in the database for this report.
I had tried a sorting grouping thing (in the Report) by Subtotal, but now can't get rid of it.
When I show the field list for the report, across the top of the window reads:
SELECT DISTINCTROW Employees.*, Products.*, [Inventory Transactions].*, [Purchase ORders].*, Suppliers.*, nz([Inventory Transact
Looks like it runs out of space
I am trying to attach a couple of images to support my comments.
Since this issue crosses both reports and forms (and tables!), I am not sure where to properly post. The end result I am looking for is on my report.
I am using Access 2003...
The problem that I am having is how to recalculate all order detail item.
FORM
Main form = Tblorder : orderID, CustumerID, TotalSquare
Sub form = TblOrderdetail : orderdetailID, OrderID, itemname, unitprice, total
Example: if I set up the totalsquare FIRST = 10 and I select the the itemname, it will calculate the total = unitprice * totalsquare this work fine.
I have 20 item in sub form orderdetail and every item was calculate based on totalsquare = 10. For some reason, I have to change the totalsquare = 20.
How do I make so that it will recalculate all 20 items in subform/orderdetail instead of deleting all item and re enter it again?
Hi, could someone please help me, I don't know how to put a total on my order form, heres my system:
Gamez System (http://www.savefile.com/files/8723782)
Thanks, Bob
I am trying to add a total to my order form but i am finding it a bit tricky, but once i have done this i also need to create an invoice, which should be simple enough but is quite difficult because of the way the system has been put together....
View 4 Replies View RelatedI am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
I need to calculate qty with price in a form.
View 14 Replies View RelatedAccess 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....
View 1 Replies View RelatedI'm new to access... I've been creating a cash register, that has the following tables:
Customer
Credit
Order_main
Order_sub
Products.
I've gotten so far that I can set up and order, consisting of several products, choosing the customer from a dropdown list and display the subtotal of the order.
But I can't, for the love of god, figure out how to deduct the subtotal from the credit value and get it updated.
Please find attached a mdb (2000) which includes 2 tables and 2 querys
If you open "QryProductsComboSelections" You will see that some product ID's have duplicate entries.
what I am trying to do is allow for contract pricing updates to be added with an affective date so they will not display until the affective date but also those old contract prices that have a date less than the current affective date will also be hidden thereby always limiting it to one record even if the product only has one pricing entry.
I can do this with a boolean "PriceClosed" but it would only be a last resort as I would like to automate this so it requires no user intervention other than adding the new price for A given date.
I have spent hours playing with this so rather than through the comp out the window Thought I would do the sensible thing and come and ask the experts.
Hope somebody can help as done a search and got loads of unrelated results.
Best wishes
Mick
Code that will allow me to post selective data from a purchase received form to the inventory using a command button. this command should also add to the existing quantity in the inventory.
View 2 Replies View RelatedHi, i'm trying to calculate the amount of employee for each company giving me a total in the queries.. how do i go about this? I'm not an person who uses Access to solve this. I have amount 20 Companies stored and over 800's employee.. Here are the field in the table:EmployeeIDEmployeeNameNumberofEmployeeThanks! Hope this helps.
View 1 Replies View RelatedI'm trying to do a lookup between two tables, whereas table A includes the product ID and table B includes the price. Is it possible to create a field in table A with a DLookup function on the product ID to get the product price from table B?
Whenever I try to type in the DLookup function, it does not calculate.
Hi All.
I try to generate a report which calculate subtotal for patient AMOUNT for current year and total for whole period from first visit to current date. The result of subtotal =Sum(IIf(Year(Date()=[VisitYr]),[Amount])) is the same like total. What is a correct way?
Thanks.
Say I have a table with ID's of people and a column with the hours they need to work on a specific day. I want to make a query where I get the total working hours for every person in that table for a certain date. The problem is that when a specific person in the table has no hours for a certain day he doesn't show up in the query if I choose that day.., is there a way to make sure that I get everybody's names for every day even if they don't have any hours on that day, but instead of not showing it would show a "0" instead?
View 1 Replies View RelatedI create a database through ms access and there have a birth date box and admission date. Another box for Age.I want to see the age in month or year figure in to the age box when I go next field. Which will be calculate from admission date to birth date.
View 1 Replies View RelatedI have an order database Access 2000 for an inspection company, tblInspectors, tblOrders, tblCustomers, I want to be able to calculate inspector pay within order database. Each inspector is an independent contractor, so it just needs to calculate percentage, for each inspection performed and total for pay period. Each inspector is paid a different percentage, which is stored in tblInspectors. Payroll is paid every two weeks . . . I've been trying to figure out the best way to do this ... :confused: Any advise be greatly appreciated.:confused:
View 4 Replies View RelatedHello, i have a table with the fields: "employee_number", "shiftdate" "department" "basic_hours", and "over_time"
And a query named "employee_hrs" looks at the fields in this table, i would like a new field in the query to be able to calculate the total hours for all employees together but for each department.
eg the total of: basic hours + overtime for department A)
the total of: basic hours + overtime for department B)
and so on for each department
would only like to show one instance of a department (distinct) with the total hours done by all employees for that department?, if any one could please help me out that would be great!?
I have this columns :
Income | Outcome | Transport | Total Income | Date
Total Income (N) = Income (N) + Transport (N-1)
Total income equals income from that day + transport from previous day
Transport (N) = Total Income (N) - Outcome (N)
Transport from previous day equals Total Income minus outcome
How can I calculate the transport from previous day to sum to next day total income?
I have a problem with Microsoft access 2003. I want to design a Check in/Check out Time Data base for my university final term project. In this data base I need to show the total of the working hours of each personnel in a month.( for instance: 124 hours and 55 minutes in one month). But adding up the hours, when the time variable reaches 24 then time automatically goes back to 01:00. As a result, the correct total hours cannot be calculated....
View 1 Replies View RelatedI have two numbers, i need to calculate how much percentage of one is the other. E.g
num_1 = 100
num_2 = 10
percent = 10%
Users enter a dollar amount (retainer) and my code should calculate the percent of the total proposal amount.
Actual Code
Private Sub cmdSubmit_Click()
Dim intProposalTotal As Double 'if i use interger i get "overflow" error
intProposalTotal = Nz(DLookup("ProposalTotal", "qryPropsalTotalForRetainer", "proposal_id=" & Me.proposal_id), 0)
[Code] .....
billing_retainer_percent is formatted to Percent with 0 decimals. In the actual table Type - Number, Format - Percent, Field Size - Single. What i get is 100% instead of 10%.
I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
I am having trouble with this running total. Let's say I have a well. I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month.
I attached a PDF with an example of what I am working with.