Problems with relationships and master to child

[Alvarogue]

I have a problem with my tables.

To use a search combo box, I had to create a relationship between 2 tables ( CompanyT and CustomerT ) and I had to to a Query in order for the combo box to work property.

The problem is that when I add them to a Form, and add a different subform, I have problems because they come from the Query, and not the tables itself. Could someone tell me how to fix this??

Here are my Master & Child props:

Source Object: TruckF
LinkMasterField: TruckID
LinkChildField: CustomerID

Thank you!

 

[Alvarogue]

Here are my Master & Child props for my subform:

Source Object: TruckF
LinkMasterField: TruckID
LinkChildField: CustomerID


Table Relationship is:

*CustomerT One to One CompanyT
*CompanyT One to Many TruckT
*CompanyT One to Many DriverT
*TruckT One to One DriverT

 

[plog]

First, queries that directly interact with data (add/edit/delete) should be based on tables, not queries.

Second, you shouldn't have a 1-1 relationship between tables. Instead all that data should be in the same table. So if only 1 Driver can drive 1 truck, those two pieces of data should reside in the same table same for Customer to Company. That means you have these relationships:

CompanyT One to Many TruckT

And no Customer nor Driver tables. That data is in the CompanyT and TruckT tables respectively.

 

[Alvarogue]

First, queries that directly interact with data (add/edit/delete) should be based on tables, not queries.

Second, you shouldn't have a 1-1 relationship between tables. Instead all that data should be in the same table. So if only 1 Driver can drive 1 truck, those two pieces of data should reside in the same table same for Customer to Company. That means you have these relationships:

CompanyT One to Many TruckT

And no Customer nor Driver tables. That data is in the CompanyT and TruckT tables respectively.

so normalization in this sense should not be done?
what about if a customer sells his company? then I need to change the information in multiple tables?
So truckT and TableT should reside in the same table??? and what about If I want to do a query with every single Driver? should that be directly in the same table or a separate table?

 

[plog]

so normalization in this sense should not be done?

Normalization is normalization and should be done. Normalizing does not involve 1-1 relationships.

So truckT and TableT should reside in the same table???

Huh? What is TableT? Why the lower case on truckT?

and what about If I want to do a query with every single Driver? should that be directly in the same table or a separate table?

Driver information will be in the Truck table. If you want to know about drivers, bring in the Truck table.

 

[plog]

what about if a customer sells his company? then I need to change the information in multiple tables?

If you want to keep a history of what customer has owned what company, then it is no longer a many-to-one. If you are fine just replacing that information, then you would do so only in the Company table.

Also, this question makes it seem like you aren't linking your tables properly via foreign keys (http://www.w3schools.com/sql/sql_foreignkey.asp)