Querry

[Eva]

One tricky question
I have a two tables - one is a source data and the second one is a new
data.
The two have same column headings.
The new data table can have new records or same records same as source data,
but with some changes

For example

Record Table Heading Data
1 Source Department Marketing
1 New Department Finance

I need to update the source data with the new records and update all the
information that has discrepancies in new data

How to do it?

 

[KARL DEWEY]

I need to update the source data with the new records and update all the
information that has discrepancies in new data

If new data has discrepancies how do you know that as it is new and
therefore should be the latest and greatest?

If new data has discrepancies how do you know which old records to update?

Post your actual table structure - all your field names and datatype.

 

[Eva]

I can show you one example
Some of the data in source can change. The source table is the base one. A
new information or the one that should be changed is in a new table
Record # is my unique code, so querry must find the record and update the
information for the new data
I need to update it in source data automatically - the first record for
department name, the second for HC Total and add a new record #4
Source data
Record Deparment HC Total
1 Marketing 2
2 Finance 3
3 Marketing 4

New Data
Record Deparment HC Total
1 Finance 2
2 Finance 5
4 HR 10

 

[Beetle]

Your first problem is the design of your tables. You should not have two
different tables that contain basically the same data. New records would
normally be added to the same table that contains the previous records, not
to a separate table.

 

[Eva]

I don't have any other option, I keep my source data and I receive reports
from other departments and I need to update the information automatically,
I already have 1000 records...

 

[Jeff Boyce]

Eva

Already answered in another newsgroup.

By posting separate questions to separate newsgroups, you have to look in
multiple places to see if you were answered.

And the folks who volunteer their time here in the groups end up duplicating
each others' efforts.

It's rarely necessary to post in more than one 'group.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

What determines that you need a new #4 record?
How is the content of the new #4 record determined?
Record, Deparment, and HC Total are your only fields?

 

[Eva]

Sorry I did it by mistake

Eva

Already answered in another newsgroup.

By posting separate questions to separate newsgroups, you have to look in
multiple places to see if you were answered.

And the folks who volunteer their time here in the groups end up duplicating
each others' efforts.

It's rarely necessary to post in more than one 'group.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eva]

I keep one source data and I receive reports from different departments with
the information that should be changed or with the new information.
I can show you one example
Some of the data in source can change. The source table is the base one. A
new information or the one that should be changed is in a new table
Record # is my unique code, so querry must find the record and update the
information for the new data
I need to update it in source data automatically -
For example see below:
I need to change the first record for department name, the second for HC
Total and add a new record #4
Source data
Record Deparment HC Total
1 Marketing 2
2 Finance 3
3 Marketing 4New Data
Record Deparment HC Total
1 Finance 2
2 Finance 5
4 HR 10

 

[Jeff Boyce]

Eva

I may be misinterpreting your description. It still sounds like you are
describing "how" you are doing something (keeping one type of data in one
table and another type of data in another table).

Let me see if I can paraphrase (accurately) "what" you want to be able to
accomplish, without describing "how" to do it (I'm assuming you are posting
in these 'groups because you are seeking ideas about "how"...):

You have a set of data for which you receive periodic updates (including
totally "new" records). You want to have a way to keep your data set
current.

Is that a fair representation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eva]

yes, it is fair representation. Is there any way in Access that I can keep my
source updated any time when I receive a new source of data?

 

[John W. Vinson]

I can show you one example
Some of the data in source can change. The source table is the base one. A
new information or the one that should be changed is in a new table
Record # is my unique code, so querry must find the record and update the
information for the new data
I need to update it in source data automatically - the first record for
department name, the second for HC Total and add a new record #4
Source data
Record Deparment HC Total
1 Marketing 2
2 Finance 3
3 Marketing 4

New Data
Record Deparment HC Total
1 Finance 2
2 Finance 5
4 HR 10

If you always want the data in the "new data" table to override the data in
the "data" table if there is a match, and you want it added to the "data"
table if there is no match, then create an Update query on a left join:

UPDATE [New Data] LEFT JOIN [Data]
ON [New Data].[Record] = [Data].[Record]
SET [Data].[Record] = [New Data].[Record],
[Data].[Deparment] = [New Data].[Deparment],
[Data].[[HC Total] = [New Data].[HC Total]
WHERE [Data].[Deparment] <> [New Data].[Deparment]
OR [Data].[HC Total] <> [New Data].[HC Total];

Just a couple of nitpicks - the word is Department, not Deparment, and if HC
Total is a calculated sum of some other field in some other table, it probably
should not be stored at all but rather calculated on the fly.

John W. Vinson [MVP]

 

[Jeff Boyce]

Eva

OK, I think I have a better handle on what you have and what you want to
accomplish...

First, what form does the "new source of data" take? Is this an Excel
spreadsheet, a comma-separated text file, a Word document, an Access .mdb
file, or ...?

For some of these, it is possible to "link" to data outside of your Access
..mdb file and use it without ever importing a copy. One advantage to this
approach is that if the "new" data is periodically replaced/refreshed, your
"link" sees the new data without further effort.

Were this my task, I would decide whether I could create a link or needed to
import a "temporary" table. Then I'd create queries that 1) updated
existing records from that link/temp, and 2) appended "new" records (by
definition, those that don't already exist in my "permanent" table.

In order for this to work, there will need to be a way to compare the
existing data with the "new" data and match up. Otherwise, how will Access
know which record to update, and which records to add?

What ID field(s) are in both your current data and your new data?

Regards

Jeff Boyce
Microsoft Office/Access MVP