General :: How To Compare Sum Of A Few Numbers From A Set Against A Given Number

Nov 1, 2012

I have a set of numbers, say (these could also be values of a column in a table)

FieldA = { 11, 16, 20, 23, 30, 37, 40, 50 }

I have a number, say 196.

I want {16,23,30,37,40,50} to be returned as these numbers add up to 196.

Note :
1) There is no possibility of two solutions with the kind of numbers that I may be using.
2) A solution using excel is also OK, though, personally I would prefer access.

Edit :
3) {16,23,30,37,40,50} - Each value is a separate record i.e. they are not in a single field, rather :
16
23
30
37
40
50

View Replies


ADVERTISEMENT

Modules & VBA :: Compare Currency To Number?

Dec 4, 2014

I'm trying to compare a Currency formatted field to a Number formatted one and getting wrong results.other then formatting the Number as Currency too ? Tried to use Val() but the Currency returned 0

View 7 Replies View Related

MS Access Lookup And Compare A Range Of Number...is This Possible ?? Please Help !!!!

Jul 17, 2006

I was wondering can MS Access do a range compair look up. I have 2 tables. One is the Info table and the other is Rate table. The Info table has 3 columns like this:

[code]...

I want access to read the Info table and pick up the values in the FICO, LTV and Type and compair it. Like with the above example. In the Info table FICO = 622. So then I want it to carry the number 622 down into the Rate table and compare that the number 622 is in FromFICO and ToFICO column to find where does 622 falls in between. In this case there are nine rows in the Rate table that 622 is between 620 and 629. The next criteria is the LTV number in the Info table. It's 76. So back into the Rate table looking for the number 76. And 76 is happen to fall between 75.01 and 80.00 in the FromLTV column and ToLTV column. Next back to the Info table is the Type column which is 2. Now back to the Rate table to look up the Type column for Type = 2. So from FICO = 622 , LTV = 76 , Type = 2...So the rate I want to populate is 0.25...So is this possible to do in Access?

View 1 Replies View Related

General :: How To Compare Strings

Aug 23, 2013

How to compare 2 strings in Access in order to determine one is biggest or highest than the other??

View 2 Replies View Related

General :: Compare Two Tables And Update Changes

May 15, 2013

i import data into a Landing table, this will always contain d days woth of data, i then run an apend query into another table were the records are stored up to 20 days, the problem i face is some of the record data may change for eample a field called ShippedQty may be 0 in a monday but on tuesday it may say 5

Import on monday
Product ShippedQty Date
123____ 0 ________01/01/2013

Import on Tuesday
Product ShippedQty Date
123____ 1 ________01/01/2013

I need to look at any changes and updatein the master table, is there a way to do this.(I cannot change the import as its the only way i can get the data)

View 3 Replies View Related

General :: Compare Data In A Column And Update If Required

Feb 28, 2013

I have a table "Customer Plans" with 100s of rows of data with 3 columns

'Name', 'Company', 'Plan Type'

I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:

If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Customer Plans.Name' column

e.g.
if
Name = Anna B
Company = Corp&Gov
then
Customer = Anna B

I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.

If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'

if
Name = Anna B
Company = Dealer
then
Customer = Dealer

View 1 Replies View Related

General :: Compare 2 Tables Where One Record Matches And Another Doesn't

Jun 26, 2013

I have table A with a list of Work Instruction references (WIREF) and a Issue number (ISSNO).I have Table B wth employees and they too have a Work Instruction (empWIREF) and Issue number (empISSNO).I want to check if the Work instruction Issue has changed since the employee was trained

check:
WIREF = empWIREF and ISSNO <> empISSNO for all records in table B

View 2 Replies View Related

Auto-number PK And Prior Numbers

Mar 26, 2013

I have an Access DB with a VB 6.0 front-end. I have a table with a PK that uses an auto-number. I deleted from data from this table in the past, then re-added it back in again just now. However, now when I go to enter a record in this table from the VB executable, I am getting an error for duplicate keys--as the auto-number tries to use a number that already exists. This happens when I just enter a record directly in the table in the DB also.

View 1 Replies View Related

