Compare two tables and write results to third table

[Guest]

I have 3 tables Table_A, Table_B, Table_C. Each month I need to compare
tables A & B and copy the differences to table C and update 2 fields on table
C, one with current date, and a second field with a status either NEW or
REMOVED.
Each month Table_B gets cleared, I copy the records from Table_A to Table_B
and Table _A gets cleared and loaded with new data. The first query will
compare Table_A to Table_B (left join based on a key field) and any records
on Table_A that are not on Table_B are considered NEW. A copy of this record
will be written to Table_C and I need to update two fields on Table_C along
with this record, a date field (will have current date when record was added)
and a status field that will have the value "NEW" placed in it. The second
query will compare Table_B to Table_A (left join based on a key field) and
any records on Table_B that are not on Table_A are considered REMOVED. A copy
of this record will be written to Table_C and I need to update two fields on
Table_C along with this record, a date field (will have current date when
record was added) and a status field that will have the value "REMOVED"
This is like doing an Append query and an Update query in one. Is there a
simple way to do this, or do I first need to Append the records in to Table_C
then run an Update query to update the date and status of the records that
have null values in the Date and Status fields?

Thanks in advance for your help.
Kenny A.

 

[Jeff Boyce]

Kenny

You've described a "how" ... the method you've chosen to solve a business
need.

Copying records from one table to another is not the only way to solve some
business needs. If you'll describe more of the "why", and what you need to
accomplish (rather than HOW you are trying to accomplish something
{undefined}), the newsgroup readers may be able to offer alternate
approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
Each month we get a new data feed and I want to be able to report on which
are new locations for our clients and which locations have been removed from
month to month. I am using Table_C to keep a historical record of the adding
and removing of locations and when they occur, so I can create a report on
this data. I am always comparing prior month to current month. The data in
tables A & B as you can see are volatile. Back to my original question. Is
there a simple way to append the records selected from the difference of
tables A & B and placed into Table_C and also updated the two fields with
date and status?

Thanks
Kenny A.

 

[Jeff Boyce]

Kenny

Again, you've already settled on "how" you plan to do this, and I'm not
convinced that the way you picked is necessarily a good idea. I can drive
nails with a chainsaw, but it is not a good idea <g>! Your response sounds
a little like "just tell me what button to push". If your mind's made up
that your approach is the only approach, perhaps one of the other newsgroup
readers can offer a "button to push".

Please consider stepping back from how you've approached this and describe
your situation in terms a computer-illiterate 89 year old grandmother could
understand.

Here's my starting ideas:

You have clients. The clients have locations. The locations change. Each
month you get new information about where your clients are located.

You want to keep track of where clients are, and where they were.

Is that a fair paraphrasing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

In simple terms I want to compare 2 tables and determine which are new
records and which have been removed. I want to report on this data. Every
month I need to compare current data with prior data. I want to keep a
history and document when the record was REMOVED or when it became NEW. Does
anyone have a suggestion how to do this?


Kenny A.

 

[Jeff Boyce]

Best of luck on your project. I'll step back and let others offer ideas...

(I'm not sure an 89 year old understands "tables" or "records" or ...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
As I have learn in Access each entry is called a record, and they are
stored on tables. Please correct me if I am wrong. How else could I describe
to a computer-illiterate 89 year old grandmother without using words like
record and table?
I would like to compare two pieces of data (oops sorry data is a
computer term) two words and see if one is appears in one place and not the
other. If it appears in one place, I would like to write an entry to a list
of a copy of the word that appears in one place but not the other and write
the date it appeared and that it is a new word or that the word had been
removed.

Jeff, I am asking for help. You know what I am asking. How would you
pose this to a computer-illiterate 89 year old grandmother? I just want to
compare two tables and determine if a record is new or has been removed and
place a copy on another table update a date field with current date, and
update a status field with NEW or REMOVED that I may report off of.

Kenny A.

 

[Jeff Boyce]

Kenny

OK, you know the Internet... I could actually BE an 89 year old
grandmother...<g>

I was trying to offer a paraphrasing:

I have a list of where my customers are. I get a new list every (... fill
in time period...) and want to keep my permanent list up to date.

The new list can have folks who moved, it can have new folks and where they
are, and it can "not have" folks who've left no forwarding address (these
are missing, not actually on the new list).

When I'm done, I want a list of every customer I've ever had, where they are
now (or some way to know that they aren't customers currently).

If you use a "Unmatched" query (the query wizard can help with this), you
can find all the records in one list that aren't in another. Does that
offer an idea of how to approach this?

First, an unmatched query to find folks in the permanent list that are not
in the "new" list. With that query done, update all the folks in the
permanent list who aren't in the new list to show they are no longer
customers (a flag, a date-dropped, whatever).

Then, for all folks who match up between permanent and new (how are you
"matching"? by name? what happens when you have two John Smiths?<g>),
update the permanent list with the information from the new list.

Finally, anyone on the new list who doesn't show up on the permanent list
must be new. Add (append) them.

You could work the queries out for each of these steps, then use a macro to
run them all, one after the other.

Is that any closer to what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
First of all, that seemed to make it more confusing. Second, Just about
all examples I see when people write into this board, they use exact table
names and samples of code (not as if they are writing to an 89 year old
grandmother), because this board is looked at by people who are MS Access
users and understand tables and records and queries and code. Third, I have
already developed both unmatched queries with no problems. The original
question that you seemed to ignore / miss was Is there a simple way to
combine an Append query and an Update query as one. Since I need to select
records based on an unmatch query and append them into another, and then
update two fields with a date and a status. I thought this was a simple
request. Is it possible to do this in one query? I know all about macros. I
have one that runs approx 20 queries to setup a table for me (which works
perfect) and I will be adding these queries to same macro.

I am sorry that this seemed so complicated of a request. I did not mean it
to be so. I have written much more complcated help requests needing help
setting up code between Access and Excel and got it very easy. I know I can
work this problem using multiple queries, but was wondering if could kill 2
birds with one stone (one query), that's all I was looking for. I was not
looking to get into an argument, was just looking for simple help. I have
come to this board many times using table names and the like and found
everyone very helpful.

Kenny A.

 

[Jeff Boyce]

Kenny

As I suggested earlier, perhaps we're just not connecting, and someone else
might work better.

For the record, I'm not looking for an argument ... I genuinely can't
visualize your what/why, so it's hard for me to offer suggestions on "how".

When I need to both update and append, I use two queries (but that's just my
style). That way I feel I have better control over each of the steps.

Given how "deep" this thread has gone, you'd probably get more eyes/brains
working on your request if you re-post it.

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP