Logic Testing For Incremental Numbers

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 Replies


ADVERTISEMENT

Incremental Validation Rule

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

Custom Incremental Numbering

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

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 6 Replies View Related

Incremental Field On Query

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

Modules & VBA :: Incremental Calculation Using Loop

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

Year / Month And Incremental Number (14-11-001)

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

Forms :: Creating Incremental Numbering On Page?

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

Modules & VBA :: Incremental Invoice Number Using DLookup

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

Forms :: Incremental Numbering From Existing Number Sequence

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

Help On Sql And Logic

Jun 14, 2006

Have this table

ID | NAME | Progresscomplete | Release
1 | a | 20% | July
2 | b | 65% | July
3 | c | 33% | July
4 | h | 15% | Sept
5 | i | 30% | Sept
6 | r | 5% | Dec
7 | s | 50% | Dec
8 | t | 15% | Dec
9 | u | 20% | Dec

now I want to create a query that shows the Progresscomplete for each task as a percentage for a Release

so the total progress for each release is
July 300%
Sept 200%
Dec 400%

and the current level of progress for each release is
July 39.33% (118/300*100)
Sept 22.5 % (45/200*100)
Dec 22.5 % (90/400*100)

thus the current level of progress for each task within a release is
1 | a | 16.9% (20/118*100) | July
2 | b | 55% (65/118*100) | July
3 | c | 28% (33/118*100) | July
4 | h | 33% (15/45*100) | Sept
5 | i | 66% (30/45*100) | Sept
6 | r | 5% (5/90*100) | Dec
7 | s | 55% (50/90*100) | Dec
8 | t | 16.6% (15/90*100) | Dec
9 | u | 22% (20/90*100) | Dec

I have queries for the first two not sure about the last also
not sure if i should have the base of the last output be the current level of progress for each release or the total progress for each release

I have tried this for the last output

SELECT ID, NAME, ((Progresscomplete/Count(Progresscomplete))*100) AS currentprogresslevel, Release
FROM TEST_RawData
GROUP BY Release;

but get an error "tried to execute a query that does not include specified expression... as part of an aggregate function.

View 5 Replies View Related

Sum Logic For Report

Dec 19, 2005

I have a report that takes money values form several records and sums them up in various category's making a list of departments with the total amount of balance for each (positive and negative numbers). I need to categorize the positive values form the negative and calculate the totals of each and the grand final total (invoice style)

View 1 Replies View Related

Don't Understand The Logic

Jan 28, 2006

I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?

View 2 Replies View Related

Please Help With Query Logic

Oct 18, 2006

Access is making my brain hurt - can anybody please help?

Here's what I have and what I am trying to get it to do...

I have a table, maba_registrations, into which students are entered according to which course they are doing in which semester - eg -


autonumber student_id course_code semester year
1 1 A123 1 2006
2 2 A124 1 2006
3 2 A124 2 2006
4 2 A126 1 2006
5 3 A132 1 2006


What I need to do is to pull four subsets of data out of this table -

1) A unique list of courses that student x is taking in semester 1 (only) of a given year
2) A unique list of courses that student x is taking in semester 2 (only) of a given year
3) A unique list of courses that student x is taking over 2 semesters (both semester 1 and semester 2 of a given year)
4) A unique list of courses that student x is taking over 2 semesters (semester 2 of a given year and semester 1 of the following year)

Obviously there should be no overlap... and that's where I get a bit stuck

I can craft a simple query that will show me the courses a student is taking in semester 1 of a given year, but I cannot figure out how to get it to exclude those courses which are also still being taken in semester 2. In the above example, the query pulls out records 2 and 4 for student 2; I only want record 4.

I think what I need is to have 2 queries -

a) pulls out all courses for student x in semester 1 for the current year
b) pulls out all courses for student x in semester 2 for the current year

- and then subtract the results of query (b) from query (a) (ie remove the rows that are matched). Is there any way to do something like that?

Or is there a simple part of query syntax that I can use to make a query that just says "pull out all the courses for student x where semester=1 and there is no row for this course and this student where semester=2"?

Any ideas would be hugely appreciated!

View 4 Replies View Related

Logic On Percent Format????

Dec 8, 2005

Maybe someone knows the logic behind MS Access for the display of percents in the formating..... ie.... If you input 3 you get 300.00%. I really dont think ANY user would look at a form... with a field called...say "Commission percent" and expect an input of a 5 to return 500.00% Does this need to be corrected with coding?

View 2 Replies View Related

Date Query Logic Help

May 4, 2007

Hey guys, I'm a little lost in my logic here for some reason.

I have a table that has 6 dates. I'll label them as 1 to 6.
Date 1 > Date 2 > Date 3 > Date 4 > Date 5 > Date 6.

I would like to run a query to ensure that this holds true. Can anyone give me a little help on this?

Do I have to do an individual compare from 1 field to the 5 other fields 6 times? for example

