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 Replies


ADVERTISEMENT

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

Modules & VBA :: Comparing Rows / Columns In 2 Tables

Jun 25, 2013

I have 2 tables and 1 query. Table dbo_RepOrderItem includes columns:

RepId
OrderNumber
Item
ShipDate
SerialCardID

Table tbl_LBP Sales Location Num includes columns:

Location ID
Rep Region Code

What I've been doing so far through my code is working through a query 'CalculateTotal' which looks up a structure number (Which I enter through an InputBox) from table dbo_RoicStructure, gets the SerialCardID from the same table and collects the associated RepId,OrderNumber,ShipDate from table dbo_RepOrderItem and checks that the OrderNumber(s) returned exist in column Location ID in table tbl_LBP Sales Location Num. It also checks that Rep Region Code is not equal to 'INT' nor 'inte'.

My code for all of this works just fine. What I am trying to do now is simplify my results. I want to only return rows that do not have the same Item, OrderNumber, and RepID.

Fore example:

OrderNumber: Item: RepID:
11 3 1
12 3 1
11 4 1
11 3 1
14 7 4
16 8 8

It would now count 5 existing RepID(s) rather than 6 because it would have deleted the duplicated row. This should only be deleted in the user's table not the actual table.

And speaking of the user's table. What I have been doing so far is only returning the results (right now it returns 6, which as I explained above is incorrect) but I also want to return a table or query that would should the user the work behind the returned number.

This is my code:

Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant

[Code] ....

View 10 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 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 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.35I 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 14 Replies View Related

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 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 To Insert Missing Rows From Another Query / Table

Oct 8, 2013

I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.

Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.

As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15


What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15

View 2 Replies View Related

Too Many Rows In Variable Used For Query.

Jun 29, 2005

Hi, I am totally out of my depth on this one. I have managed to find ways to solve my other problems but this one won't seem to budge.

I have one large table ALLENGPUMP which has columns of data and other columns of lookups to associated tables. What I am trying to achieve is that by selecting a value from a combo box (which contains the results of: SELECT DISTINCT ALLENGPUMP.Coupling_Order_code FROM ALLENGPUMP; ) a list box will show all of the other columns from the table (and the associated tables).

In lstOther3 and lstOther4 the columns sought are in the same table and this will produce a result of multiple rows when the Coupling_Order_Code is apparent for more than one ID, as required.

lstOther5 contains the columns with lookups and only works when the Coupling_Order_Code returns only one ID. When more than one ID is returned it no longer functions and returns nothing. The best I have so far is shown below (this is from VBA) although lstOther5 only has one of the columns I am looking up whereas there are 5 in total for the data in 5 other tables.

So, to recap, the user chooses one of the list of Coupling_Order_Codes, this returns the ID for all rows that contains the Coupling_Order_Code. From these IDs I want a list box to show the data held in all of these rows, including the lookups to the associated tables.


Dim GvarCPLG_Ord As String

Private Sub cbCPLG_Ord_AfterUpdate()
GvarCPLG_Ord = Me.cbCPLG_Ord

Me.lstOther3.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.FW_No, ALLENGPUMP.FLANGE_MOUNTING, ALLENGPUMP.SHAFT_DETAILS, ALLENGPUMP.SHAFT_LENGTH FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))"
Me.lstOther3.Requery
Me.lstOther4.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.COUPLING_REF, ALLENGPUMP.Coupling_Order_code, ALLENGPUMP.DRAWING_No FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))"
Me.lstOther4.Requery
Me.lstOther5.RowSource = "SELECT ALLENGPUMP.ENGMOD_ID, ENGMOD.ENGINE_Model FROM ENGMOD INNER JOIN ALLENGPUMP ON ENGMOD.ID = ALLENGPUMP.ENGMOD_ID WHERE (((ALLENGPUMP.ID) = (SELECT ALLENGPUMP.ID FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """)))))"
Me.lstOther5.Requery
End Sub

Any help at all would be very, very, very much appreciated.
Thanks, Brodie

View 4 Replies View Related

Query Help :copying Rows

Jan 30, 2007

Hi ,

I need to copy rows from a table to a specific location in the same table.

I suppose that it is possible to copy rows from a table to the same table
as followed :

INSERT INTO Table
SELECT * FROM Table
WHERE Condition

How can I insert into a specific location based on another condition. ?

Thanks for the help .

View 14 Replies View Related

Getting The Count Of Various Rows In One Query.

Oct 12, 2007

I've been toying with this for a few hours but I always end up with the wrong result. Hopefully an expert here can provide some insight. I have a table called colors with two columns PNAME and COLOR. There are various products and only three possible colors. E.g:

Tablename: COLORS

PNAME COLOR
-----------------------
tshirt blue
pants blue
jacket blue
jacket green
shoes green
vest black


Now what I'm trying to do is create a single query that would provide me with the count of colors in three different columns.
E.g

Blue Green Black
-----------------------
3 2 1

I can get these results easily by creating several queries and then getting the results into one query from these, but I'm trying to learn how to put together nested queries. Any help would be greatly appreciated.

View 12 Replies View Related

Query Help: Grouping Like Rows

Mar 10, 2008

I have a table that has 2 colums.


Room_Number Items
1 Chair
2 Chair
2 Desk
3 Table
4 Computer
4 Radio
4 Book
4 Chair
6 Fish Tank
6 Chair


I want a query that will select rooms 1-6 and include all items for those rooms on one row. Example below


Room_Number Items
1 Chair
2 Chair, Desk
3 Table
4 Computer, Radio, Book, Chair
6 Fish Tank, Chair


I hope this makes sense. Thank you in advance.

View 4 Replies View Related

Merge Query Rows In A New One

Apr 15, 2008

Dear Friends,:)

I have 2 tables .. (GroupNo.) & (employee)

The conjunction between them as :

tbl1 tbl2
--------------------------
No. Name
--------------------------
1 John
1 Alian
1 Sali
2 Robert
2 ......
...
..
.
---------------------------

How Can I make a new Query that produce the result as :

1 John,Alian,Sali
2 Robert,.......
3 ......


Kind Regards,

View 7 Replies View Related

Repeating Rows In A Query?

Mar 27, 2014

I am attempting to perform a normalization process in a query where an organization would be operating in several different states and different countries. I have created three tables where: one table is Organization, second table is Country, and the third table is State. I made a table to record: Organization ID, Country ID and State ID. An organization could have 1 or more countries while having no US states. Also the organization could have 1 or more countries while having 1 or more US states.

What I am seeing is in my query is:

Organization | Country | State
Organization | Country | State
Organization | Country |

What I am wanting to see is:

Organization | Country, Country, Country | State, State

In the end, I want to make a mail merge from this query table.

How can I 'normalize' the query?

View 1 Replies View Related

Compare Several Hundred Rows Against Other Rows

Nov 21, 2005

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

View 3 Replies View Related

Query To Total Columns AND Rows

Jun 2, 2005

Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.

Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add

Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By

Field: Pending
Table: qryStatusRptA
Total: Sum

Field: Overdue
Table: queryStatusRptA
Total: Sum

Field: TotalRecords: Count(*)
Table:
Total: Expression

Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)

Many thanks,
Christine

View 2 Replies View Related

Query To Find Rows With Only 5 Characters

Aug 30, 2006

How do a write a query to find those rows in a field that have only 5 characters?

View 2 Replies View Related







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