Relationships and Form Question

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?

Please explain what information is in your tables Customer System Inventory
and Testing and we can help you better.

Steve


"Confused" wrote in message
news
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?

On May 27, 2:47*pm, Confused
wrote:
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? *

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.

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?



"Piet Linden" wrote:

On May 27, 2:47 pm, Confused
wrote:
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?

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.

Your Customer System Inventory table and Testing table are not correct.
Consider using the following tables:

TblCustomer
Customer ID
CustomerName
Region
etc

TblContact
ContactID
CustomerID
FirstName
LastName
Email

TblSystem
SystemID
System
SystemName
etc

TblCustomerSystemInventory
CustomerSystemInventoryID
CustomerID
SystemID
SystemVersion
etc.

TblCustomerSystemTesting
CustomerSystemTesting
CustomerSystemInventoryID
DateTested

Note ... I am assuming you are testing systems owned by a customer.

Steve



"Confused" wrote in message
...
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?



"Piet Linden" wrote:

On May 27, 2:47 pm, Confused
wrote:
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?

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.

With the proposed tables you would need to create a form/subform/subform,
The main form would be based on TblCustomer. The first subform would be a
subform in the main form and would be based on TblCustomerSystemInventory.
The second subform would be a subform in the previous subform and would be
based on TblCustomerSystemTesting.

Steve


"Steve" wrote in message
...
Your Customer System Inventory table and Testing table are not correct.
Consider using the following tables:

TblCustomer
Customer ID
CustomerName
Region
etc

TblContact
ContactID
CustomerID
FirstName
LastName
Email

TblSystem
SystemID
System
SystemName
etc

TblCustomerSystemInventory
CustomerSystemInventoryID
CustomerID
SystemID
SystemVersion
etc.

TblCustomerSystemTesting
CustomerSystemTesting
CustomerSystemInventoryID
DateTested

Note ... I am assuming you are testing systems owned by a customer.

Steve



"Confused" wrote in message
...
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?



"Piet Linden" wrote:

On May 27, 2:47 pm, Confused
wrote:
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?

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.

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.