Relationships and Form Question

[Confused]

I'm new at this and trying to do it by the book and would appreciate some
guidance on the layout.

I have tables (Customers and Contacts) that I related using Customer ID.
That worked fine for the Contacts form I created. Now I have two more tables
I created, (Customer System Inventory, and Testing).

For my new Testing Table which has no records, how should I relate it? What
field does it need besides Testing Information. I want to eventually make a
form and include the information from the Testing Table and the Contact
Table. But of course all of the Customers that will be testing are on the
CustomerSystem Inventory Table! Should I relate everything back to the
customer ID, since all of this involves the same customers?

I tried doing this with a query linking it all the ways I can find, but I
always ending up being able to type in fields that belong to one of either of
the tables.

How should I design this?

 

[Piet Linden]

I'm new at this and trying to do it by the book and would appreciate some
guidance on the layout.

I have tables (Customers and Contacts) that I related using Customer ID.  
That worked fine for the Contacts form I created.  Now I have two more tables
I created,  (Customer System Inventory, and Testing).  

For my new Testing Table which has no records, how should I relate it? What
field does it need besides Testing Information. I want to eventually makea
form and include the information from the Testing Table and the Contact
Table.  But of course all of the Customers that will be testing are on the
CustomerSystem Inventory Table!  Should I relate everything back to the
customer ID, since all of this involves the same customers?  

I tried doing this with a query linking it all the ways I can find, but I
always ending up being able to type in fields that belong to one of either of
the tables.  

How should I design this?  

Beats the hell outta me.... how are they related in real life? What
exactly are you modeling? Sounds like it's time to turn the computer
off and get out the pencil and paper and draw your relationship
diagram. If you don't have that clear in your mind, trying to code is
really just wasting time. It's like trying to drive somewhere when
you don't really know where you're going.

 

[Piet Linden]

I'm new at this and trying to do it by the book and would appreciate some
guidance on the layout.

I have tables (Customers and Contacts) that I related using Customer ID.  
That worked fine for the Contacts form I created.  Now I have two more tables
I created,  (Customer System Inventory, and Testing).  

For my new Testing Table which has no records, how should I relate it? What
field does it need besides Testing Information. I want to eventually makea
form and include the information from the Testing Table and the Contact
Table.  But of course all of the Customers that will be testing are on the
CustomerSystem Inventory Table!  Should I relate everything back to the
customer ID, since all of this involves the same customers?  

I tried doing this with a query linking it all the ways I can find, but I
always ending up being able to type in fields that belong to one of either of
the tables.  

How should I design this?  

Beats the hell outta me.... how are they related in real life? What
exactly are you modeling? Sounds like it's time to turn the computer
off and get out the pencil and paper and draw your relationship
diagram. If you don't have that clear in your mind, trying to code is
really just wasting time. It's like trying to drive somewhere when
you don't really know where you're going.

 

[Confused]

Customer Table
Customer ID
Customer Name
Region
etc

Contact Table
Contact ID
Customer ID
First Name
Last Name
Email


Customer System Inventory has the same customer in the Customer table but
only include the customers that have systems.

(Customer System Inventory Table)
Customer ID
Customer Name
System Name
System Version etc.

(Testing Table)
System Tested
Dates Tested
Version Tested

The Testing form would be for gathering the information about the testing,
but at the same time a place to enter the Contact Names, email, and TN of the
Customer and have that feed into the Contacts Table. That is what I am
having a difficult time making work. The customer that is selected on this
form would be selected from the Customer System Inventory Table. (Only
customers with Systems can test).

It seemed pretty simple, but I don't know how to relate Testing Table to
Contacts Table, so that it works together all on one form. Furthermore,
my relationships are all based on Customer ID. I didn't know if I set the
following up correctly:
Contacts relates to Customers on Customer ID. Customer System Inventory
relates to Customers on Customer ID. Testing Table doesn't have a
relationship yet. I didn't know if I should relate it back to the Customers
table or the System INventory table based on customer ID or not?

 

[Confused]

Customer Table
Customer ID
Customer Name
Region
etc

Contact Table
Contact ID
Customer ID
First Name
Last Name
Email


Customer System Inventory has the same customer in the Customer table but
only include the customers that have systems.

(Customer System Inventory Table)
Customer ID
Customer Name
System Name
System Version etc.

(Testing Table)
System Tested
Dates Tested
Version Tested

The Testing form would be for gathering the information about the testing,
but at the same time a place to enter the Contact Names, email, and TN of the
Customer and have that feed into the Contacts Table. That is what I am
having a difficult time making work. The customer that is selected on this
form would be selected from the Customer System Inventory Table. (Only
customers with Systems can test).

It seemed pretty simple, but I don't know how to relate Testing Table to
Contacts Table, so that it works together all on one form. Furthermore,
my relationships are all based on Customer ID. I didn't know if I set the
following up correctly:
Contacts relates to Customers on Customer ID. Customer System Inventory
relates to Customers on Customer ID. Testing Table doesn't have a
relationship yet. I didn't know if I should relate it back to the Customers
table or the System INventory table based on customer ID or not?

 

[Fred]

All of the received advice is good, but I think that Piet's will be the best
for you for this design and your next one.

The fact that your posts all skipped a key step/piece of information
probably means that that you have skipped it.

So, start by turning off the computer.

The answer lies in the real world process that you are trying to database
plus in your recording mission for this database.

It's clear that a test relates to a particular customer . The question is,
is there a more specific relationship, which would be: does a test (always)
relate to a particular CustomerSystem? And, if so, do you want / need to
record that more specific relationship, given that it will incur a slight
increase in complexity? If the answer to all of the above is "yes", then
link your test table to the CustomerSystems table; if not, then just link it
to the customer table.

 

[Fred]

All of the received advice is good, but I think that Piet's will be the best
for you for this design and your next one.

The fact that your posts all skipped a key step/piece of information
probably means that that you have skipped it.

So, start by turning off the computer.

The answer lies in the real world process that you are trying to database
plus in your recording mission for this database.

It's clear that a test relates to a particular customer . The question is,
is there a more specific relationship, which would be: does a test (always)
relate to a particular CustomerSystem? And, if so, do you want / need to
record that more specific relationship, given that it will incur a slight
increase in complexity? If the answer to all of the above is "yes", then
link your test table to the CustomerSystems table; if not, then just link it
to the customer table.