General :: Compare Two Tables And Update Changes

May 15, 2013

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)

View Replies


ADVERTISEMENT

General :: Compare Data In A Column And Update If Required

Feb 28, 2013

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

View 1 Replies View Related

General :: Compare 2 Tables Where One Record Matches And Another Doesn't

Jun 26, 2013

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

View 2 Replies View Related

Compare And Update

Dec 28, 2004

I need to campare two tables. if there are new records from table A would like to add it to table B.
Also if there are any changes in a field of record i would like to see the change in table B.
thank you
Mark

View 1 Replies View Related

Update And Compare Table Records

Sep 1, 2005

Hi
I'm using MS Office 2000, I have an Access database table that contain many fields, namely I need to update FieldA from an Excel Spreadsheet according to the specified primary key of CustomerID. Is there a way to write automate process to import the records from an excel spreadsheet and then place those records in FieldA of the Access Database according to the corresponding CustomerID? The reason I ask is that these two fields are not exactly in the right order in my spreadsheet and my table and to do it manually will take lots of effort.

Your prompt response is greatly appreciated

Thanks

View 3 Replies View Related

Compare Value Before Running Update Query

Aug 31, 2006

Hi all
I want to view vehicles due for servicing in the next week. The queries giving me this information work fine. I have a form that I can select, from a drop down box, either 1,2,3 or 4. A report is then run and emailled to the appropriate people. After testing to see what was the easiest way to remember what the last week was (I tried setting the combo box to remember the value but it did not, unsure why, I also tried recording Mondays date in a field in a table and assigning each Monday a value 1-4 then running a query...long story short, to time consuming) I have used a table with one field to hold the value and after emailling the report, an update query adds one to this field and it is displayed above the combo box in a txt box. I want to reset the value to 0 if the value equals 4 before the update query runs. Is this the best way to do it or can someone suggest another way of doing this.
Thanks in advance
Craig

View 2 Replies View Related

Update Several Fields And Compare Strings

Aug 8, 2007

Have devloped a database to log all support issues for an application I work with.

I periodically update the database by importing from another database

for example my main table tbl_issues has fields IR_NO, DESCRIPTION and STATUS

I then import data from tbl_import which also has these fields. The following query works fine:

UPDATE tbl_support
SET tbl_support.STATUS = tbl_import.STATUS
WHERE tbl_support.STATUS <> tbl_import.STATUS
AND tbl_support.IR_NO = tbl_import.IR_NO

However I also want to do this at the same time:

UPDATE tbl_support
SET tbl_support.DESCRIPTION = tbl_import.DESCRIPTION
WHERE tbl_support.DESCRIPTION <> tbl_import.DESCRIPTION
AND tbl_support.IR_NO = tbl_import.IR_NO

How do I update both fields at the same time?

View 2 Replies View Related

Queries :: Compare A Field In Another Table And Update Accordingly

Jun 25, 2015

I am programming an Acess Database, the primary data come from an excel sheet. Since Access one to many relationship only works for primary key and another field in another table as foreign key, I can't have normalized database.

I will explain what I mean with a hypothetical situation.

So, let's say I have two tables: WindowT1, CustomerT2.

WindowT1 has fields: WinID (the pk), WindowName.
CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.

So, in table "CustomerT2', WinID is the foreign key and I need that field filled in to make one-to-many relationship between these two tables to work. CustomerName and WindowName will be imported and prefilled already from an excel file with append query.

My question is can I make a update query (after the initial append query that brings in the data into Access) such that the criteria is matching WindowName (from CustomerT2) and the update would be the corresponding WinID (the pk number from WindowT1) that would be filled in the field WinID (in CustomerT2)? I know for this to work tables must have relationship and I did that.

Is there a better way to accomplish this task? i.e. through a form (remember info will be prefilled, so can't use combo box to fill in another form field) or maybe can I do this using the first append query (that brings in data)?

I am not good at Macro or VBA. The only thing I have done in code builder is After Update event in forms while using combo boxes. So, if there is no easier solution, I will try to deal with codes.

View 11 Replies View Related

Tables :: Compare Field Data In Two Tables

Jul 10, 2015

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

View 14 Replies View Related

General :: Add And Update Multiple Records To Different Tables

Jan 12, 2013

I have a pretty normalized Access Database. The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.

When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.

If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.

Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.

For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.

Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.

By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.

View 3 Replies View Related

General :: How To Compare Strings

Aug 23, 2013

How to compare 2 strings in Access in order to determine one is biggest or highest than the other??

View 2 Replies View Related

Compare 2 Tables

May 31, 2007

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:

View 2 Replies View Related

General :: How To Compare Sum Of A Few Numbers From A Set Against A Given Number

Nov 1, 2012

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

View 12 Replies View Related

Compare Tables From Diff Db's

May 5, 2005

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

View 1 Replies View Related

Compare Tables In Access

Sep 27, 2007

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.

View 14 Replies View Related

Compare Contents Of Two Tables

Oct 5, 2007

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 Related

What To Use To Exclusively Compare Tables

Apr 6, 2006

I 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

View 1 Replies View Related

How To Compare The Elements Of Two Tables

Aug 25, 2004

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

View 2 Replies View Related

Compare 2 Tables Where NOT EQUAL &lt;&gt;

Feb 7, 2006

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?

View 2 Replies View Related

Using Access To Compare Tables

Apr 3, 2014

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.

View 1 Replies View Related

Partial Text Compare Across Two Tables

Jul 20, 2007

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?

View 5 Replies View Related

Tables :: Compare 2 Database Structures?

Mar 13, 2013

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 Related

Compare Two Tables And Show It In A Form

Jan 25, 2012

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?

View 1 Replies View Related

How To Compare Data Type And Properties In 2 Tables

Mar 20, 2005

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

Compare Data In Two Tables, With Multiple Conditions…..

Aug 25, 2005

Been 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

View 2 Replies View Related

Queries :: How To Query Two Tables And Compare Results

Apr 18, 2013

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

View 1 Replies View Related







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