Compare 2 Values From Fields On A Table

Mar 6, 2006

Hi there

This is probably quite easy but I cannot make it work so would appreciate some help. I have a table containing vehicle information. I have 2 fields, Current Mileage and Max Mileage. I want to do a comparison of the 2 fields and should the Current Mileage be greater than the Max Mileage, the query returns the Vehicle Registration. Test data has been entered in the table I am querying and I was expecting 1 value to be returned. However the query as I wrote returned either all the vehicle Registrations or none of them. Can someone help with this please.

Thanks in advance for the assistance
Craig:)

View Replies


ADVERTISEMENT

Compare Values Of Columns Within The Same Query Table

Nov 19, 2006

Hi,
I am trying to compare two columns' values within the same QUERY table, but I kept on getting the "Data type mismatch in criteria expression" error. What am I doing wrong?

Here is my Select Query Statement:
SELECT qryDedparmDedetail.EMP_ID, qryDedparmDedetail.[Employer Amt], qryDedparmDedetail.[Employer Actl], qryDedparmDedetail.[Admin Amt], qryDedparmDedetail.[Admin Actl], qryDedparmDedetail.[Employee Actl], qryDedparmDedetail.[Employee Amt], qryDedparmDedetail.FirstOfSTATUS, qryDedparmDedetail.FirstOfAGENCY, qryDedparmDedetail.FirstOfTITLE, qryDedparmDedetail.FirstOfFORMAT_NM, qryDedparmDedetail.RepUnit, qryDedparmDedetail.FirstOfDEDTYPE_CD1 AS Expr1, qryDedparmDedetail.SumOfNBR, RepUnit.REPUNITDESC, qryDedparmDedetail.LeftType
FROM qryDedparmDedetail LEFT JOIN RepUnit ON qryDedparmDedetail.RepUnit = RepUnit.REPUNIT
WHERE (((qryDedparmDedetail.[Employer Amt])<>"Employer Actl") AND ((qryDedparmDedetail.LeftType)="01"));

View 2 Replies View Related

Modules & VBA :: How To Compare Values Between Input Form And Table

Nov 12, 2013

I am making a dp for end of day sales, and i want to compare data in a table to the numbers inputted in a form and see whether this value is higher or lower. the table displays the target sales figures for each day, and the form allows the area mangers to enter in the current day figures.

View 3 Replies View Related

Queries :: Compare Data In 3 Fields In 1 Table / Group By Largest

Dec 4, 2013

I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:

PRODUCT table:
ProductName
Chemical
ChemicalAbstract
PhysicalState
NFPAHealth
NFPAFlammability
NFPAReactivity

qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.

The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.

In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:

Flammability
Rating 4
Product 1
Product 2
Product 3

Rating 3
Product 1
Product 2
Product 3

Rating 2
Product 1
Product 2
Product 3

Health
Rating 4
Product 1
Product 2
Product 3

[code]....

and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.

View 4 Replies View Related

How To Compare Time Values?

Nov 15, 2007

Hi.
I need create a query that can compare different time values.
My time field format something looks like 12:59 PM.
And compare other fields that has +- 10 minutes from my input.
It means when I input 12:00 PM to the query inputt then it lists records that have times 11:50 AM and 12:10 PM.
But I don't know how to compare these..they are not natural numbers..but they aren't text niether..
I tried to do it
[Arrive_Time] + 10
[Arrive_Time] - 10
in the criteria..but..doesn't work.

View 4 Replies View Related

Compare Values Between Two Subforms

Jul 16, 2007

Any idea if there's a way to compare values between two subforms on one main form?

I have a Required Inventory and and an Actual Inventory subform. I want the Actual Inventory field to be conditionally formatted to turn red if it is less than the corresponding required field and green if it is more. I know how to use Conditional Formatting, but I don't know how to reference the values in the next form.

Note: I have no problem using conditional formatting compared to values in the parent form. The syntax I used for that was:

[Forms]![frmMain]![txtControl]

But still no luck referencing subform controls.

Thanks.

View 1 Replies View Related

Compare Sum Of Values In Rows With Same ID To Another Value

Feb 6, 2013

I need to compare the sum of a values in a column of rows with the same id. For example:

Code:
ID Value1 Value2

122222 20 40 (these are okay, the sum of Value1 in rows with same ID equal value 2)
122222 20 40
333333 100 100 (ok)
111111 50 100
111111 50 100 (not ok, the values with same id add to 150, so the first row containing this id should be deleted so the the total of Value1 equals Value2)
111111 50 100

