I need to create a report that shows profit/loss totals for a vehicle that I have listed in a table named "Vehicle Table". I have four columns: Cost, Shipping, Promotional expense, Repair, that I need to total and then subtract from the sales price to show the profit. Then, divide the profit by different percentages to show what I owe each salesman. I need to keep the different columns seperate and be able to increase the dollar amounts in each column as a new invoice is received for each vehicle.
For example, I have a $25.00 wash job in the "Repair column" and then I have to install a new set of tires for $450.00. The total in the "Repair Column" should now be $475.00. I can not make it add to the current balance in each of the certain columns. Would someone be so kind and help?
Well at the moment i'm still in the design stages, but i made a database with test fields to see how it would work out best.
Basically i have to have a form with a drop down list for each type of computer componant and when selected the total price for all the items selected so far needs to show up at the bottom of the form.
Or when a button is pressed the price could be updated, that is fine too!
The way i was planning to do it was having an orders page, in which each field was a lookup to a difference table, one for each componant Processor; ProcID, Proc name, Proc cost
Anyone have any ideas on the easiest way to load the total value for the items selected? Would be much appreciated! Thanks!
I have a order system which allows customers to place items on. When an item is selected and a quantity has been entered and add button is selected, this adds the item into a listbox with all the details. Each item that is selected a quantity is selected, this would then work out a total price and this total would be displayd within the list box too. i want to add all the totals up for that order and have it displayed within a text box.
for example:
product ID, Name, quantiy, item price, totalcost P0001 Top 5 1.00 5.00 P0002 Top 4 2.00 8.00 P0003 Top 3 1.00 3.00
i then have a text box where i want to display the overall total for these items within. hope this helps. im really stuck on this one!!!
I am currently making a database project for a school assignment.
I have based the project idea on a local hotel, which consits of a diffrent price dependant on the date booked.
So i have set up a table called "prices" which consits of the following fields:
PriceID RoomID * Price Start Date End Date
the RoomID is the foreign key, as it is linked (one-to-many) with my Room's table, which consists of the following fields:
RoomID * Room Number .. (unecessary fields for the problem)
The room ID is also linked to the bookings table (again one-to-many) with the bookings table consisting of:
BookingID CustomerID RoomID Start Date End Date ... (uneccessary fields)
what i am hoping to do is (i assume) make a query that will calculate the cost the customer has to pay. So it will look at the start and end date booked in the bookings table and comapre it to the dates in prices table, and find out the cost from this....
any help on going about this would be greatly appreciated, and i hope i have supplied sufficient information (i'll try attach a graphical image of the relationships if that is helpful?)
I have a table called: Invoice which has Invoice No (as the PK), Invoice Date, Total Cost.
I also have another table called Task: which has Task No (as the PK) , Task Type, No of hours, Charge Amount.
The user enters the Details of Task and Invoice in a Form. I have a field called Total Cost of my Form which should Calculate =([No of hours]*[ChargeRate]).
The problem I have is the total cost on the form is not being updated on the Invoice Table with The Total Cost.
I have a table called Price list, and in it I have two fields, one called Service and The other called cost. The services are listed and their matching prices are listed beside.
In another table called appointments, when making an appointment, I have linked the information so that I can choose from a dropdown from the price list table, under Service type.
I need to be able to create a receipt for the appointment. How can I do that in a query format?
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.????
I'm adding to a DB which has been working well for a while.
I have a continuous form which has a query behind it. The query takes two values: (For example)
[LineCost] and [LineQTY]
Inside the query, we use the expression TLC: [LineCost] * [LineQTY]
I need to sum this so a Sale with several lines gives me the total sell and cost value.
If you look at the sfrmEditQuote - you'll see the exact same method - working. sfrmEditSale is direct copy of this form, with some changes here and there. But can I get past the dreaded #error? Nope.
I've tried renaming the fields and text boxes, checking the query, but I keep getting #error on the sfrmEditSale form footer.
I need a query to help me track share of cost for clients. Anyone have any ideas? This is what I am working with. sorry it didn't paste very well. My problem is that my query keeps subtracking the share of cost and I want it to stop subtracking the share of cost once the share of cost has been met. HOpe that makes sense. Thanks in advance for your help in this matter. Angelflower.
Throught 01 year, I can make 10 purchases of pens with different prices.
I need to run a query of an individual department's consumption of pens in the month of June for example. I can get the right qty, no problem. But for $$$ purposes, I needed the query to return the most recent PricePurchased as compared to the OrderedDate.
Hi, I was wondering if there was an easy way to deduct VAT from ALL my records as I entered them with VAT, and now my boss is saying he wants them without VAT.
Is there an easy way to do this!? As there are thousands of records that I need to calculate the cost without VAT!!
I 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..
1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.
2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)
3) I start with daily (Lets don't be too overly ambitious).
4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.
5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?
Might be in the wrong place but didnt know it it needed to be in query, form or vb sorry admin.
I need help, what i need is a way of assigning a price too a drop down menu.
Basically its a Form with a number of drop down menus where you can select different varibles colour, finish for a certain product, but theres no method to generate a total cost of selecting different variables.
Do you know of a soloution becuase ive exhausted my capacity!!
I am having trouble calculating a field in a table and on a form. I have the following Tables CUSTOMER: Customer No, Name, Address, City, State, Zip, Discount, Phone, Fax, Contact Person. ORDER: Customer No, Order No, Order-Date, Delivery-Date. ORDER LINE: Order No, Product No, Quantity Ordered, line-item-cost PRODUCT: Product No, Description, Price, Product Image.
I need to calculate the line-item cost and need it to update every time the user enters a new Quantity ordered on the ORDER LINE form. I know its (Quantity Ordered*Price)-(Price*Discount) and I did this is a query and it worked but I need it to update in the form and update in the table not the query. I have tried everything (Macros, Queries, and Formulas) and I just can not get it to work. Does anyone know how to do this? Thanks
I am having trouble calculating a field in a table and on a form. I have the following Tables CUSTOMER: Customer No, Name, Address, City, State, Zip, Discount, Phone, Fax, Contact Person. ORDER: Customer No, Order No, Order-Date, Delivery-Date. ORDER LINE: Order No, Product No, Quantity Ordered, line-item-cost PRODUCT: Product No, Description, Price, Product Image.
I need to calculate the line-item cost and need it to update every time the user enters a new Quantity ordered on the ORDER LINE form. I know its (Quantity Ordered*Price)-(Price*Discount) and I did this is a query and it worked but I need it to update in the form and update in the table not the query. I have tried everything (Macros, Queries, and Formulas) and I just can not get it to work. Does anyone know how to do this? Thanks
Hi all. I have created a db for tracking different types of request (corrective action, preventive action, rework, scrap, etc). My problem is that the hourly overhead cost is different each year. (I use this cost to figure out actual cost of rework and scrap actions.) I know this is a simple solution but it is eluding me - I need to have this cost change each year but not affect the previous year's figures. Right now my formula is basic:
ReworkCost =[ReworkTotalTime]*69 ($69 is the 2005 overhead cost - this is the first year of use for the db)
I was toying around with using a table for cost alone and then using an If statement to pull the cost based on the year. Does anyone have a different idea on how to do this?
I have stored the costs in my database with a date they apply from so I have a series of costs which I can then refer back to using the order date to see what cost I should use at any one time. My table structure is as follows:
Code:
tblExtruderCosts ExtruderID PK - identifies the extruder FinishID FK - identifies the finish Cost - the cost for that finish from that extruder DateFrom - the date the cost applies from tblExtHead ID PK ExtruderID FK FinishID FK Orderdate
I have a form which has a number of fields and 2 of them are called [Postcode] and [Cost].
The cost field is defaulted to "£75.00"
What I am trying to is after the user enters the postcode it looks at the first and second value and if they enter a postcode in Scotland like below it changes the cost field accordingly.
The changes will only effect postcodes in Scotland if that makes sense as we have 2 different pricing areas for these
examples of some postcodes AB1 1AU £125.00 EH1 1RT £85.00 G1 1AT £85.00 IV5 1ER £125.00 ML1 1RT £85.00 and so on
so any postcode containing the following would update
AB = 125 EH = 85 G = 85 IV = 125 ML = 85 DD = 85 FK = 125 etc etc
all other postcodes in the uk will show the default value of £75.00
I have a table that holds information for a type of show and the base cost of the show. I am trying to make a query that gathers information for all the shows in the table and uses the Dlookup function to identify the base cost of that show. This will in turn be used in the same query to create a quote, but I can not get the Dlookup function to work and I am sure that I may be doing it very wrong.
The table is called ShowList. That table contains 3 fields, ID, Show Type, and Cost.
The query is called Venue Cost and will have a series of fields that work to develop some math on all items from another table called Venue Info. Venue Info uses ShowList to select the type of show.
What I want the query to do is look up the Show type from VenueInfo and then look up the base cost of the show type from ShowList. What I have been using is not working but is as follows:
I have this estimate database for a construction company. In this database I calculate how much will a project cost. It's pretty much complete the only problem that I have is trying to figure out how to update the cost of a trade without affecting older records
Example let's say we have a painter that makes $15/hr in project A,B,C,D,E we decide to give him a raise so project F would have a new amount for painter. The problem with that is that it will affect record A-E
I don't want that my department wants to go back and view a history of records. Also take a look at my database it's my first time creating one ...
I am setting up a inventory database and i will like the cost of the items to be calculated using FIFO.
Sample tables:
PARTS TABLE: part code description cost quantity on hand
PART TYPES part type id party type
INVENTORY invent id location reorder quantity
INVOICE id date part code part type qty cost ext cost
NB
For every item i will like the previous cost to be charged before the new cost. eg. if 10 pens where entered at $2 and another 5pens were entered at $4 each and 1 need 11 pens, i want the first 10 to be charged at $2 each and one at $4.
The client db is not Normalized. I understand their requirements and the current table structure has to remain in tact. The DATA ENTRY staff need to enter cost for Jan1 to Jun30. One of the many calculations that needs to be updated daily is the SUMPRODUCT from STARTDATE to ENDDATE - which can be anywhere between Jan1 to Jun30 at any time. The example scenario below needs to calculate the sumproduct of COST between any two fields (days of week).
For example, let's say a Purchase Order has been created, and the initial entry looks like this:
Here is the code I am using to calc the SumProduct upon initial entry WHERE the 'average daily burn rate' for the entire spread is the same every day (FYI, we need to calculate the YearTo-Date (YTD) and Remaining Balance from YTD for both Commitment and Incurred cost. For this example I am only snipping the code here to reflect Incurred) 'Initial Calculations
If CurrentDate <= frmStartDate Then SUMPRODUCTYTDIncurred = ZeroValue Else SUMPRODUCTYTDIncurred = Round((DailyBurnRate + DailyBurnRate * NoDays), 2) End If If CurrentDate <= frmStartDate Then SUMPRODUCTRemainingIncurred = ZeroValue Else SUMPRODUCTRemainingIncurred = Round(DailyBurnRate + DailyBurnRate * (DateDiff("d", CurrentDate, frmEndDate)), 2) End If
In my UPDATE SCRIPT (not shown - cause its TMI):
- I am using the For Each statement to search an ARRAY to filter for the db.TableDefs("MYTABLE").Fields I need to update. - I am also using a Do While True statement to catch the cost for the found field.
How to catch all the cost between Jan1 to Jun30 before updating the SUMPRODUCT field and then .MoveNext until EOF (remembering that the For Each statement will stop when field Jun30 is found)?
I am using access 2007 i need to know that i have a field as combined sum of apx cost with the name =sum([apx cost]) in the report section, i need the value coming in this box to be converted in text as pak ruppees.
I have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
