Comparing Values In A Query

Dec 7, 2007

Okay I have a query that pulls from a table. It's pretty simple - nothing big.

The query will be used to correct information in some sales tax data.

I have 3 colums with sales tax info, and they should all be the same. I want to compare each column to show only those which don't match each other.

NetTax, TotalTax, and TaxB are the fields.

In the query, for the criteria of NetTax, I put in:

<> [TotalTax]

When I run the query, I get results, but they are all equal.

EG:

4.35 4.35 4.35

I don't want them to show, and not sure if that's the right way to go about it or what.


If I run without that criteria, I get 21,600 results.

With that in the criteria, I get 304 results. So it is working somewhat...but for whatever reason some don't seem to be seen as being equal? I don't get it. Data type is number and they are formatted the same way.

View Replies


ADVERTISEMENT

Comparing Values In A Query

Dec 17, 2007

Hi...
I will try to keep this succinct.

I have a query that totals a column in a sub-form. The query also gets a sum from the main form for the same record. Is it possible to have a message/colour change/warning if the total of the column in the sub-form exceeds the other number in the query? I would need this comparison to take place within this query, rather than creating a new one.

Thanks!
Kathryn

View 8 Replies View Related

Comparing Values

Oct 28, 2007

i want to know if it is possible to compare values (a string) stored in a field with a field of another table?
for example, i have a table named Courses, it has field Course ID...and Course Requirements.
the field Course requirements can accept only 3 value: A,B and C.
i have used a combo for this.
then, i have Student qualifications table. i have also designed a form to input student qualifications.
my problem is that i have to compare qual. of students with the req of the course, to allocate them a specific course. if both values meet, the db automatically allocates the course to the student.

View 1 Replies View Related

Comparing Values From Table1 And Table2

Sep 10, 2006

Hello everyone! I've found much use of this forum recently as I am working on a database concerning my work.

I'm not a newbie working with access but this problem seems confusing at the beginning.

I've got 2 tables
1) Information about produced details. Article, Mass etc.
2) Mass constants
Mass min. Mass max. Constant

What I need, is to build a query that compares the mass of the detail with the range mass min. and mass max. and shows the mass constant that the detail belongs to.

Has anyone tried to solve this kind of issue before?

Greets from Estonia,
K

View 1 Replies View Related

Comparing Positive To Negative Values

Jan 20, 2014

I'm doing a bank-rec database, so on the one hand I'm comparing positive debits, to negative credits. How do I query to get, say, "Bank1 -$1000, Bank2 $1000"

View 1 Replies View Related

Comparing 2 Tables To Find Duplicate Values

May 9, 2006

Hey all

Im rather new to access but have to use it at work. Ive been asked to see if there is a way to delete duplicate records from a table.

Now, I have 2 tables. The 1st table (tbl_list) contains records of various customer details. This list is old.

The second table (tbl_new_list) contains new customer details.

We have found that we have the same customer details in tbl_new_list that are in the old table.

Im looking for a way to compare these 2 tables so that the values in tbl_list are not in tbl_new_list.

I have tried numerous methods using append queries but nothing seems to work.

Thanks in advance people

P.S the data will be compared through a telephone number.

View 1 Replies View Related

Queries :: Comparing Two Values From Rows In Same Field

Jul 10, 2013

I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].

The idea of the table is that you can input and update the value of each product on a given day.

I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..

A picture of what the table looks like is attached for reference.

View 9 Replies View Related

Queries :: Comparing Text Within A String To Keep Only Unique Values

May 15, 2013

I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:

initial field:
xxx;yyy;ppp;yyy

targeted result:
xxx;yyy;ppp

How this could be achieved ?

View 5 Replies View Related

Query: Comparing Two Tables

Mar 22, 2006

I'm trying to write a report which initially I didnt think would be hard to do, but when it came time to write the underlying query I realized it was a bit above me.

I cant give away too much of the database structure here, due to legal issues, so sorry for being vauge. Hopefully I can give enough information that someone can get me started.

I have three tables that will be used in this query. One is called NAMES and has all of personal details of contacts. The only fields that are needed from this table are the primary key and citizenship field.

The other two tables are virtually the same, and they are the two being compared. One is REGISTRATION and the other APPLICATION. Essentially what I want here is to be able to compare the number of who applied to the number who actually registered.

Here are the pseudo-tables to give you a better picture:

NAMES
nID
FName
LName
DoB
Citizenship

REGISTERED
programCode
startDate
endDate
nID (foreign key)

APPLIED
programCode
startDate
endDate
nID (foreign key)

I've tried a few different approaches which worked in my head, but when it came time to run the query the results were different. Any suggestions on how I should tackle this?