View 1 Replies View Related

Compare Negative Values With Positive

Jun 5, 2013

I am trying to to find a way to show all the positive values with nagative, in order words, if the value is 10,000 is should be followed by corresponding -10,000 so that the user could investigate if the entry is a reversible GL.

View 2 Replies View Related

Modules & VBA :: Compare Values Of Several Columns Of A Record

Jan 23, 2014

I would like to compare the values of several columns in the same record and remove any duplicates. So...

ID | Col1 | Col2 | Col3 | Col4 | Col5
-------------------------------------------
1 | A | B | C | D | C
2 | C | C | C | D | D
3 | A | A | B | A | D

In the above example I would remove one of the Cs in row 1.

Two Cs and one D in row 2 and two As in row 3.

At the moment I have the value of each column stored in a variable and each one is compared against each other using LOTS of If statements.

View 5 Replies View Related

Compare Data In Different Tables And List Non Matching Values

Nov 2, 2007

Hi

I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below

Table 1

xyz - A1 - Do not use
ABC - A4 - Use

Table 2

123 - A1

The result of the query should show me

A4 (as this does not appear in table 2

I am not too familiar with Null values as there will be many null values that i want to ignore.

Any help will be appreciated.

View 4 Replies View Related

Compare Memo Fields

Feb 5, 2008

Can someone tell me if this can be done somehow.

I have a date field, ID field and a memo field (I have to have this memo field).
I want to compare the memo fields by two certain dates and find out what doesn't match and if the ID field is blank. I have been trying with quaries but no luck.

I am not sure what forum this would fall under so I put it in general. feel free to move.

View 7 Replies View Related

Compare 2 Fields To Get 3rd Field...

Apr 13, 2006

Hello,

I have 2 tables:

tblProviders
ProviderName
ProviderAddress
ProviderType
LocationCode
FacilityType
Etc...


tblFacilityType
ProviderType
Location
FacilityType

In tblFacilityType, the FacilityType field is a combonation of ProviderType and Location. Each of those fields are alphanumeric. What I want to do is have the FacilityType field in the the tblProviders table to look at the Location and the ProviderType fields in the tblFacilityType table to determine what the FacilityType field in the tblProviders table should be.

Hope that is understandable.

Thanks in advance for any assistance.

View 5 Replies View Related

Queries :: Need To Compare 2 Fields

Jun 13, 2014

Need to compare 2 fields in a query, and return the larger of the 2 values in a 3rd field.

As in:

Field A=10 Field B=6 I need Field C to = 10.
Field A=5 Field B=6 I need Field C to = 6.

View 3 Replies View Related

Modules & VBA :: Query To Compare Values To Show All Records Where Form Combo Value Is Selected

May 1, 2014

I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.

For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.

View 4 Replies View Related

Copying Values Of Fields From One Table To Another.

Dec 14, 2005

Ok so here is the problem I need help with,
I have a prices table that stores all the different price changes for each part I have in my Parts table what I need to figure out how to do is to copy the "Last" price for that part from the price table into the Price field of the Parts table or have some way of updating it automatically either by macro or what not - example:

Price Table:

PriceID (PKey)
PartID (Connected to Part Number ID)
Price
Price Added( Date & Time when price was added so I can groupby "Last" or "Max" in the Price Query I have made)

------------------------

Parts Table:

PartID (Pkey)
Part Number
Price

-------------

Now how do I take the value my Price query gives me for "Last" or "Max" Price using the last date for that parts price and have it automatically get copied into the Parts Table price?

Thanx

View 3 Replies View Related

Easy Question - Compare 2 Fields

Mar 12, 2006

I have a field called Badge_Number in Table A.

I have a field called Badge_Number in Table B.

In Table A Badge_Number has entries 101, 102, 103, 104, 105

In Table B Badge_Number has entries 102, 103

How can I do a query to get a list of entries that are in A but that are not in B. (Query result would be 101, 104, 105)

This is the only thing I have to do in Access, so after trying to learn this for a while, I have decided it would be much easier to get help at this time.

View 2 Replies View Related

Update Several Fields And Compare Strings

Aug 8, 2007

Have devloped a database to log all support issues for an application I work with.

I periodically update the database by importing from another database

for example my main table tbl_issues has fields IR_NO, DESCRIPTION and STATUS

I then import data from tbl_import which also has these fields. The following query works fine:

UPDATE tbl_support
SET tbl_support.STATUS = tbl_import.STATUS
WHERE tbl_support.STATUS <> tbl_import.STATUS
AND tbl_support.IR_NO = tbl_import.IR_NO

However I also want to do this at the same time:

UPDATE tbl_support
SET tbl_support.DESCRIPTION = tbl_import.DESCRIPTION
WHERE tbl_support.DESCRIPTION <> tbl_import.DESCRIPTION
AND tbl_support.IR_NO = tbl_import.IR_NO

How do I update both fields at the same time?

View 2 Replies View Related

How Can I Compare Fields With 2 Bound Columns

Aug 9, 2005

Hello..

In one of my tables I have a field with the following characteristics:

Listbox
row source: SELECT Category.CategoryID, Category.Category FROM Category ORDER BY Category.CategoryID;
row source type: table/query
bound column:2
column count: 2

This gives the user the opportunity to see the category as well as the categoryID when choosing, however once selected only the CategoryID (which is a number) is visible.

I would like to put the same option on one of my forms, but Im not succeeding at this. How can I do this? Is this possible?

This leads me to my next question: If I want to load the information from the form and compare it in VBA with the one from the table, is it seen as a number or a string?
I hope it is clear..
Any thoughts on how to deal with this??
Thank u..
Stacey

View 8 Replies View Related

Forms :: Compare 2 Text Fields?

Aug 27, 2013

Text fields name:

Password
confirmpassword

Trying to achieve:

on "After update" event for Confirm password a code needs to find if both Passoword and confirmpassword are having the exact character (Case sensitive).

Below code doesn't recognize the Case. It allows as long as both words are same:

Dim StrString1, strString2 As String
StrString1 = Password
srtString2 = ConfirmPassword

[Code].....

View 8 Replies View Related

Forms :: Compare Dates In Two Fields?

Jun 18, 2014

I would like to change the colour of field on a form if the date in that field is later than the date in another field.

View 9 Replies View Related

Queries :: Compare Fields In Different Records For Changes

Jul 18, 2013

I have a table called tblAuthorizations. It appears as follows

AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits
1374006036.........356679..........20255102....... ...4/22/13......7/21/13.....0..............416
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520
504564357...........469432..........20254788...... ....4/22/13......7/21/13....0..............520
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520

I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.

What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.

I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.I've tried adding the following fields, but this isn't working.

PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])