Numbers Only Input Mask, If Checking For Number

Aug 21, 2006

I have a control called GoToId which afterupdate moves the user to a specified record where ID=GotoID. I want the control to only take numbers as input. I used input mask: 999999;;" " but with it the user can still press a letter on a keyboard and even though the letter doesn't get typed in, I think an empty string gets passed as value.
If I can't do it with masks, what's the simplest if statement in vba that will check if the value is a number and positive? (IF the value is Null, "", or anything else I want the if expression to be false)
I know how to do it with several if statements checking if is null, or if "", but there must be a clean way of asking it in a single expression.

View 3 Replies View Related

Forms :: Filter Records Between Two Numbers IF A Number Is Present

Mar 21, 2013

I have two text boxes and I'm wondering how I can filter my records of my table (through my query), between two numbers on my form. So for example lets call the two text boxes Num1 and Num2, the logical process is:

Code:
IF Num1 or Num2 IS empty
THEN filter records with "*"
ELSE IF Num1 and Num2 contain a number
THEN filter records between those two numbers

SQL, Me.Filter, BETWEEN in criteria. How I can accomplish this.

View 7 Replies View Related

Access Linking A Job Number To Multiple Task Numbers

Dec 2, 2014

I have done multiple tutorials to get the fundamentals behind me, but seem to have a few issues on the best ways of setting out what I have in mind.So the set out I have used is creating a table called tblProjects - this will be the area where all new projects are added as they come in including details such as a ProjectID ProjectNumber ProjectDescrition Client Name etc. For costing purposes we also have task numbers that need to sit under an individual Project Number EG.

ProjecNumber: 13001

[code]...

So the plan was to create another table called tblJobTasks..How then does one go about linking the project number to the job tasks... and following on I suppose where I want to get to with this is on a form having a drop down that selects the job number then another drop down that has a list of tasks specified to that particular job so when the girls are doing the data entry from a time sheet she only sees the list of tasks associated with the particular job selected in the first place..

View 1 Replies View Related

Queries :: Adding Sequential Numbers To Generate A Unique Reference Number

Oct 5, 2013

Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.

two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship

I also have a query (Named: FullLog) that picks up the following data from the tables:

Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count

The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:

Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]

This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.

Effectively what I want to be able to get is something of that resembles the following

DateReceived - ReferenceNo

051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001

View 6 Replies View Related

Queries :: Replace Middle Four Numbers With A Character To Make Entire Number Unreadable

Aug 2, 2013

I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?

View 14 Replies View Related

Queries :: Get Average Repair Price Of Multiple Part Numbers With Same Root Number

Nov 19, 2013

I need to get an average repair price of multiple part numbers with the same root number i.e.

8 each 8TJ124GGN1
4 each 8TJ124GGM1
7 each 8TJ124GGP1

First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.

If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:

AdjustedAvgLabor: Int((Sum([LaborPrice])-Max([LaborPrice])- Min([LaborPrice]))/(Count([LaborPrice])-2))

…to get a more accurate average by throwing out the high and low values, I get a:

“…expression too complicated to be evaluated”

when I try to average the averages in the second query using the AVG function.

I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.

View 8 Replies View Related

General :: Getting Invoice Number Field To Auto Generate Next Number

Jun 2, 2014

I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0

Code:

Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

invoice numbers are 04024, 04025 etc...how I keep the formatiing?

View 5 Replies View Related

General :: Auto Number PK Change Starting Number?

Dec 18, 2013

I have a table with an auto number PK. This table will contain orders. I'd like to use the PK from this table as the Invoice number on the invoice. I'd like to have it start at a number other than "1" just because it looks better on an invoice. I don't know how to do this. I looked at the table design to see if there were options available to me there but couldn't find anything. Is it possible? (I do not know how to use code.)

View 7 Replies View Related

General :: Field Auto Add Numbers To Value

Jul 11, 2012

I have a field for SSN. If the SSN has a 0 or 00 as the first numbers it auto deletes those numbers. Is there a way in access that when the record is visible the 0's are relpaced so that the SSN will read the 9 digit. Also i have another fiels EIN with 7 digits an i need for there to be 2 9's in the beginning. I was goingto use vba but when i go to yp it i only get .value <