View 3 Replies View Related

Query Comparing Two Other Queries?

Mar 24, 2006

I want to compare two queries as follows: Select all the records in query1 where the value in fieldA does NOT also appear in any record in fieldA of query2. Is there an easy way to do this using the query tools or will this require a vba function?

View 3 Replies View Related

Comparing Rows In A Query

Aug 26, 2006

is it possible to compare values in 2 different rows? for example if the query returns this:

1 | $5.00
2 | $6.00
3 | $20.00
4 | $30.00

i want to find out the cost difference between 1 and 2 which will be $6-$5 and then 2 and 3 which will be $20-$6 and so on. how can i make this happen in SQL? any suggestions?

View 6 Replies View Related

Comparing Two Fields In A Query?

Jun 18, 2007

I am trying to compare two different fields in the same query and return the most recent date in some another field. Would I be able to do this and if so how?

Any help would be greatly appreciated.

View 14 Replies View Related

UNION Query Using For Comparing?

Oct 2, 2007

Hello again...

Is it possible to use a UNION query for comparison between to tables or queries?

If so, please advise how to

example:
one query consists of a selection of books, grouped by category for a student
the second query consists of all books, grouped by category for a student

The purpose of the new union query is to check if the student has all the applicable books in his booklist; if the student has forgotten to pick a book for his list, this new query will show the one(s) the student forgot.

Kind of cryptical this example, hope i could make it clear enough...

View 4 Replies View Related

Comparing 4 Tables With A Query

Oct 13, 2004

I have 4 tables (one for each quarters data) each with the same fields:
Fund
FundFamily
Value

I have a query for each table that sums the values for each FundFamily (if there are multiple funds with the same FundFamily, it only shows the sum for the Family).

I am wanting a query that will show all of the different FundFamily fields from each table, but only once (say there is a Value in one quarter, but not the next). How is the best way to go about this?

Does what I'm trying to do here make sense?

-GorillaBoze

View 2 Replies View Related

Update Query Comparing 2 Tables?

Dec 8, 2005

Hi
I have such situation:
i have tables [k] and [r] with street and city field.

I would like set on field[dubel] in the second table [r] in the rows where the street and the city are the same for the both tables.
There could be one to many relations. It means many fields in [k] could have the same as in [r]

I've tried with this query but it marks all the fields....

UPDATE r SET dubel=1 where EXISTS ( SELECT r.str, cit
FROM k, r
WHERE (([k].[str]=[r].[str]) AND ([k].[cit]=[r].[cit])));

when i'm using just select part, it gives me good results.....
Can Anyone help ME?
THANKS

View 1 Replies View Related

Update Query Comparing 2 Tables?

Dec 8, 2005

Hi
I have such situation:
i have tables [k] and [r] with street and city field.

I would like set on field[dubel] in the second table [r] in the rows where the street and the city are the same for the both tables.
There could be one to many relations. It means many fields in [k] could have the same as in [r]

I've tried with this query but it marks all the fields....

UPDATE r SET dubel=1 where EXISTS ( SELECT r.str, cit
FROM k, r
WHERE (([k].[str]=[r].[str]) AND ([k].[cit]=[r].[cit])));

when i'm using just select part, it gives me good results.....
Can Anyone help ME?
THANKS

View 1 Replies View Related

Queries :: Comparing 2 Fields Query?

Mar 11, 2014

Trying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?

SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");

View 1 Replies View Related

Comparing Multiple Dates And Times In A Query

Mar 8, 2007

Hi there,

I'm trying to 'rank' x5 categories that overlap. To determine the ranking i'm utilising date & time (if the dates are equal). I've managed to do this comparing x2 of the categories but struggling with the logic for x3,x4 & x5 categories

I have the data in separate tables by category date & time and in one large table.

Hoping someone can assist as my head is starting to hurt.

Thanks

View 4 Replies View Related

Queries :: Comparing Pairs Of Records In A Query

Aug 9, 2013

I need to identify pairs of records by their ID and date fields, then compare their number values to get a text result.

I have a query that has the following fields

AuthInstanceID, ConsumerID, AuthNumberID, AuthStart, AuthEnd, PSRUnits, BSTUnits.

