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