Importing tables and linking them

  • Thread starter Thread starter mohd21uk via AccessMonster.com
  • Start date Start date
M

mohd21uk via AccessMonster.com

I have a table called tbl CATS which I have imported from a spreadsheet I
have then used table analyser to split the table inorder to normalise it.

The table was originally in the following format below:

tbl CATS

WBS Code User Id User Name Date A/A Type Hours
GB2-L0144.02 205445 John Edwards 03/01/2006 401 1
GB2-L0144.02 205445 John Edwards 03/01/2006 40. 7.5
GB2-L0155.02 315454 Michael Andrews 05/05/2006 401 6.5
GB2-L0155.02 315454 Micheal Andrews 05/05/2006 500 3.2
GB2-L0166.03 653324 Claire Edwards 06/06/2006 690 7.5
GB2-L0166.03 653324 Claire Edwards 06/06/2006 320 2.5
GB2-L0166.03 653324 Claire Edwards 06/06/2006 401 8.5



I have normalised this table in the following tables by splitting fields
containing repeated fields into seperate tables:

tbl WBS Code
WBS Code

tbl User
User Id
User Name

tbl A/A Type
A/A TyPE

Table 1 (a duplicate of table CATS) contains a lookup to the above fields and
includes date and hours which have not been exported to another table. Since
I have used the table analyser all of this has been created through the
automated process with relationships being created as well.

Table 1 only contains a reference to USER Id but no reference to User Name. I
have created an extra field in Table 1 which contains the User Name field. I
would now like this to be filled based on the user Id. The User id field uses
the query below as a lookup:

SELECT [tbl User].ID AS xyz_ID_xyz, [tbl User].[User Id] AS xyz_DispExpr_xyz,
[tbl User].[User Name], [tbl User].[User Id] FROM [tbl User] ORDER BY [tbl
User].[User Na

How can I modify this to import the User Name as well.

Mnay Thanks.
 
The whole point of normalising your data is to avoid doing what you want
to do. If you have User Name fields both in Table 1 and in tbl User, any
time you add a user or a user's details change, you need to update both
tables.

Instead, any time you need the user name, use a query that joins Table 1
and tbl User on the User Id field.


I have a table called tbl CATS which I have imported from a spreadsheet I
have then used table analyser to split the table inorder to normalise it.

The table was originally in the following format below:

tbl CATS

WBS Code User Id User Name Date A/A Type Hours
GB2-L0144.02 205445 John Edwards 03/01/2006 401 1
GB2-L0144.02 205445 John Edwards 03/01/2006 40. 7.5
GB2-L0155.02 315454 Michael Andrews 05/05/2006 401 6.5
GB2-L0155.02 315454 Micheal Andrews 05/05/2006 500 3.2
GB2-L0166.03 653324 Claire Edwards 06/06/2006 690 7.5
GB2-L0166.03 653324 Claire Edwards 06/06/2006 320 2.5
GB2-L0166.03 653324 Claire Edwards 06/06/2006 401 8.5



I have normalised this table in the following tables by splitting fields
containing repeated fields into seperate tables:

tbl WBS Code
WBS Code

tbl User
User Id
User Name

tbl A/A Type
A/A TyPE

Table 1 (a duplicate of table CATS) contains a lookup to the above fields and
includes date and hours which have not been exported to another table. Since
I have used the table analyser all of this has been created through the
automated process with relationships being created as well.

Table 1 only contains a reference to USER Id but no reference to User Name. I
have created an extra field in Table 1 which contains the User Name field. I
would now like this to be filled based on the user Id. The User id field uses
the query below as a lookup:

SELECT [tbl User].ID AS xyz_ID_xyz, [tbl User].[User Id] AS xyz_DispExpr_xyz,
[tbl User].[User Name], [tbl User].[User Id] FROM [tbl User] ORDER BY [tbl
User].[User Na

How can I modify this to import the User Name as well.

Mnay Thanks.
Click to expand...
 
Back
Top