Code:
Me.SSN.Value

Value is tho only option to get. IDK y it does this or how to get it to shop if there is a ay.

View 1 Replies View Related

General :: COMPARE DATE Variable To DATE / TIME FIELD

Feb 18, 2014

In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.

View 13 Replies View Related

General :: Adding Two Numbers While Inputting The Information?

Apr 1, 2014

I have a database where on the form we are just put in total deductions. We want to make sure that the total deductions is correct so while we are entering this number we want to do an excel thing by hitting = 1 + 1. Anytime we do that we are getting an error message. I really didn't want to have to set up a table to store these different amounts because we really only need the total. Is there a way to do that? I changed the format from currency to general but I'm still getting the error.

View 2 Replies View Related

General :: Generate Numbers With Fixed Interval

Sep 9, 2014

I have a range say 12.03 to 13.11.

I need to generate all numbers falling within this range with 0.01 increment.

(This increment is based on the numbers; if the numbers have 2 decimals, it should be 0.01. If the numbers have 3 decimals, the increment should be 0.001. Both the numbers will have the same number of decimals)

The answer is: 12.03, 12.04, 12.05, etc. etc. till 13.10, 13.11.

Is there a way we can do it in MS Access ?

View 14 Replies View Related

General :: Display Numbers As 0001 Or 0012

Dec 4, 2012

- I have a table "Quotes" with fields "UserID" and "Quotenumber"
- I have a form "Make quote" with a textfield "txtQuotenumber" and "txtUserID"

With the following code "txtQuotenumber" gets the highest quotenumber for the current "txtUserID" from the "Quotes" table:

Code:
=DMax("[Offertenummer]";"[Offertes]";"[Offertes].[Gebruikers-ID]=" & Formulieren![Offerte formulier]!txtGebruikersID)+1

It displays quotenumber 1 as "1" but what I actually want is it to be displayed as "0001". Quote 211 will then be displayed as "0211". I tried this by entering "0000;;_" into the property "input mask" of "txtQuotenumber" but that results in "1".

View 5 Replies View Related

General :: Phone Numbers From Excel To Access

Jul 22, 2015

I have an excel spreadsheet linked to a table n Access. I have phone numbers that I would like to transfer. I set a format in Excel that made it automatically change to the (###) ###-####, but when I look on the table in Access it shows up ###-#######.

View 2 Replies View Related

General :: Create A Form That Allows To Search For Particular Quote Numbers

Jul 8, 2013

I am creating an access database to store customer details as well as quote information. I have created a form to input quote details that our office can complete when a customer calls to complete a quote. I need to create a form that opens as a new form every time we click on the form to input a new quotation. How do I do this? At the moment when I click on the form, the form opens but the details of the last quotation are stored on the open form.

I have no training in access at all and am self taught so far so by no means an expert.I also want to create a form that allows you to search for particular quote numbers, so if someone calls and asks about a particular quote number we click on a page that says 'search quotes', input the quote number and the form opens with all the details of that quote.

View 2 Replies View Related

General :: Generating Random Project Numbers With Date

Jun 7, 2012

How to generate random project numbers that reset automatically every month? I want to generate project numbers that look like this:

1234/06/12

where the 06/12 stands for the month and the year while the 1234 is a randomly generated number.

I want the random number to be four digits long.

I already have a number of projects with similarly generated projects from an existing database. Can I import these projects without changing their project number?

View 3 Replies View Related

General :: Query Criteria - Data In Fields Are Numbers

Nov 7, 2014

I have a query criteria where the data in the query field are numbers.

The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.

Code:
Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null

Also tried

Code:
[FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null

Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox

Code:
Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2]
Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5]
Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]

[Code] .....

View 5 Replies View Related

General :: How To Have Supplementary Auto Numbering Column To Give Invoice Numbers

Dec 29, 2014

I have the auto ID number set up set up on my Access database which gives me membership numbers. How do I have a supplementary auto numbering column to give me invoice numbers. You used to be able to do this automatically up until a few years ago. Now I have to enter them manually.

View 8 Replies View Related







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