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 Replies


ADVERTISEMENT

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

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

Autofill Columns Of Table With Values From Another Table

Jul 10, 2006

Hello

I have two tables tblCList and tblCode. The primary key of tblClist is an autogenerated number which is the foreign key to the tblCode.

The tblCList has another column names sClist.

whenever I import values from Excel into the tblCode, I need Access to look up the text values from the sClist in the tblClist and insert the appropriate number corresponding to the entry in tblCode.

Please Help


Thanks

View 2 Replies View Related

Compare Table Query

May 1, 2007

Hello,
I am just wondering whether there is any query I could use to compare the data on, say, whether all data in Table A are in Table B although some records in Table B may not be in Table A and give me the record that is in Table A but not in Table B.

Thanks!

View 4 Replies View Related

Including Columns With No COUNT Values In Crosstab Query.

May 6, 2007

I have a columns that do not incur a count value within the time period of my query, yet i still want them to be displayed with a 0 or no value in the query view, how do i do this?

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

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

General :: Query To Separate Columns In Table

Aug 14, 2014

I have a table tblItemSold

ItemNum Date Sold
1111 Aug-10-2014 25
1111 Aug-9-2014 24
1111 Aug-8-2014 23
2222 Aug-10-2014 11
2222 Aug-9-2014 12
2222 Aug-8-2014 13
3333 Aug-10-2014 5
3333 Aug-8-2014 3

I want to write a query and transform above into

ItemNum Aug-10-2014 Aug-9-2014 Aug-8-2014
1111 25 24 23
2222 11 12 13
3333 5 3

View 3 Replies View Related

Queries :: Make Table Query With Calculated Columns

Oct 8, 2013

I am trying to run a make table query that involves using a linked table in Ms from a SharePoint list. However, a few columns I wish to include in my Make table query are calculated columns from the SharePoint list.

The make table query will not run saying "Calculated columns are not allowed in SELECT INTO statements".

Can any think of a solution or a potential work around to this?

I need to create a table with these columns in it.

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

Create Duplicate Table Using Same Form And Compare It With Original Table

May 1, 2012

Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.

Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?

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

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

Concatenating Various Values From Multiple Columns

Nov 25, 2005

I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.

So for example

Column1 Column 2 Column 3

B11 Europe

would concatenate as:

B11.Europe.

View 1 Replies View Related

Compare An Updated Table

Dec 5, 2007

I have 2 tables with the same structure but some of the data in one field is different

The tables are called Current and New

They both share a field called feesch but in the new table data is updated


The tables have share the same primary key of unique ID


How can I make a query in design view to show the feesch records that don't match?

Thanks in advance

View 4 Replies View Related

Compare Rows In A Table

Nov 14, 2005

Hi,

I have a table with four columns - A, B, C, D
I want to compare the values in columns A, B and C in row 1, with the values in row 2 and if they are the same, I want to copy the value that is in column D in row 1 into column D in row 2. I then want to move to the next row in the table and compare the values in row 2 with the values in row 3 in the same way. This will continue for all rows in the table.

Any help would be greatly appreciated as my VB is not up to much.


Thanks in advance

View 2 Replies View Related

Default Values For Columns (Date Related)

Jul 27, 2007

Hi,

I want to make the default value of my column "TimeonOW" to be the difference in months(past 12) from the entered date in "OWStartDate" and the current system date.

For for example
mm/dd/yyyy

CurrentDate - OWStartDate = TimeonOW
07/01/2007 - 05/01/2005 = 26

I played around with the DateDiff function but to no avail.
=DateDiff("d",Now(),[OWStartDate])
=DateDiff("d",Now(),#2004-31-12#) etc


The reason behind this is because I need to count how many people fall into different month span categories, maybe theres a better way?

Thanks in advance.

View 4 Replies View Related

Tables :: Can Split A Column Values Into 2 Columns

Jul 3, 2014

I have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?

This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.

View 14 Replies View Related







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