Creating one table

  • Thread starter Thread starter Teri Cyr
  • Start date Start date
T

Teri Cyr

I have downloaded a template from the Microsoft site and hope to be able to
modify it to suit my needs. It already has a contacts table in it and I have
already imported an Excel spreadsheet into it with the contacts I want to add
with all their information. Is there a way that I can just copy and paste
the information from the table I imported into the existing contacts table so
that I don't have to try to change the relationships (I am not good with
those)?

Thanks in advance for any help anyone might be able to give me!

Thanks,

Teri.
 
Teri -

You can do this with either copy/paste or an append query.

For the copy/paste to work, you need to check the imported table against the
template table and align the fields. If they are in a different order,
simply drag the columns around in your import table until they match the
template table. If you don't have all the fields, you will want to add them
to your import table, even though they will be blank. If all the data types
are compatible and the fields are in the same order, then you can select the
whole imported table (like you do in Excel) and copy, then go to the template
table and select the entire insert row (the one with the asterisk) - make
sure you have the row selected, and not just the first field. Then paste.

To use an append query to do this, open a new query and base it on the
import table. Set this as an Append Query, and append to the template table.
Match up the fields, and then run it.

In either case you may have some data fallout depending on the source
records. You may need to clean up some data to get it all in place.
 
Daryl S,

Thank you for your attempt at helping me, but I think I might need some more
information. The copy and paste didn't work for me at all, it would only
paste one record, not all 94 that I have. The second option looks a little
more hopeful, but is a little cryptic to me. What is an append query? How
do I base it on the import table? How do I append to the template table?
Match up the fields, does that mean I just need to have all the same fields
in the same places? Sorry, but it has been a long time since I have done any
kind of query, and I have never done and append query.

Thanks again for your much needed assistance!

Teri.
 
Teri -

First, for the copy/paste: Did you select the entire row with the asterisk
before hitting paste? If not, then Access will try to paste the entire table
into the first field. If you did select the entire row, then did you get any
errors telling you why the other records weren't posted? Maybe the data
format is not the same (e.g. a text field that contains number going into a
numeric field - it may not automatically convert, especially if there are
dollar signs or extra spaces). If there are data issues, then they need to
be cleaned up first, and the append query won't help.

Second, for the append query. Create a new query (many ways to do this,
and which Access version you have will make a difference). If you are in
design mode for the query, you should have only the import table in the top
section, above the grid. If it is not there, right-click on the top portion
and select Show Table, and select the import table from the list. No other
tables should be there. Then select the 'Append Query' from Query menu item,
and it will ask what table you want to append the records to. That should be
the template table. Now double-click on each field in the import table that
you want in the template table. That will put the field into the grid below.
If a field in this table has the same name as a field in the template table,
then the "Append To" row will be automatically updated with that field name.
If it is not there, or it is not the right field, then in the "Append To"
row, click and you will get a drop-list of the fields in the template table
to choose from. Select the field where the data should go.

Once the fields are all entered this way, you can see the results by
switching to datasheet mode (usually the left-most button on the toolbar).
Switch back to design mode and if you liked the results, run it using the
button with the red exclamation point on it. This can also give you data
errors, like the copy/paste method does.

Hope that is enough to get you started...
 
Back
Top