If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Relationships and Form Question
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? |
#3
|
|||
|
|||
Relationships and Form Question
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. |
#4
|
|||
|
|||
Relationships and Form Question
Customer Table
Customer ID Customer Name Region etc Contact Table Contact ID Customer ID First Name Last Name 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. |
#5
|
|||
|
|||
Relationships and Form Question
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 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 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. |
#6
|
|||
|
|||
Relationships and Form Question
|
#7
|
|||
|
|||
Relationships and Form Question
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. |
Thread Tools | |
Display Modes | |
|
|