I have table A with a list of Work Instruction references (WIREF) and a Issue number (ISSNO).I have Table B wth employees and they too have a Work Instruction (empWIREF) and Issue number (empISSNO).I want to check if the Work instruction Issue has changed since the employee was trained
check:
WIREF = empWIREF and ISSNO <> empISSNO for all records in table B
basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"
Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.
room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.
here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then
MsgBox "This number already exists."
Else
p.s it unbound form
i import data into a Landing table, this will always contain d days woth of data, i then run an apend query into another table were the records are stored up to 20 days, the problem i face is some of the record data may change for eample a field called ShippedQty may be 0 in a monday but on tuesday it may say 5
Import on monday
Product ShippedQty Date
123____ 0 ________01/01/2013
Import on Tuesday
Product ShippedQty Date
123____ 1 ________01/01/2013
I need to look at any changes and updatein the master table, is there a way to do this.(I cannot change the import as its the only way i can get the data)
I am trying to find company matches between 2 tables. The issue I have is that the spellings on table A differs from table B.Some of the differences are minimal like "St. Annes" and "St Annes". And some really big "St. Annes" and "Annes, ST London".
View 1 Replies View RelatedI'm trying to build a search form.- Access 2007
I have a table (Table1), contains a 'part' field.
I wan to build a form with a blank text box (txtbx1) where users can enter a part description, this will search records in Table1>part and return a list of parts that match the textbox input.
Here's what I've done -
txtbx1 - property sheet - control source - part
event - on enter - ...
I've built an expression -
[Table1]![part] = [txtbx1]
hoping that this would match records 1n Table1 to txtbx1
I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted. I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:
Code:
Private Sub Accounting_Unit_Enter()
'find where AUs match. only works for first Subform2 record
If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then
msgbox "Match"
End If
End Sub
My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1,
How do you compare one record to the previous record? I have a query that shows items sold. It is sorted by day with today on top. I would like to create a column that has a 'thumb up' or 'thumb down" if todays was a better day than yesterday.
View 1 Replies View RelatedI inherited a database that has two tables that are structured identical; one called tblcurrentdata and the other called tblpriordata. The user wants a report or export query that only shows records that has differences between prior weeks data and the current weeks data. The tables have 12 fields of data in a record per quote number. The user wants a query or report to identify the QuoteNum and any changes between the prior and current records. The table structures are as follows:
Code:
tblCurrentData/tblPriorData:
QuoteNum
CoName
State
ZipcodeNo
priorityColor
[code]...
QuoteNum 12345 field 5 (prioritycolor) changed from red in the prior week to green in the current week and field 7(POC) changed from Scott in the prior week to Jonson in the current week.QuoteNum 23451 did not have any changes therefore does not need to be listed in the query/report Unmatched query doesnt work because it does not compare multiple fields. I tried to structure a union query and use <> in each field but got too tedious and didnt give the expected results.
How to compare 2 strings in Access in order to determine one is biggest or highest than the other??
View 2 Replies View RelatedSituation:
Have 2 Tables that contain the same information, one table was used during registration and had the Data changed
Question:
How do I pull a comparison query to match the registration table to the Main table and pull only the data that is different so I can update the main table with the new Information? :confused:
My database tracks employee training.
I have tblClasses, tblEmployees, and tblRegistrations. I have a registration
form with EmpName and subformClasses, which allows me to register one
employee for several classes at a time.
My registration form has an OK button. I need a message to appear (on btnOK click) before adding a new record to the registrations table that indicates if the employee has already had training for the one or more of the classes selected in the subform. Then the option to "Yes" add the record anyway, or "No" cancel the record.
I have everything working except, I can’t figure out how to do the
comparison with the subform and generate my "Yes/No" option. I’m hoping that one of you wonderful access geniuses will have a relatively simple suggestion for me.
--
Thanks in advance!
Shel
I have a set of numbers, say (these could also be values of a column in a table)
FieldA = { 11, 16, 20, 23, 30, 37, 40, 50 }
I have a number, say 196.
I want {16,23,30,37,40,50} to be returned as these numbers add up to 196.
Note :
1) There is no possibility of two solutions with the kind of numbers that I may be using.
2) A solution using excel is also OK, though, personally I would prefer access.
Edit :
3) {16,23,30,37,40,50} - Each value is a separate record i.e. they are not in a single field, rather :
16
23
30
37
40
50
hello, i have checked out the forum for pevious Q's to this problem and found the below.
http://www.access-programmers.co.uk/forums/showthread.php?t=80692&highlight=compare+tables
only problem is thats its writen at a bit too technicle a level from where i am. i've done some investigation into the problem though.
found out i need to use the docmd.transferdatabase function to import the tables i need to compare into my current db.
my question is this. how does it import them, does it create new tables within the current db or is it put somewhere in temp memory for use only while db is open, not sure how it would work. ideally i would like it to import tables, do the table comparison then create a report of the differences or make a new table showing the differences, then it reverts back to how it was before i imported it. i.e. he current databse is unchanged except for maybe a new report or new table (the results).
thank you for any advice.
Neil
Hi,
I need to compare 2 tables with Same Structure in a Database and pull out records that are not matching in both the tables. As anyone done similar task in Access 2002, if so please let me know how this can be accomplised.
I have two tables that have the exact same structure. They both started with the same data, but each were changed separately. (Two copies of the same DB, editted separately). I need to review the data in each record and determine what has changed or if records have been added. Is there an easy way for me to do this? (Note: Each record does have a primary key)
View 1 Replies View RelatedI have company names in 2 tables to compare, but the data is slightly different but same companies. I want to know what companies are in one table but not in another. Here's an example:
Company Name in Table 1 = Virtual Micro
Company Name in Table 2 = Virtual Micro Technologies
Same company, but the spreadsheet I imported for Table 2 had the full name for the company, so therefore they are not compared the same and the query result shows them to be different.
Do they have to be exactly the same, or can I compare a certain number of characters or somehow use the "like" criteria filter?
Jen
Hello to all. The problem that I am faced with is that I have a master list with all the current users and an updated list with all the current users and new users. What I have been asked to do is to compare the information in these two tables and have been given the following steps.
1. If name (surname, first name) exists in both lists do nothing.
2. If name exists in Update list but not in Master list then insert a blank row in the Master list and add the following Update list fields to this row: ¨
3. If name does not exist in the Update list then remove the entire row that contains that name from the Master list.
4. If 2. condition ("name exists in Update list but not in Master list") below applies, then insert the blank row with some kind of marker (e.g. "*") in a column on the far left. This will allow us to distinguish between:
So that is what I have to do can someone please tell me what a quick to do this in access would be. Thanks
It need to compare two tables where the ID Number is not equal. Specifically I need to update new poducts in a table, but only those ID#s that are not already listed.
I know how to compare two feilds where they are not equal...
i.e. [New_Table].[ITEM_NUM]<>[Old_Table].[ITEM_NUM]
...but I'm currently limited by the join properties in the tables, since I'm comparing IDs and not the feilds. ANY IDEAS?
I work in retail and am trying to compare items that are on auto-replenishment for a particular location to items that the location should have in their retail assortment to make sure that all of their items are on auto-replenishment. I need to also perform the comparison backwards to find out if any items are on auto-replenishment that should not be because they are not carried in the location's retail assortment.
I have attached a file below that shows the files I am working with. The first tab "items on replen" show the item # (in text and general formats) along with the location number, the min and the max. The 2nd tab "POG's by Center" show the retail planograms assigned to each center. The location# is at the top of the file and the planogram numbers fall below each location #. The 3rd tab shows the items on each planogram. The first thing I need to do is convert the planogram # into items on each planogram in each center. This is what I would need to use to compare to the items on replen.
Hi,
I'm trying to compare partial text records across two tables.
The first table simply has a description, such as:
DescriptionHammer
Bone Saw
Power Saw
The second table is a list of terms and a category, such as:
Term, Category
Hammer, 1
Saw, 2
I'm looking to build a query will compare the two and assign each description a category based on the partial match. It seems like this should be fairly easy to do, but I'm struggling to find anything to point down that path.
Any ideas?
I have an access application that I've developed and given to people and now i'd like to update it. I have a "master" database that I use for development and I would like to compare the users databases to this one and apply any changes that I've made to the database schema without having to remember all the new fields, tables and everything else I've added.
View 1 Replies View RelatedI have these tables:
Table1:
ID, Date, Code
Table2:
ID, Date, Code
Now I need to compare these two tables based on ID and Date and show matching records, the ones extra in Table1 and the ones Extra in Table2. How can I do this?
I have a table "Customer Plans" with 100s of rows of data with 3 columns
'Name', 'Company', 'Plan Type'
I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:
If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Customer Plans.Name' column
e.g.
if
Name = Anna B
Company = Corp&Gov
then
Customer = Anna B
I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.
If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'
if
Name = Anna B
Company = Dealer
then
Customer = Dealer
I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?
View 2 Replies View RelatedI would like to compare the values of several columns in the same record and remove any duplicates. So...
ID | Col1 | Col2 | Col3 | Col4 | Col5
-------------------------------------------
1 | A | B | C | D | C
2 | C | C | C | D | D
3 | A | A | B | A | D
In the above example I would remove one of the Cs in row 1.
Two Cs and one D in row 2 and two As in row 3.
At the moment I have the value of each column stored in a variable and each one is compared against each other using LOTS of If statements.
I would like to compare 2 tables by looking only at the names of the fields, data types and their properties (e.g. required yes/no). I am searching for a method to export this information to another table and then make comparison. Maybe sth. similar to “documenter” or another tool. Some ideas would very much appreciated. Thanks Bartek.
View 1 Replies View Related