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 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

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

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

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 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 2 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

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

Modules & VBA :: Union All Query - Transposing Columns To Rows With Variable Columns?

Aug 8, 2013

I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.

Example:

original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5

original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22

The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).

When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".

Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.

View 2 Replies View Related

Queries :: How To Compare One Record To Previous Record

Feb 20, 2014

How do you compare one record to the previous record? I have a query that shows items sold. It is sorted by day with today on top. I would like to create a column that has a 'thumb up' or 'thumb down" if todays was a better day than yesterday.

View 1 Replies View Related

Compare New Record With Existing

Apr 19, 2005

My database tracks employee training.

I have tblClasses, tblEmployees, and tblRegistrations. I have a registration
form with EmpName and subformClasses, which allows me to register one
employee for several classes at a time.

My registration form has an OK button. I need a message to appear (on btnOK click) before adding a new record to the registrations table that indicates if the employee has already had training for the one or more of the classes selected in the subform. Then the option to "Yes" add the record anyway, or "No" cancel the record.

I have everything working except, I can’t figure out how to do the
comparison with the subform and generate my "Yes/No" option. I’m hoping that one of you wonderful access geniuses will have a relatively simple suggestion for me.

--
Thanks in advance!
Shel

View 4 Replies View Related

Modules & VBA :: Finding Duplicates In A Table And Recording Values In That Record

May 2, 2014

I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.

Let me try to explain better.

example:

I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.

Quote:

Name DOB
bob 19800201
Sam 19761211
Jim 19800201

The output I would like is to have Name = bob & Jim DOB = 19800201.

I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.

View 1 Replies View Related

Modules & VBA :: Create Unique Reference Number Based On Field Values For Record

Jun 18, 2015

I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.

The first field is called Operation Number.

The second is Bag Number.

The reference number needs to be in this format: 19C.3.1

Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.

This is kind of like library catalog numbers. Not sure how to do this.

View 2 Replies View Related

Compare Current To Previous Record In Query

Aug 14, 2007

I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?

View 2 Replies View Related

Modules & VBA :: How To Compare Time

Aug 12, 2014

The following code gives me error message. There is problem in Format function. How can I write using VBA that "Scantime is less than 08:00"

Code:
strsql = "SELECT Count(BatchNo) AS CountOfBatchNo " _
& "FROM Table1 " _
& "GROUP BY ScanDate " _
& "HAVING ScanDate=" & J & " and Scantime< format("08:00:00","hh:mm:ss")

View 10 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

Modules & VBA :: Compare The Last Two Data-points With 1?

Jul 19, 2014

one more thread of mine today

I have this table:

-> tblTest

Code:

test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company A | 01.01.2010 | 0,90
2 | Company A | 02.01.2010 | 0,95
3 | Company A | 03.01.2010 | 1,10
4 | Company A | 05.01.2010 | 1,05
5 | Company B | 01.01.2010 | 0,9
6 | Company B | 02.01.2010 | 1,20
7 | Company B | 03.01.2010 | 1,15
8 | Company B | 05.01.2010 | 0,95
9 | Company B | 07.01.2010 | 0,80

The important level is 1,00

Now I will, for each company, check:

- Is the value of ID-1 >1 AND the value of the ID-2 <1, THAN give me a 1
- Is the value of ID-1 <1 AND the value of the ID-2 >1, THAN give me a -1

In this example the result should look like this:

Code:
test_id | test_Name | test_Date | test_value | test_Code_result
-------------------------------------------------------------
1 | Company A | 01.01.2010 | 0,90 |
2 | Company A | 02.01.2010 | 0,95 |
3 | Company A | 03.01.2010 | 1,10 |
4 | Company A | 05.01.2010 | 1,05 | 1
5 | Company B | 01.01.2010 | 0,90 |
6 | Company B | 02.01.2010 | 1,20 |
7 | Company B | 03.01.2010 | 1,15 | 1
8 | Company B | 05.01.2010 | 0,95 |
9 | Company B | 07.01.2010 | 0,80 | -1

Probably I have to carry out this in vba.but i dont know how I can calculate across the datapoints (rows).

View 14 Replies View Related

Modules & VBA :: How To Compare Two Records (Rating)

Jan 5, 2014

So I have a table:

table1: company_name, year_month, company_rating, rating_change

For each company, I would like to compare the rating from 201201 and 201202 (and keep going for each month) and if there's a change in the rating, update rating_change to the difference in rating values.

The company_rating is a value from 1 to 20.

How should I go about doing that?

View 6 Replies View Related

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

Values In Certain Columns

Nov 14, 2007

In my table i have three columns for data entry. They are Subject 1, Subject 2, Subject 3. I need to be able to produce and query that will bring results up if for example 'english' is in anyone of these columns. I know how to do it for just one but i need it to be able to check all three columns?

Hope this makes sense

Sam

View 1 Replies View Related

Modules & VBA :: Compare Column Names And Sequence

Sep 5, 2013

I want to be able to compare two tables to see whether their Column Names & sequence are the same.

View 1 Replies View Related

Putting Row Values Into Columns

Nov 17, 2006

Hi,

I have been trying to figure this out for a long time. Any tips/helps is much appreciated it.

I have a table with 3 columns(SSN,DeductionType,DeductionAmount). Let say I have 100 employees. There are 3 records per employee, because each have 3 different deductionType(TypeA,TypeB,TypeC).

I want to create a queryTable that will result with 1 record per individual, with TypeA,TypeB,and TypeC as column names and the DeductionAmount as their values. Make sense? Below is an example of what the new table should look like:

SSN--------------TypeA-------TypeB-------TypeC
022-58-5898------ $10.35-----$25------------$14
036-89-5487-------$5----------$45.69---------$47.33
...
..
.

Thank you VERY much!

By the way, if QueryTable is not the method to do this, please let me know how.
Joe

View 4 Replies View Related

How To Put Two Columns' Values Into One List-Box

Nov 14, 2006

Hi All,

I have a list box and I populate it with records from a "Select" query. I am using VBScript to do this. Here are my codes:

gr1_list.RowSource="Select Criteria from CriteriaList WHERE Criteria='" & cvalue & "';"

My "CriteriaList" table has two columns; Criteria and CriteriaDescriptions.

The question is, what if in my list box I want to be able to see both 'Criteria' and 'CriteriaDescription?' How would I code this?

Thank you.

View 4 Replies View Related







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