Sample data is below (PSRChange and BSTChange are fields I want to self populate based on the comparison I'm trying to do)

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

What I need to do is compare these records in the following fashion:

Identify pairs of records where ConsumerID is the same AND AuthStart dates are successive. Then compare PSRUnits of more recent record (by AuthStart) with the previous record (by AuthStart) and determine the change of PSRUnits between them. If change is positive, "Gain" in field PSRChange, "Loss" for a loss, "None" for no change, and "Null" if there is an error due to no previous record to compare with. Do the same with BSTUnits/BSTChange

I've tried adding the following fields, but this did not work

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

View 14 Replies View Related

Diagonalize A Table (Comparing Items In A Crosstab Query)

Mar 17, 2007

I posted in this section because I don't know a better category.

I have data (from a cross tab report) that looks:
16763

and I would like it to look like:
16764

So that the 'X's make a diagonal.... It helps the humans understand the data better. :D

I was thinking of exporting the query to excel and then running a script that would look for 'X's in the first column and then move those rows to the bottom. And so forth for each column, until they are in a diagonal like above. (Because the rightmost 'X's are moved to the bottom last).

Am I barking up this tree the wrong way? Does someone know how to do this?

EDIT: I have multiple groups like 'Date' in the above example. Basically for each group I would like it to be diagonal. I also have some groups that use 'Feature A, Feature B, ect' in place of Date, and for those groups I need to leave the rows undisturbed.

View 1 Replies View Related

Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').

View 5 Replies View Related

Comparing Tables

Aug 17, 2006

I have two tables: -

1) Sales data
2) Claims data

I want to anaylse Premium (contained in the Sales Data Table) against Claims (contained in the Claims Data Table). The result I want to achieve is to show a loss ratio (Premium / Claims as a %). I need to show this on a per policy basis (each Table has a Policy Number Column).

Im new to Access and do not know how to go about this.

View 3 Replies View Related

Comparing Tables

Jul 12, 2005

Hi,

I have imported data from an ERP system into an Access table. This big table contains information about invoices processed last month. 2 of the columns are currency code and payment batch code.

Actually, there are rules that accountants should follow, but they can make errors. We would like to find those error using this Access database.

One rule is that only predefined payment batch codes should connect to a
currency code.

My idea was to create another table, where controllers are able to type in these rules like

EUR CIE
USD CIU
HUF BKH

This table has only 2 columns: currency code and payment batch code and no primary key has been defined.

The 2 tables were joined with the currency code and a query printed only those transactions where the payment batch code from the big table <> the payment batch code from the new table. This shows where accountants made an error.

This is the query:

SELECT [475 Master].*
FROM Tbl_Pmt_Btc INNER JOIN [475 Master] ON Tbl_Pmt_Btc.Cur_Code2 = [475 Master].Currency
WHERE ((([475 Master].Pmt)<>[Tbl_Pmt_Btc].[Pmt_Btc_Code]));

475 Master is the big table, Tbl_Pmt_Btc is the new table that can be modified by the controller.

This worked until more than one payment batch code were assigned to a currency code.

EUR CIE
EUR CIU

It seems that Access can use only one of them.

Can you please advise what to do in order that Access takes both rows into
account and the query prints those transactions booked in EUR where the payment batch code are neither CIE nor CIU.

Is there a simple solution without any programming?

Thanks,
Peter

View 2 Replies View Related

Need Help Comparing Tables

May 17, 2005

Hello - I am new to Access and don't know VB.

I am trying to compare 2 tables that each contain the following information:

Fields:
Document Number
Date
Changes (Amendments, etc.)

I would like to see the following on a report:

A - If a document number is on Table 2, but not on Table 1, show these on a "missing documents" report.
B - If a document number exists on both reports, however the dates do not match. Example: Document 123 is on Table 1 with a date of 1/1/1999 in the date field. Table 2 contains the same document in the document number field, however the date field contains 2/15/2005. SHow these on a "Date Differences" report.
C - If a document matches on number and date, however the changes field does not match.

Ultimately, I would like to add other fields to compare as well.

Hope this makes sense to someone out there!

Thanks!

View 1 Replies View Related

Comparing To Columns

Jun 15, 2006

If I have a date column and another column with another consistant date, Can I make a criteria to compare one to the other. Ex:

END_DATE Today's_DATE
06 Feb 05 15 Jun 06
12 Feb 05 15 Jun 06
01 Jan 06 15 Jun 06
26 Mar 06 15 Jun 06
18 Aug 06 15 Jun 06
28 Dec 06 15 Jun 06

Can I make a criteria that will only show the END_DATE data that is before Today's DATE? Does it need to be a new column or can I place it in the criteria of one of these column.

View 1 Replies View Related

Comparing Records

Aug 16, 2006

Hi,

I need to compare records in the same table. I know that this is typically accomplished by joining the table with itself. However, this is not entirely suitable for my needs. I need to compare the first record to every other record - excluding itself - and then compare the second record to every other record excluding itself AND the first record. I want to continue this pattern for all the records. Is there any way I could go about doing this?
Any help would be much appreciated.

Edit: Each record has an autonumber generated key

View 12 Replies View Related







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