Please save me

[Sherry]

I've been using Excel to do something I know should be done in Access. I
have 2 files.

One with about 50 rows like this:
aaaaa bbbbb ccccc ddddd eeeee

And one with about 50 rows like this:
aaaaa bbbbb xxxxx yyyyy zzzzz

I've been manually merging the 2 files together to get a file like this:
aaaaa bbbbb ccccc ddddd eeeee xxxxx yyyyy zzzzz

I've been telling my boss that if he buys me a copy of Access I could do
this faster. Now he gave me his copy of Access 97 and wants me to do this
merge on file of 12,000 rows. NOW! Can someone PLEASE walk me through the
very basic steps to do this?

Merry Christmas and thank you.

 

[Rick Brandt]

I've been using Excel to do something I know should be done in Access. I
have 2 files.

One with about 50 rows like this:
aaaaa bbbbb ccccc ddddd eeeee

And one with about 50 rows like this:
aaaaa bbbbb xxxxx yyyyy zzzzz

I've been manually merging the 2 files together to get a file like this:
aaaaa bbbbb ccccc ddddd eeeee xxxxx yyyyy zzzzz

I've been telling my boss that if he buys me a copy of Access I could do
this faster. Now he gave me his copy of Access 97 and wants me to do this
merge on file of 12,000 rows. NOW! Can someone PLEASE walk me through the
very basic steps to do this?

It looks like your saying that the fields (in common) are aaaaa and bbbbb. If
that's correct you would import both sheets into tables within Access. Then you
make a query that includes both of those tables joined on the two common fields.
Pull down all of the fields from the first table and just the fields ccccc,
ddddd, eeeee, yyyyy, zzzzz from the second one. The result of that query should
be the same as your current merge. Change the query into a Make-Table query,
execute it and you're done.

 

[Sherry]

Thanks, but I don't understand how to do this. Could you elaborate?

 

[Rick Brandt]

Thanks, but I don't understand how to do this. Could you elaborate?

I assumed from this statement...

"I've been telling my boss that if he buys me a copy of Access I could do this
faster."

....that you had "some" familiarity with using Access.

Open a new Access file and then use File - Get External Data - Import. This
will open the import data wizard which you can use to import the two Excel
sheets into Access tables. Sometimes when importing Excel sheets you end up
with some of the empty columns getting imported as well. You can open the
tables in design view and delete these if that happens (or just don't include
them in the Make-Table query later on).

Then create a new SELECT query. When first opening the "New Query" you will be
shown a dialog for adding tables. Just select both of the tables produced by
step 1 above. Create join lines between the two tables in the designer by
dragging a field name from one table and dropping it on top of the matching
field in the other. This should create a line on the screen between the two
fields. You need to do this for both of the *common* fields in the two tables.

Drag down to the lower portion of the grid all of the fields you want from each
table. You only need to pull the common fields down from one of them.

At this point you should be able to switch to datasheet view and see what the
results look like. If it looks correct, then return to design view and go to
the menu and change the query type to "Make Table". You will be prompted for
the name of the new table. Then press the [!] in the toolbar to run the query.

The above will include only rows where the matching fields exist in BOTH tables.
If you have some rows that are in one table and not the other, then you will
need to do some more work. Post back if that is the case.

 

[Sherry]

Thanks for the Christmas present. This is just what I needed!!!!!!!!

Thanks, but I don't understand how to do this. Could you elaborate?

I assumed from this statement...

"I've been telling my boss that if he buys me a copy of Access I could do this
faster."

...that you had "some" familiarity with using Access.

Open a new Access file and then use File - Get External Data - Import. This
will open the import data wizard which you can use to import the two Excel
sheets into Access tables. Sometimes when importing Excel sheets you end up
with some of the empty columns getting imported as well. You can open the
tables in design view and delete these if that happens (or just don't include
them in the Make-Table query later on).

Then create a new SELECT query. When first opening the "New Query" you will be
shown a dialog for adding tables. Just select both of the tables produced by
step 1 above. Create join lines between the two tables in the designer by
dragging a field name from one table and dropping it on top of the matching
field in the other. This should create a line on the screen between the two
fields. You need to do this for both of the *common* fields in the two tables.

Drag down to the lower portion of the grid all of the fields you want from each
table. You only need to pull the common fields down from one of them.

At this point you should be able to switch to datasheet view and see what the
results look like. If it looks correct, then return to design view and go to
the menu and change the query type to "Make Table". You will be prompted for
the name of the new table. Then press the [!] in the toolbar to run the query.

The above will include only rows where the matching fields exist in BOTH tables.
If you have some rows that are in one table and not the other, then you will
need to do some more work. Post back if that is the case.

 

[Don]

You know... this sounds a lot like a lie has been told ...(ie; I can do this
better and faster) she knew she was lying about "How she could do this". Now
the lie has caught up with her and she's asking to be bailed out. Ask if she
would send some funds for doing her job.

Thanks, but I don't understand how to do this. Could you elaborate?

I assumed from this statement...

"I've been telling my boss that if he buys me a copy of Access I could do this
faster."

...that you had "some" familiarity with using Access.

Open a new Access file and then use File - Get External Data - Import. This
will open the import data wizard which you can use to import the two Excel
sheets into Access tables. Sometimes when importing Excel sheets you end up
with some of the empty columns getting imported as well. You can open the
tables in design view and delete these if that happens (or just don't include
them in the Make-Table query later on).

Then create a new SELECT query. When first opening the "New Query" you will be
shown a dialog for adding tables. Just select both of the tables produced by
step 1 above. Create join lines between the two tables in the designer by
dragging a field name from one table and dropping it on top of the matching
field in the other. This should create a line on the screen between the two
fields. You need to do this for both of the *common* fields in the two tables.

Drag down to the lower portion of the grid all of the fields you want from each
table. You only need to pull the common fields down from one of them.

At this point you should be able to switch to datasheet view and see what the
results look like. If it looks correct, then return to design view and go to
the menu and change the query type to "Make Table". You will be prompted for
the name of the new table. Then press the [!] in the toolbar to run the query.

The above will include only rows where the matching fields exist in BOTH tables.
If you have some rows that are in one table and not the other, then you will
need to do some more work. Post back if that is the case.