Calculating Line-Item Cost In Table
Oct 11, 2006
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
View Replies
ADVERTISEMENT
Oct 11, 2006
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
View 1 Replies
View Related
Dec 7, 2004
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?)
thanks
michael
View 2 Replies
View Related
Mar 26, 2014
I want to have line item numbers on report look like this:
Number....Order Number
1............9533-1425-20
2............5866-3411-14
3............2332-2355-14
3............2332-2355-14 ->repeating item
4............4399-5208-12
5............7392-2305-62
5............7392-2305-62 ->repeating item
6............2332-2355-14
Each new row of record needs to have their own line item number starting from 1 with criteria if order number is the same then carryover line item number on that record. I know how to get line item starting from 1 to last by assigning unbound control field with entry "=1".
View 14 Replies
View Related
Jun 29, 2006
For some reason I can't write the proper query to get a simple total from a single table. Here is the basic table design:
Transaction ID___Product ID___ Transaction Date____Transaction Quantity
1______________Product A____6/12/2006__________200
2______________Product B____6/12/2006__________500
3______________Product C____6/14/2006__________100
4______________Product B____6/15/2006__________200
5______________Product C____6/16/2006__________300
6______________Product A____6/17/2006__________500
I'm trying to get the total transactions quantity for each product so that I could end up with a form or a report that would show:
Product ID_____ Transaction Quantity Total
Product A______700
Product B______700
Product C______400
... and so on for each item.
What would actually be entered in the query? I'm assuming I would have four columns in the query:
transaction id____product number____transaction qty____expression
Then I would use the SUM feature so the "group by" row appears.
Then I would build the expression above in an expression column. If that's correct, could you give me the expression typed "exactly" as you think it would appear? I'm getting syntax errors when I'm trying it.
And then, once I have the expression built, what should be the "group by" selection under each of the columns?
Sorry for being so ignorant. The rest of my data base works great... but I'm just not getting this one thing!
Thanks anyone who can help me!
View 4 Replies
View Related
Apr 8, 2013
Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.
1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.
2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.
View 7 Replies
View Related
Mar 24, 2014
I'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.
Error message highlights the red line below with the following message:
Runtime error '438'
Object doesn't support this property or method
I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.
Code:
Private Sub cmdSend_Click()
Dim outApp As Object
Set outApp = CreateObject("Outlook.application")
Set outmail = outApp.CreateItem(olappointmentitem)
[Code] .....
View 10 Replies
View Related
Nov 29, 2014
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG)
Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer
Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String
Dim Lim As String, ITNO As Integer
[code]....
View 8 Replies
View Related
May 17, 2007
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.
Can anyone help please
View 3 Replies
View Related
Aug 18, 2006
I have a form that simply lists the items:
DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE:
If IsNumeric(DEPARTMENT_NBR) = False Then
If DEPARTMENT_NBR <> "00" Then
MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly
DEPARTMENT_NBR.SetFocus
GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks)
End If
End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
View 4 Replies
View Related
Apr 23, 2015
I have a database to keep track of time spent on development work. The database uses mainly two tables: Estimates and Status.
The Estimates table holds a static number for each item to be worked on. We generally subtract this number from the total number of hours in Status spent on each of the items. In queries, to calculate the overall delta, we subtract the Estimate from the overall Status for each item.
However, we would like to create a report that gives us a running total for each item. So, if we have 100 hrs in the Estimate table for Item A and 5 hrs for item B, then the report would ideally show something like this (delta between Status table and static value in Estimate table):
Code:
Item | Resource Name | Estimate | Actual | Delta
--------------------------------------------------------
A John Doe 100 10 -90
A Jane Doe 90 5 -85
A John Appleseed 85 5 -80
B John Doe 5 10 5
B Jane Doe -5 5 10
This is so that we can see how our actual hours spent working on a task line up to our estimates. So, if we are under estimating our work, we can easily see this.
In Excel, this is of course no issue, but it becomes an issue when trying to write a query in Access to report this information.
As I said, we can do the overall numbers, just not the line item numbers.
View 14 Replies
View Related
Jun 25, 2013
Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
View 12 Replies
View Related
Jun 5, 2014
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
View 6 Replies
View Related
Nov 18, 2014
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
View 14 Replies
View Related
Oct 18, 2014
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line
with me.graph47
.SeriesCollection(1).border.Color = vbblue ' change the line color
.SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3
.SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4
.SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color,
.SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis
end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
View 1 Replies
View Related
Oct 18, 2007
I have a table with a DateTime field. I would like to create a query that queries only the 3rd most recent record in the table. So there would only be one record per ID. If there is only two records for an ID, it would show nothing. I have tried a few things already, but none of them have worked quite right. I was wondering if anyone had a new idea.
View 11 Replies
View Related
Sep 14, 2007
So here goes :)
I have made this example database to show you my problem, I have 2 tables here:
1. "items" table that works like warehouse listing items in stock and has these fields:
ID autonumber
Example name TEXT
Sold Yes/No
2. "sales" table has these fields:
ID Autonumber
Example name created using Lookup in Design view
I created Query called "sales Query" that I use to enter data and my problem is I need only to be able to enter items that are not checked in "Items table".
In other words I need to be able to select in the query dropdown items currently in stock.
How to solve this the easy way? I tried using criteria "False" but dropdown still lists checked items WHY?
View 5 Replies
View Related
Jul 8, 2014
code to save data in listbox to a table in vba access
View 2 Replies
View Related
May 16, 2014
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
View 1 Replies
View Related
Jul 11, 2014
Save Item in listbox (Row source type : Value list) to a table in a database .
View 3 Replies
View Related
Jan 14, 2013
I am trying to augment my database to have a price list. Because I wanted the price list to be of record and saved, I used a form with a subform. The main form taking the price list header info price list ID, price area, and date and putting into tblPricelist) and using the sub form to table to load the actual price list info (product, price) and putting into tblpricelistsub.
What I am trying to do is to be able to bring the entire price list as it is and be able to update (or not) the price of all or some of the items and then have it save as a new entry into the tables. I am trying to do this without having to use a sub form and lookup every product all over again (500 products).
Is there a way to do a query to bring up the entire price list, turn that into a form, and then be able to update the prices that would in turn create another entry into the price list and pricelistsub tables?
View 13 Replies
View Related
Dec 24, 2013
Im trying to make a basic customer database and pos system. The part iam having trouble with is, I have two tables as follows:
Inventory: orders(invoices):
productId ProductId
amount (in stock) amount(sold)
pricePerUnit CashTendered
I want the amount(in stock) sold in each order to be deducted from the total in the amount(sold) automatically.
View 1 Replies
View Related
May 1, 2013
How do I get only one item in a combo box when using the Table/Query option (ie No Duplicates). I want only one of each field with no duplicates.
View 2 Replies
View Related
Mar 4, 2015
I have what should be a really simple database to build. In table tbl1PlanHdr is the header item. Many items in tbl2MaintItem will then be allocated to each header. MaintItems items will be allocated to more than one Header.
I want to set up a form that has the tbl1PlanHdr as the main part of the form and tbl2MaintItem as the subform. Possibly as a datasheet, or continuous form.
I need the ability to update items in the subform back to the orginal table.
I have a relationship of one to many from the tbl1PlanHdr to tbl2MaintItem using the primary key in tbl1PlanHdr.
But should this be a one to many from tbl2MaintItem to tbl1PlanHdr
The end result required is to be able to get a data dump of all the tbl1PlanHdr items with the corresponding tbl2MaintItems items.
View 8 Replies
View Related
Aug 23, 2013
I have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?
I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe
Product A - CustomerADiscount - 10%
Product A - CustomerBDiscount - 20%
Product B - CustomerADiscount - 5%
Product B - CustomerBDiscount - 7%
This link these values up within a query? Would this be the best way of doing it?
Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?
View 3 Replies
View Related
Nov 7, 2014
Basically I am inputting stock into my database so I can have a record of what I have left and who it's gone too. Where I'm stuck though is when I want to add an item that I have more than one off but only enter it once and have it assigned a unique stock ID number (this is in the form of an autonumber) for however much quantity I choose to enter. So for example I have 5 keyboards, I type in 'Keyboard' into my item field (named txtItem) and then type '5' into my quantity field (named txtQuantity). When I then press Add Stock (named btnAddStock) I should then see in the table, 5 separate Keyboards listed each with a different Stock ID number.
View 14 Replies
View Related
