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 RepliesI 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.
Is there a tool that is not to expensive that can be used to compare access databases for differences.
View 2 Replies View RelatedI have recently inheritied a database and find that there are functional differences between different versions of the database. The database contains over 30 very large tables and I am unable to find the differences between the versions. I need a process or tool that I can use to compare the different versions to find the table/record/field differences. I am up against a deadline and am needing a solution ASAP.
View 1 Replies View RelatedA few years ago I upgraded a little software package that I had written to Access 97, without updating some of the old-fashioned methods and structures (eg. it still uses things like : Set workdynaset = db.Createdynaset("Client") ).
Due to problems that some potential customers have had loading the A97 runtime version of this software onto PCs with Access 2000 or Access 2000 runtimes, I have been trying to upgrade to an Access 2000 runtime version.
I have read all the various threads on DAO stuff and the need to select the appropriate references on the Tools menu.
My question is this. If I select " Microsoft DAO 3.6 Object Library ", will the VBA code compile without my having to convert all of the dynaset-type code to a more modern form ?
Situation:
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:
I need help with my table structures. Attached is a Access 97 sample of my existing tables.
Questions
1) I need a new table that shows which BankRepID’s are assigned to each ClientID. Currently the relationship between the Banks and their BankReps is normalized.
But every time I create a table to assign BankReps to Clients I have noticed it becomes possible for the user to assign BankReps to Banks where they do not work.
How do I prevent this through normalization?
2) Because one Client can have many banks and one bank can have many clients I created a many to many relationship. Is this correct?
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 everyone. Over the last couple months, this site has been a real lifesaver for me, just reading about similar problems to mine. This new problem, however, I have no idea how to search for.
I have a table in the following layout:
ID, FieldName, Value
10001, ID, 10001
10001, FIRSTNAME, Jonathan
10001, SURNAME, Reid
10001, ADDRESS, 10 Abbotsford Road
I need to be able to flip it around to be in the following layout
ID, FIRSTNAME, SURNAME, ADDRESS
10001, JONATHAN, REID, 10 Abbotsford Rd
The table is about 30 fields wide and about 450 records long, therefore I cannot do it manually.
I'd appreciate any help
I've spent most of the afternoon trying to find an answer to my question so please forgive me if I just didn't have the right keywords to find it via search.
I have several clients that use my database which is split into two parts, one of which is the data back end. At times I release upgrades to the whole package and must currently go through the process of having them get me the BE file and I manually make changes to tables and update data.
What I was hoping to do was create a seperate database that could link to the BE file, check for structure items and if they are missing add them. For instance if I have a new field I want to add to a table it will look for it, add it, and then if necessary add data. This way I could have one master file that I could send my clients and have them run it and not have to worry about the timing issues of transfering files back and forth.
Any guidance would be great. Not necessarily needing lengthy examples that would take a lot of your time to type out, simply want to know if it's possible and the basics that I can research and read up on.
Thank you in advance for your time! :)
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 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 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 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 RelatedBeen trying to crack this one for a while hoping someone on here might be able to help me. ;)
I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.
This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...
Any help would be greatly appreciated.
Graeme
I am putting together a database for work, I have a table that is used to refer to (this table is part of our production system) in this table it lists products run, dates run and who has run.
I have the training records data table, this lists; who, machine and status: "trained" "refresh required" " not trained"
I want to build a query so that it searches the production table for the last 6 months worth of records & if an operator that is in the training table (WHO) as status (trained) is not in the production table it outputs a "to be refreshed" report or amends the records from trained to "refresh required" ...
I have three tables: Table A, Table B and Table C
Fields of Table A: Truck No, Capacity
Fields of Table B: Consignee ID, Capacity A, Capacity B and
Capacity C
Fields of Table C: Consignee ID, Final Rate
Input form is based on Table C:
1-I select Truck No using a combo box and it shows the Capacity from Table A using DLookup.
2- I enter the id no. in the field of Consignee ID and it returns the values of Capacity A, Capacity B and Capacity C using DLookup from Table B.
So I have to write manually one final rate in the field of Final Rate in the form.So I want:When I enter id no. in the field of Consignee ID, it matches with the Capacity in Table A and shows the matched result only in the field of Final Rate of Table C.
Compile error: Method or data member not found
Code:
Option Compare Database
Option Explicit
Dim intItems As Integer
Dim strName() As String, datBirthDate() As Date, intAge() As Integer
Private Sub Form_Load()
intItems = Val(InputBox("How many names do you have?", "Name List"))
[Code] .....
Hi
I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below
Table 1
xyz - A1 - Do not use
ABC - A4 - Use
Table 2
123 - A1
The result of the query should show me
A4 (as this does not appear in table 2
I am not too familiar with Null values as there will be many null values that i want to ignore.
Any help will be appreciated.
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