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 Replies
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
Jan 29, 2007
Hey Guys,
Just wondering how I would go about comparing two strings, and spitting the results out a similarity percentage?
E.G. (String 1) Postal Address: "11 John St"
(String 2) Street Address: "11 John Street"
(Output): 80%?
Cheers
View 5 Replies
View Related
Apr 10, 2007
Hi
what i want is, if i update one database to new database then
i want to compare tables in these two databases if any table is not matched it should be retrieved,so please tel me the solution for this.
View 3 Replies
View Related
Apr 11, 2007
Hi
how to compare one table of one msaccess database to another table in another msacess database. please tel me the steps to do this.
thanks
sri
View 1 Replies
View Related
Dec 30, 2007
Hello,
I am a Beginner in MS Access and SQL and I need a query to compare two fields.
I am having two tables; T1 and T2, and I want to compare their
fields TF1 and TF2, and display the non matching fields in new
field.
Example:
Table T1
TF1
Location Louisville
Number 555345
Name Smith
Table T2
TF1
Location Louisville
Number 553450
Name Smythe
Thanks in Advance.:)
View 1 Replies
View Related
Jan 31, 2005
Hi all.
I have a Quotation (Table), Quotation Details (Table), with One to many Relationship. Quotation Details(Table)
selects info from Products(Table). Then I have an Order (Table) connected with Quotation (Table) and Order Details
(Table) for ordering products.
What I want to do is to compare, when i make an order, the Quotation Details with the Order Details.
For example in a a quotation the customer is quoted with 4 piesces of modem. Then the next day he comes back with the
order but now he wants 3 modems. I would like to display a warning to the user or smt like that, inform him
that the quotation details are different from order details.
Is it possible??
Thank you in advance.
View 1 Replies
View Related
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
Dec 31, 2007
I have a database where I need to take the sum of all income and categorize it to find the commission rate according to what the sum is. For example, if the sum is between $0 and $7,500, the level is "A" and commission rate is 5%. If it's between $7500.01 and $15000, the level is "B" and commission rate is 4.5% and so on. I have this working with a switch() statement, and it finds the correct level exactly as I want it to.
Here's where I'm having a problem: if the sum of all income is $9,000, $7500 of that needs to be level "A" at 5% and the other $1500 needs to be "B" at 4.5%. How do I get that to split up?
Any suggestions would be appreciated.
Thanks.
View 1 Replies
View Related
Dec 24, 2007
Hi Guys,
I have made a database for a mobile phone retail store. The purpose of the database is to sort out network (e.g. Orange, AT&T) payments and compare them to our records. Our manager will enter in details into one table, including the different commisions that should be paid, plus the phone number. The other table is an imported spreadsheet that the commisions from the networks are shown. For each table, I have totalled the commisions.
My next step is to compare the two tables against the phone number, and commision totals, so that deals that have not been paid, or underpaid are highlighted. I know this involves a couple of loops, but my knowledge of Access does not go that far.
I have been thinking that I need to write code that gets one number in the first table and compare it against ALL records in the second table until it gets to the one with the same number, compare the values of commision total, and move on to the next record.
Can anyone help?
Cheers!
View 5 Replies
View Related
Aug 22, 2011
I need to create a query that will compare both tables and just send out the records that dont match to a report.
Table1 asset#,Serial#
Table2 asset#,Serial#
query that checks that in Table1 the asset OR the Serial match the what is in Table2. and if it does not find a match to send it to a report.
View 11 Replies
View Related
Sep 17, 2005
I have two tables named datadistint and links
and i need to insert only some values in five fields of links from datadistint but before inputting values we need to ensure they are already not there in links.
Insert Into links (LinkTitle,LinkURL,LinkDescription,maincat,cat)
Select 1,3,2,4,5 From datadistint Where
1 Not in (Select LinkTitle From links) and 3 Not in (Select LinkURL From links)
and 2 Not in (Select LinkDescription From links)
and 4 Not in (Select maincat From links)
and 5 Not in (Select cat From links)
i ran the querry as above but it doesn't work what should i do or what's wrong with this querry,
because i need to compare all five fields and then insert only new values.
View 1 Replies
View Related
Feb 14, 2007
As part of my job I am constantly comparing files from a production environment against those from a UAT environment. The checks are generally the generic before I reconcile the monetary values I check that the static data population is the same. Basically I import the files into access and then do a like for like comparison on the number of blanks per field. Eg.
Field Blanks_UAT Blanks_Pro Diff
Coustomer_Number 10 2 8
Is there any way of doing this programmable so I can reuse it or are there any tools out there that do this work for me. Some of the files contain a large number of fields so the work can become rather cumbersome!
Any help is appreciated
View 3 Replies
View Related
Oct 17, 2006
Hi, I have 2 tables with similar data. However I need to compare this particular field called CompanyName in both tables. Reason being human error/exposition data errors. For example, Chef Kitchen Holdings Limited, in one table it is Chef Kitchen Holdings Ltd while the other table is Chef Kitchen Holdings Pte Limited, they both are the same but Access recognise them as 2 distinct datas.
Ｔｈｅｒｅｆｏｒｅ Ｉ need help in comparing datas between these 2 tables for this particular field. So long as there is 75% similarities, one of the 2 tables will have the data replaced.
Is that possible using Access? if not, how about excel? please suggest.
thanks.
View 4 Replies
View Related
Feb 28, 2007
Hi guys, i'll try and explain this as best i can....
I have 2 tables:
CurrentMonth
PreviousMonth
Both tables hold financial information by policy number. What i am doing is working out financial movement on a month on month basis. For example, if in previous month the financial position was £100 but in current month the finances had moved to £150 then the movement is £50 for the month. This i can do no problem by linking the tables by policy number and extracting the financial position and subtracting one from the other to give me a movement. However, in some instances there will be some policies that appeared in PreviousMonth but have since been cancelled back to policy inception so is not appearing in CurrentMonth.
Problem: I need to be able to bring in the policies on the previous month that are not appearing on the current month (due to cancellation).
Can anyone help me?
Cheers
View 1 Replies
View Related
Jun 14, 2007
I would really appreciate if someone could help me with the following.
I have one list (3 million records)
Table = DNC
Field 1 - Area code
Field 2 - Phone number
I have another list that has say 1000 records
Table = Phone list
field 1 - area code
field 2 - phone number
What type of relationship do I need to setup in order for me to see which records in the Phone list table are not in the DNC table?
thanks for any help
Josh
View 1 Replies
View Related
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
