How Do You Cerate Incremental Calcs
Apr 24, 2006
Im trying to put info from an excel sheet in to a ms db my problem is as follows:
the user enters 3 numbers, the result needs to be calculated from number 1 + a % of another number this is dependent upon number 3
in short, each result is dependent on the prevoius calculation.
So if result (1yard)=100+10% yard 2)=110+10% yard 3)=121+10%
In excel it would look like:
1 yard b2 = b1+b0
2 yard b3 = b2+b0
3 yard b4 = b3+b0
Please how can this be done ?
View Replies
ADVERTISEMENT
Jul 31, 2007
Hi everyone, I'm having an issue getting a database working, I've set up a form to enter the data but I'm having trouble getting the form to calculate GST (10% of initial value) from an initial value entered if a check box for GST required is selected and then adding the GST to the initial value to create a Total Value. If you have a look at where I've got too with it so far and the picture of how I need the data too be formatted.
Here is a link to where I'm at so far with the database:
http://www.mediafire.com/?0mb0j1kdjwg
And I have what the report needs to look like:
http://www.mediafire.com/imageview.php?quickkey=cpneb3tgscd&t
http://www.mediafire.com/imageview.php?quickkey=9dzrbsx2hz5&thumb=4
Thankyou very much for any assistance you provide.
Jeremy
jeremy_2640 @ hotmail.com (remove spaces)
View 5 Replies
View Related
Oct 7, 2005
one of my tables has a field called # of Days. the default (minimum) value for this field is five (5), which i set. however, if there is a case where five (5) days are not enough, the next value would be ten (10) since the permit is issued for 5 days at a time, and so on. can someone suggest a validation rule that only allows increments of 5, but the minimum value is never less than 5? thanx in advance! :)
View 1 Replies
View Related
Oct 1, 2004
Hi, I have done this before but it has bee awhile. I have set up a small database to keep track of invoices. Before I generate an invoice I go to this database and put in the pertinant info. What I want it to do is generate an invoice number every time I go to start a new record, starting with a specific number and incrementing from there. FOr example I started with invoice number 2429 so I want it to generate 2430 for the next record . Please tell me how to do this again it is driving me nuts.
Whitlo
View 2 Replies
View Related
Dec 27, 2014
I'm building an application via Access that will allow a user at my company to import a CSV file with hundreds of records, press a button that runs several insert queries which inserts some of those records into our ERP system based on criteria.Each record of the insert query must have a unique "Interchange Number" that is historically unique, ie, if I used number 1000 in the insert query yesterday, I would need to use 1001 today. I already can find which value to start with using a DMAX() function on the table where you insert the records.I need a way to give each record of a select query a unique value that is +1 from the preceding record.
When I import the CSV file, I already have Access assigning an Autonumber. The problem is the file can have up to a thousand records a day while I only need to insert maybe 10 each day. If the starting Interchange number was 1001, I would like it to go 1001, 1002, 1003, etc. If I add the Autonumber from the table I imported, it can go 1001, 1050, 1343, based on where that record was on the autonumber when it imported.
So is there anyway to run a select query and have a field on that query start at 1 and count up by 1 for each record? If I can get that I can code the rest.
View 2 Replies
View Related
Jan 7, 2005
I need some help with code. I can do this in Excel, but I am not sure how to do it in Access.
Here are the fields:
PoundsPerBox
PoundsPerOrder
BoxesToOrder
Here is the scenario: If a customer orders custom paint for their equipment, we need to total how many pounds of Powder Coat paint it takes to paint all the units (PoundsPerOrder). When we order this from the vendor we have to order it by the box which is measured in pounds. In this example, the vendor will sell us a box of white Poweder Coat paint in increments of 55 pounds. (55, 110, 165,...)
Verbally, the logic reads like this: If PoundsPerOrder is less than or equal to PoundsPerBox, then BoxesToOrder equals 1. This logic needs to be tested for each increment value (indefinately) of PoundsPerBox.
Am I making sense? How do I accomplish this in Access? And, as a side note, I need to capture and store this value in a table for future reference.
View 7 Replies
View Related
Oct 3, 2014
I work for an insurance company where various (and multiple) discounts or loads can be applied to a quoted premium for one reason or another.
These discounts/loads are stored in a table with a corresponding customer ID, where each row represents an individual discount/load (labelled and ordered-by a 'Step').
Unfortunately, the resulting premium from these discounts/loads is not stored in the database and are calculated on the fly by the front-end.
I have a requirement however, to store the 'new' premiums based on the stored discounts/loads for a report.
My problem is that the calculations must occur incrementally one after another, where the discount/load at each 'Step' applies to the resulting premium from the previous calculation.
The attached spreadsheet is demonstrative of the existing table where two additional columns have been added to show you what I need to calculate. 'Price_Amount' represents the discount/load in monetry terms relative to the calculation and 'New_Prem' is the premium resulting from the calculation step.
Notice that each discount/load applies to the previously calculated premium rather than the original one (Original_Prem = the starting point from which all further calculations should apply).
The full table would include many more rows for different customer ID's, where the number of 'steps' could be as few as 1 or as many as 7 per customer ID (ordered by 'Step').
Having toiled for many hours in Access to achieve the above, I am now resigned to the fact that only some kind of VBA function will achieve my requirements.
This function should take the Original Premium for each CustomerID and loop through each 'Step' applying the relative discount/load based on the value in 'Pricing' and the number type in 'Price_Type'. Once the function has calculated each step per CustomerID and has reached the maximum 'Step', it should move on to the next CustomerID.
such can only articulate the requirements without being able to convert this into actual coded logic. Nonetheless, I am in a bit of a pickle with this one and am under some pressure to create the report.
View 7 Replies
View Related
Nov 6, 2014
I am working on an access database for my team. We keep and maintain a trail log for different departments.
The person will send a trail sheet to us we will input the info and attach what we call Production Instructions Number in this format (YEAR-Month-001), the next trail will get (YEAR-Month-002), the last 3 digits will start over when the month changes, I would like the PI number to populate automatically when the info is inserted.
View 11 Replies
View Related
Jun 30, 2015
I have 2 page tabs on a form, and I want an incremental number assigned to each new record on that page.
Been doing some research and this is what I came up with:
Private Sub Form_Current()
If Me!tabSupplier.NewRecord = True Then
Me!tabSupplier!DocumentNo = Nz(DMax("DocumentNo", "Product"), 1000) + 1
End If
End Sub
The page nane is: Supplier Page Index is:0
The control to display the incremental number isocumentNo (tblProducts)
it does not work and I am getting no error message.
View 11 Replies
View Related
Jun 8, 2015
Part of my access application does invoicing. I have an invoice number table that wholes one field "InvoiceNum". On my invoice report i have the following code:
Code:
Private Sub Report_Open(Cancel As Integer)
'// lookup invoice number when invoice opens
intInvoiceNum = Nz(DLookup("InvoiceNum", "tblInvoiceNum"), 0)
End Sub
[Code] .....
Problem: My report open with groupby ClientID and ProjectNum. Multiple invoices print on the same report. If any invoice spills over to second page the Format Code runs again inserting an incremental invoice number to the second page. How can i prevent this from happening?
View 8 Replies
View Related
Aug 28, 2013
I have a Primary Key field named Member ID(Number format) in a Table named Mail List. The field is populated with existing ID numbers and my need is to use my Member Entry Form to increment by 1 from the last entry.
I have gleaned from other references that this can easily be achieved by the following entry in the Default Value of the property sheet for the relevant field;
=DMax("Member ID","Mail List")+1
The Form saves without error but when I attempt to add a new record in the entry form, the ID Number reads "#Error".
View 14 Replies
View Related
Sep 10, 2006
Well, here's my database :
Name : String
Address : String
I want to add an automatically generated column, "no.", to show which number it's in. Like this :
No. Name Address
1. A Unknown
2. B Unknown2
The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.
Is it possible to generate query that have "no." column in it ?
View 1 Replies
View Related