Select if
Date 1 < Date 2
OR
Date 1 < Date 3
OR
Date 1 < Date 4
etc etc.?

View 5 Replies View Related

Faulty Query Logic?

Apr 4, 2008

I realize my newbness shows here...

Im trying to run a query that looks in a table to see if product a and product b were purchased and then return the id but i never get any data, and I know there are some results that should be showing up. If I remove either one of the products it returns data but not when both are requested. here's the query:
SELECT [Copy Of data].id, data.purchase_product, data.purchase_product
FROM data INNER JOIN [Copy Of data] ON data.email = [Copy Of data].email
WHERE (((data.purchase_product)="ProdA") AND ((data.purchase_product)="ProdB"));

what am i not getting?

View 5 Replies View Related

Creating A New ID Field With Logic

Apr 24, 2008

I am looking for some direction on how to approach this.

I have a table that has a field call DocumentID and I don't like it. I would like to create my own.

In the same table I also have 10 fields of data called L1, L2, to L10.

I would like my new DocumentID to be the 10 fields concatenated together, with a period between them unless the value of the field is null.

If this is a query I need to then move the data back to a table in the same database.

Any suggestions on a good strategy/approach?

Thanks

View 3 Replies View Related

Logic Commands: IF Function

Feb 26, 2007

I have been working on customizing MS ACCESS to
produce a report that involves one mathematical
computation from my input data. However, I've run
into a problem because I can't get the program to
carry out a logic command that would work fine in
MS Excel: the "IF" command. Specifically, what I'm
doing involves a calculation, where one data input is
subtracted from another data input, but if the difference
is a negative number, I would like to substitute the value
of ZERO. The closest I've come to achieving this is to
enter the condition ">0" while in Query Design mode, but
the problem with this approach is that if the difference
is a negative number, the report does not display the
data that resulted in the calculation of the negative number.
In other words, I want the data to be included in the report,
but I want a "Zero" to be substituted for all calculations that
produce a negative number. For example, if I enter a set of
data, and the two numbers that get subtracted are 40 minus
36, then the difference is positive 4, and so I want a "4" to
be displayed; But if the two numbers to be subtracted are
36 minus 40, the result is negative, and so I'd want the
report to display a "zero" in the calculation.
Please advise me on how to set this up, as the only approach
I've found resulted in all negative calculations being omitted
from the report.
Thanks for your help.

View 6 Replies View Related

Testing

Oct 9, 2004

hi is this working yert?

View 1 Replies View Related

Can Someone Please Explain Basic Query Logic To Me?

May 11, 2006

I am not sure if I understand this...

I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????

Any help is greatly appreciated.

Tom

View 4 Replies View Related

Reports :: Subreport Based On Logic

Oct 21, 2013

I would like to create a report based on a query. The first part is simple enough. However within this query I have a 'Count field' (a total of the number of duplicated this record has appeared in the table). And would like to create a subreport (if possible) based on that value for example. if count > 1 then show subreport.

My question therefore; is the above possible? How would I go about linking this logic with the subreport

View 1 Replies View Related

Testing For A Sunday?

Feb 22, 2008

Hello peeps,
I have been working on a hours checking application, in which the user can enter their worked hours for the week and the application calculates how many hours at normal rate , time and half rate or double time rate have been worked for the week. All has been going swimmingly, with some help from here of course :)

What I would like to achieve now however, is, testing for a Sunday. This is because all hours worked on a Sunday, is paid at double time, regardless of the previous hours already worked for that week.

Example if a person worked 60 hours in a given week, there would be 47.5 hours of normal rate, 7.5 hours of time and a half rate and the remaining 5 hours would be at double time rate. But, if a person worked 60 hours in a week and 10 of those hours are worked on a Sunday, this would mean 47.5 at normal rate, only 2.5 hours at time and half rate and the remaining 10 hours at double time.

Therefore, is there a way I can test for a Sunday and perhaps automatically set a flag for the overtime calculation? Or is it possible with DateDiff() function, and if so could someone please assist me with the syntax or at least point me in a direction where I could solve this?

All replies gratefully recieved. Thanks in advance to anyone taking the time to read this post.

View 6 Replies View Related

New Record, Testing For

Jun 11, 2005

I need to disable a custom goto next record button. How do I find out if the current record is both the last and new or if a new record is possible?

Many thanks in advance,

Aidan

View 4 Replies View Related

Testing For Null

May 2, 2008

I know this is an easy one but have to ask...I am trying to test wether or not a Textbox in my form is Null or not...

Cant get it to work

Thoughts?


Code:If Text45 = "" Then MsgBox "45"Else MsgBox "THERE IS SOMETHING"End If

View 4 Replies View Related

What Is The Logic On How Report Fields Are Exported To Excel

Nov 13, 2007

Tried to export a report to Excel using Tools>Office Links>Analyze It With Microsoft Excel menu. The order of the fields appearing in Excel doesn't seem to match the order on the Access report layout. What is the logic on how the fields exported? Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved