What command am I describing? Merging? Checking for duplicates? (MS Access Q)

Factual Questions
1

I have a slight problem. I’m trying to automate a process where I download a list early on, and then compare it to shortened lists later on in the month. For example, an auction comes up with 1,000 properties at the beginning of the calendar year. After the first month, the list is reduced (by the auctioning authority) to 995. After the second month, it’s 889; the third is 750, and so forth. Finally, for the sake of argument, by auction time, the list is down to like, 230.

The problem I’m running into is that while I’m “scrubbing” the list of the original 1,000, I’m inserting comments of what I like/don’t like, and in a table putting my personal vote to bid/not to bid. What I’d like to do is carry those comments into subsequent iterations of the lists–into what the authority is still offering for auction, that way I don’t have to start from scratch every time the authority culls their lists.

I want to apply the new auction list to the old one that I’ve worked on, and apply my comments from the old to the new list.

For example:



Orig List      New Auction List
(From Day 1)       (Day 30)
A (my comments)        A                         A (my comments)
B (my comments)
C (my comments)        C                         C  (my comments)
D (my comments)   +                        = 
E (my comments)
F (my comments)        F                         F   (my comments)
G (my comments)
H (my comments)       H                         H  (my comments)
I (my comments)        I                          I  (my comments)

I’m positive the “Help” documentation covers this sort of thing . . . but for the life of me, I can’t think of a term for what I’m trying to do. I don’t think it’s a “merge” function. Any ideas? Or, anyone try this before?

Note: The mechanics of which are not a problem. A Doper previously, and kindly, wrote a script which allows me to download an HTML format into delimited text, which I can then import into Excel or Access. Gathering the data isn’t the thing, it’s the massaging or working with it, where I’m running into trouble. Any thoughts or ideas are warmly welcomed.

Tripler
Automation enthusiast.

2

This looks to me like a nearly-textbook ‘inner join’ operation - you’re crossreferencing the full list in table A, (including your comments,) with the abbreviated list in table B that has only certain keys, to create a result set listing the records from table B, along with your comments from table A.

The access query designer should have no issues with this - you could table B to include the comments, make a new table for the results, or just look at them in the query results pane.

Hope that this helps.

3

Hi Tripler. I think this is fairly simple, but I’m not entirely clear on how you’ve put together your db and what you’re wanting to do.

It looks like you have a table for the original list, right? So you’ve got column(s) listing info about the property, plus column(s) for your comments and bid vote.

Then you have another table for the new list with fewer entries. Again, you’ve got column(s) with property info and column(s) with your notes.

If that’s the case, you just need to use the query designer. This is a pretty basic kind of query.

Add both tables and join the tables on a common column (an ID if there is one in the original data, any column that is both unique and unchanging if there’s no ID).

To join the tables just select your join column in one table and drag it to the join column on the other table. That will create an “inner join” that only allows rows that exist in both tables to be selected for output.

The doubleclick the fields in each table that you want in your report and run - voila.

It’s a little more complicated if you want to combine the comments from multiple tables into one column, but not bad.

Does that help?
ETA: or what he said. :slight_smile:

4

Wow! I think that is exactly what I was looking for! Y’all have saved me innumerable amounts of hours trying to cut and paste. I’ve played around with it, and I can export the query results to where my wife can edit them, and I can reimport them to Access (she likes Excel, and frankly, it’s easier to post on Google docs). But what you guys revealed to me is much better. I knew there was a term for it. . . I just couldn’t think of what it was.

Thanks again!

Tripler
A now really enthused automatist.