View 2 Replies View Related

Adding Value In Table Based On Values In Two Other Fields

Dec 3, 2007

Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!

View 12 Replies View Related

How Do I Read Input Values From Fields In Another Table?

Dec 10, 2007

Hi,

I have a query which uses manual input on two fields. I would like to automate this query by pulling the input values from another table.

How do I read the input values from the other table instead of having the users type them in ?

Al

View 1 Replies View Related

Help With Linking Calculated Fields To Table Values

Aug 28, 2006

I need to display a value from a table in a text field on a form based on another text field on the same form.

The table has records as follows:

Month/Year xx Month No xx Year No xx Month ID (xx shows break in columns)
Jan-06 xx 1 xx 2006 xx 24
Feb-06 xx 2 xx 2006 xx 25
Mar-06 xx 3 xx 2006 xx 26
Apr-06 xx 4 xx 2006 xx 27
May-06 xx 5 xx 2006 xx 28
Jun-06 xx 6 xx 2006 xx 29
Jul-06 xx 7 xx 2006 xx 30

In one text field I use the DateSerial function to calculate the value of the previous month (e.g.Jul-06). I need to return the MonthID value to the second text field based on the value stored against it is the table, so in case of Jul-06 it would show 30 in text field 2.

View 1 Replies View Related

Return Only One Record From Table With Repeating Values In Fields

Nov 7, 2006

I have a table like this


Stuff.
ItemNo Name ImageName
123 Foo 00123.jog
123 Foo 00FOO.jpg
123 FOO FOO123.jpg
456 bar 00456.jog
456 BAR 00BAR.jpg
456 Bar BAR456.jpg
...


I want to do a query to return just one row per unique ItemNo

So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog

I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.

I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.

View 3 Replies View Related

Modules & VBA :: How To Check If A Table Contains Blank Fields / Values

Feb 28, 2014

How can I check if a table contains blank fields/values.

If there are blank fields I want to replace them with 0.

View 4 Replies View Related







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