Sharing data between tables

  • Thread starter Thread starter coquist
  • Start date Start date
C

coquist

Hello,

I'm a relatively new Access user and I have a question about sharin
data between 2 tables. My 2 tables are Data and Agents. The primar
key for both tables is AgentID. I need to add a field in the Dat
table which will bring in the name of the agent in the Agents table.
tried doing this through a lookup and query, however, it only created
dropdown menu with the entire list of agent names rather than the on
name that belongs to that record based on the relationship of th
AgentID.

Any help is greatly appreciated.

Thanks,
Chri
 
If you have an AgentID as a primary key you probably don't want th
agents name in the Data table (that would be redundant). What you wan
to do is create a query that joins the two tables where you get th
data from the Data Table and add the agent name from the other table
then you use that query
 
Thanks for the reply. I should've mentioned this initially, but I'
using the database online and query the information from webpages.
Since the data is in two different tables, it's takes longer for a pag
to load because it has to do two separate queries, match the data, the
display the data. If it was all in one table I would only have to d
one query and not have to do run any code to join the data together.
Thus loading the page significantly faster.

Thanks,
Chri
 
Ok, you need to add a field to to your data table to hold the agent nam
and then you need to create an Update query. create a new query an
add the two tables to the query. Make sure that the two tables ar
joined at the agentid field. Add the data table field for the agen
name in the query grid and type in the name of the table and field fo
the Agent table name in the update to row on the grid. Run the query.
It should come up and tell you that you are about to update X number o
rows. The number of rows should match the number of records in th
agent and data tables. Say yes.

Ps. create a backup of either the db or the tables before you do thi
and then check and make sure that the data is how you expected it to b
before blessing it. If you make a mistake then fall back your backup
and try again
 
A note of caution. You will now have the agents name in two places i
your database. If you change the name in the agents table then it wil
not be changed in the Data table, you will have make the chang
programmatically
 
Back
Top