RE: Showing data based on other values

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Valerie,

Importing and linking have two different purposes. Importing creates a
local copy of the data in your database. Linking, provides a way to get to
data not in the local database and that you do not want in the database.
Probably the most common use is to link to a table in another database.
Depending on what you link to you may be able to and and update data through
the table link. Links to Excel no longer allow for that. I am guessing that
in your situation you desire that the data be in your database. To create a
link to table, you do this in Access 2003 or earlier: Click on the Tables
item under Objects in the database window. Then click on "New" in the
databse window tool bar. Choose Link Table. Follow the prompts to complete
the process. Or you can choose File menu and Get External Data item and then
Link Tables. In Access 2007 go to the External Data ribbon and then in
Import section click on the Excel icon. Choose the file and choose "Link to
the data source by creating a linked table.". Follow the prompts. Search
Access's online help for "linked table" for more information.

Now, the reason I suggested using the linked table to the detail
spread*** is that you need to add in a project ID to each row before you
can store it into your detail table. If you imported it, you would still
need to do this and you could not import it directly into the detail table,
you would have to import it into a temporary or work table. Regardless of
which way you did it, you would then do an append query to get the
information into the actual detail table. If imported into a temporary or
work table, you would then need to delete either the table or the data in the
table. With a linked table, you avoid the need to add the data in locally
and then later on remove it.

To create an append query you start out creating a regular (select)
query that has the columns of data you want to add into another table. Then
in Access 2003 or earlier you go to the Query menu and choose Append Query.
In Access 2007 you click on the Append icon in the Query Type section of the
(Query) Design ribbon. Choose the destination table. In the Append To: row
of the query design screen it will try to match fields in the query to fields
in the table. Correct as needed. When you want to do the actual append
action click on the Run (!) icon.

I would suggest you try this out in a test copy of your database before
you do it for real. Then if you do not get it right the first time you have
not added a bunch of bad data rows to the production system. You can search
Access's online help for "append query", with the quotes, for more
information.

Hope that helps,

Clifford Bass

"Valerie Wong" wrote:

Hi Clifford,

Many thanks. What is the advantage of linking Excel data to the table? At
the moment I import the data from Excel, but I don't 'linked' them
permanantly. How do I link them?

Also what do you mean by the append query? Could you please tell me what are
the steps to do the query? Apologies as I am a new user so need a little more
guidance.

Regards,
Valerie
.

Quantcast