N00bie to access and need help with reusing data between tables andimplementing it.

[christopher.catlin]

Hi,

I'm currently designing a database for a construction co and learning
how to use access on the fly. I'm having a few problems with the re-
use of data if I link tables using relationships and enter the data
into one table it doesn't seem to show the data in the other table
which its linked to.

Example of Table setups

Table 1
ID
Job No
Project Name
Address
Phone
Fax
Sub Constractor Name
Date
Daily Date
Foreman
Client Contact
Contract Value

Table 2
MemoID
Reference
Details
Job No
Response
FCID
Client Contact
Reason for Sending
Date
Time
Date of Notification
Options
Project Name
Superintendent

Table 1 Job No is linked to Table 2 Job No and that displays since its
a drop down box link to table 1, however Project Name and Client
Contact from Table 1 is link to Table 2 Project Name and Client Contac
but the information doesn't flow into Table 2. I'm assuming that since
these tables are linked that information should auto flow from table1
to table2 but I must be wrong since it doesn't so can someone point me
in the right direction as to what i should do?

[hunterb91]

On Dec 18, 6:37 pm, [e-mail] wrote:

Hi,

I'm currently designing a database for a construction co and learning
how to use access on the fly. I'm having a few problems with the re-
use of data if I link tables using relationships and enter the data
into one table it doesn't seem to show the data in the other table
which its linked to.

Example of Table setups

Table 1
ID
Job No
Project Name
Address
Phone
Fax
Sub Constractor Name
Date
Daily Date
Foreman
Client Contact
Contract Value

Table 2
MemoID
Reference
Details
Job No
Response
FCID
Client Contact
Reason for Sending
Date
Time
Date of Notification
Options
Project Name
Superintendent

Table 1 Job No is linked to Table 2 Job No and that displays since its
a drop down box link to table 1, however Project Name and Client
Contact from Table 1 is link to Table 2 Project Name and Client Contac
but the information doesn't flow into Table 2. I'm assuming that since
these tables are linked that information should auto flow from table1
to table2 but I must be wrong since it doesn't so can someone point me
in the right direction as to what i should do?

Hi Christopher,
I think you have the wrong idea about tables in access. It is an easy
enough mistake, especially when you are learning on the fly. You will
want to take the duplicated information and either pick one table to
have it in and define a relationship or create a new table and relate
all three. I have some time and experience defining tables if you want
to get a hand.

Bill

[hunterb91]

On Dec 18, 6:37 pm, [e-mail] wrote:

Hi,

I'm currently designing a database for a construction co and learning
how to use access on the fly. I'm having a few problems with the re-
use of data if I link tables using relationships and enter the data
into one table it doesn't seem to show the data in the other table
which its linked to.

Example of Table setups

Table 1
ID
Job No
Project Name
Address
Phone
Fax
Sub Constractor Name
Date
Daily Date
Foreman
Client Contact
Contract Value

Table 2
MemoID
Reference
Details
Job No
Response
FCID
Client Contact
Reason for Sending
Date
Time
Date of Notification
Options
Project Name
Superintendent

Table 1 Job No is linked to Table 2 Job No and that displays since its
a drop down box link to table 1, however Project Name and Client
Contact from Table 1 is link to Table 2 Project Name and Client Contac
but the information doesn't flow into Table 2. I'm assuming that since
these tables are linked that information should auto flow from table1
to table2 but I must be wrong since it doesn't so can someone point me
in the right direction as to what i should do?

You want to place Table 1 ID as a foreign key in table 2. Then access
can relate the two tables and you do not have to reuse the same
information in Table 2. To view info from both tables is a simple
querry. Hope that helps. Feel free to let me know

Bill

[anthony]

If you don't get the table design correct at the outset, you will
slowly descend into an ever deeper mire from which, in the end, you
will not emerge! Think of tables as being the container for individual
items. Try to define what items your project consists of. For example
jobs might be one, clients another, subcontractors a third. You then
connect these items using additional linking tables, tables which can
also hold information about that particular link. Doing it this way
allows you to show clients with more than one job, foremen with more
than one job or jobs with more than one foreman; ditto subcontractors.
In other words the items are only ever held once; if you find you are
holding the same items in more than one table, then warning signs
should start flashing that the table design needs fixing. I would
strongly recommend getting a book on database design prior to
expending lots of brain power creating forms, reports etc all of which
will be wasted if the underlying structure is flawed

[hunterb91]

On Dec 19, 9:58 am, anthony <[e-mail]:

If you don't get the table design correct at the outset, you will
slowly descend into an ever deeper mire from which, in the end, you
will not emerge! Think of tables as being the container for individual
items. Try to define what items your project consists of. For example
jobs might be one, clients another, subcontractors a third. You then
connect these items using additional linking tables, tables which can
also hold information about that particular link. Doing it this way
allows you to show clients with more than one job, foremen with more
than one job or jobs with more than one foreman; ditto subcontractors.
In other words the items are only ever held once; if you find you are
holding the same items in more than one table, then warning signs
should start flashing that the table design needs fixing. I would
strongly recommend getting a book on database design prior to
expending lots of brain power creating forms, reports etc all of which
will be wasted if the underlying structure is flawed

right you are anthony. I am available to help you with your table
design if you wish, Christopher.