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 Replies


ADVERTISEMENT

Queries :: Select Records By Comparing Time Stamps In 1 Field

Jun 26, 2014

I can not solve this problem, my Access knoledge is mediocre.

I have the following table from imported data:

RecNr IPaddres DateandTime
1 178.00.000.01 20-11-2013 21:47:21
2 178.00.000.01 20-11-2013 21:47:59
3 178.00.000.01 20-11-2013 21:48:35
4 178.00.000.01 22-11-2013 20:44:59
5 178.00.000.02 22-12-2013 19:47:59
6 178.00.000.02 22-12-2013 20:47:59
7 178.00.000.03 01-02-2014 12:47:59
8 178.00.000.03 01-02-2014 12:48:30
9 178.00.000.03 01-03-2014 11:47:59
etc

I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.

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

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

Comparing Records In A Table

Jun 2, 2006

I have been searching on here and have not found an answer, could be due to me not seeing it or its not here. I have a multi part question.

Anyways, here is what I have. I am getting data from a time clock, which dumps the data into an access database. It dumps the data as EmployeeNumber (Number), DateTime(text). I use the format command and make a new table to give me EmployeeNumber(Number), Date(text), Time(text).

Question 1:How can I convert these to date and time fields instead of text fields?

After I do that I need to be able to compare the records within the table, such as: I need to check to see if the date is the same between one record and the one above it (assume that I have sorted this correctly) if the date is the same then I need to subtract the first record from the second record to give me the number of hours between the two records.

Question 2: How would I go about comparing those records in the same table?

Thanks in advance to those who can help. Even ways not to do this would be good.

View 5 Replies View Related

Noob Need Help, Comparing 2 Records From Same Field

Aug 12, 2005

Hello everyone,

I'm quite new to access (used to php mostly) and for usual task it's ok but now I have trouble for a quite simple query I think.

I have to compare the values from 1 field and select only the rows just before the value becomes smaller and also the very last row.

example:
if I have a row with 1, 2, 3, 4, 1, 2, 3, 4, 5, 1
I need to put 4 and 5 in an other table

View 1 Replies View Related

Verifying Records By Comparing With Multiple Standards

Jul 2, 2007

Okay that Title sounds confusing, but so is this task. Let me explain what I'm trying to do:

I work for a property management company and I'm trying to build a database that will allow me to catalog the inventory of furniture in several (thousand) rooms in several different buildings.

There are several types of rooms on campus all with different requirements. I want to eventually create a "standard" inventory list for every TYPE of room and then compare the actual inventory of each room to the standard inventory of each TYPE of room to make sure it is correct. The problem is that every room has several possible acceptable standards.

I was thinking that for each TYPE of room I could have several possible standards and then run a query that returns all rooms that do not match any of their standards. Does this sound doable? Any suggestions for how I might run that? Am I in way over my head? haha

View 2 Replies View Related

Comparing 2 Tables And Listing Records That Dont Match

Jun 19, 2006

Very new to access, I need an easy way to compare 2 tables with a common field (the name field) and list just the records that appear in the second table but not the first (primary) table.

Please help, thanks.

View 3 Replies View Related

Comparing 2 Tables Or Queries

Nov 8, 2004

I have 2 tables. Quotation Details and Order Details. As you fill in the Order details Form I wish to compare fields from the
Quotation Details Table. Is this possible? Please advise.
Thank you.

View 1 Replies View Related

Queries :: Comparing Two Tables Across Months

Sep 16, 2013

As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.

I would like to compare two similar tables for different months. I am looking for:

+ loan id's that are new in table(t), i.e. not exist in table(t-1)
+ loan id's that exited: that is exist in table(t-1) and not in table(t)
+ loan id's that stayed in both

How do I solve this in the easiest way?

View 4 Replies View Related

Queries :: Comparing Data From One Field To Several Others

Aug 27, 2013

I have one table A that has a Name field that I would like to find if it matches any of the cases in table B with 2 other fields: Field 1 or Field 2

There is no way of establishing referential integrity because the Name Field can be found in either Field 1 or Field 2.

How would I go about searching so I can pull in the rest of the data that I need with when the Name is matched either with Field 1 or 2?

View 2 Replies View Related

Queries :: Equation For Comparing Three Quantities

Aug 6, 2014

I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.

I am trying to build a an equation in a query field, which compares each quantity:

Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].

If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?

View 5 Replies View Related

Queries :: Comparing Two Lists Based On Criteria

Nov 23, 2013

I have run into some problems by comparing two lists. My project is to compare two lists and on the below listed criteria's and with the wanted results.

Criteria's:
1. Part
2. Colour
3. pcs.

Wanted results:
1. Equal match (same part, Colour and pcs (pcs difference above and equal 0)
2. Equal match (same part, colour but missing pcs. (pcs difference below 0)
3. Missing parts (difference in org. list - 1 and 2 query)

But already in the first part I run into problems. I do a query as below. (picture attached)

SQL code:
SELECT Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal, [Parts_All]![Antal]-[Set 7897-1]![Antal] AS Part_Diff
FROM Parts_All RIGHT JOIN [Set 7897-1] ON Parts_All.[Lego part] = [Set 7897-1].[Lego part]
GROUP BY Parts_All.[Lego part], Parts_All.farve, Parts_All.Antal, [Set 7897-1].[Lego part], [Set 7897-1].farve, [Set 7897-1].Antal
HAVING (((Parts_All.[Lego part]) Like [All_parts]![Lego part]) AND ((Parts_All.farve) Like [Set 7897-1]![farve]) AND (([Parts_All]![Antal]-[Set 7897-1]![Antal])>=0));

This query is returning 2 of the same lines with part and colour but with different pcs. Why.?

View 7 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 :: Data Comparing And Updating Incorrectly

Apr 17, 2013

I have 2 tables one Access, one FoxPro both containing similar information. The idea of my query is to compare both tables and update the Access table if the value in the FoxPro table has changed. This works fine for a number of similar queries that I have. However, intermittently, this query will see two values as different when they are exactly the same and replace the value in the Access table with a seemingly random value. I know the "Random" values are from other records within the table but cannot see any link between the values.

View 2 Replies View Related

Queries :: Comparing Data From Current To Previous Years

Oct 9, 2013

I have a table which is used to record electric billing period dates, total kWh and costs for a list of locations each month. I would like to compare the quarter total kWh and costs for this year "2013" against same quarter last year "2012". I have created two queries, the 1st is capturing data from 2013 and the 2nd is capturing data from 2012. I created a 3rd query which joins the 1st and 2nd together by month where fields match. Both tables have the same fields, however there is more records in 2012 than 2013. How can I join the tables together so months that have no data are left blank?

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

Queries :: Comparing Data Differences Between Two Identical Access Tables?

Dec 10, 2013

I have two identical Access tables, for example tblDataYesterday and tblDataToday. The data from tblDataToday has updated data changes in it that are different from tblDataYesterday. I have a maximum of 100 fields to review. I want to compare the two tables and display in a query the differences between the data from both tables.

View 1 Replies View Related

Queries :: Comparing A Text Field In A Table To Textbox On A Form

Jul 8, 2013

I have a table that has a date field set to text. I use the Mid function and get just the month in a query. I have a textbox on a form that when the user adds a month or removes a month from a selected listbox it adds or removes that months number from the textbox.

I want to us that text box on the form as the criteria for the month (the Mid function getting the month) and I can seem to get it to read what's in the textbox and use it as the criteria. I can manually type in a number and it works fine.

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

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







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