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
|
|||
|
|||
Relationship Help!
Background: my form is for simple data tracking. I have 4 bound combo boxes
and another table with fields such as Date, ChangeMadeBy, Requestor and Comments. I added fields to this table for the combo boxes, the combo boxes are bound to these fields, and the form is bound to this table. I use an autonumber ID field in the look up tables for the combo boxes to set a one to many relationship with each of them in a line. But I can't come up with a way to get my data table, Otherfields to relate. I did have a One to One relation ship set, pointing from OtherFields to my first combo box, ChangeMadeTo. If I used just these 2 tables in a query, things seemed okay. But if I add all my tables to the query, the main table becomes ChangeMadeTo instead of OtherFields and the query pulls the first record over and over. Any suggestions? |
#2
|
|||
|
|||
Because we cannot see your database, please provide us with more specific
details about the tables, form, and combo boxes. What are the table names and contents (including field names)? What are the Row Source values for the combo boxes? What is the Record Source of the form? And so on. It's not clear from what you posted what you want to achieve, etc. -- Ken Snell MS ACCESS MVP "BadJooJooJen" wrote in message ... Background: my form is for simple data tracking. I have 4 bound combo boxes and another table with fields such as Date, ChangeMadeBy, Requestor and Comments. I added fields to this table for the combo boxes, the combo boxes are bound to these fields, and the form is bound to this table. I use an autonumber ID field in the look up tables for the combo boxes to set a one to many relationship with each of them in a line. But I can't come up with a way to get my data table, Otherfields to relate. I did have a One to One relation ship set, pointing from OtherFields to my first combo box, ChangeMadeTo. If I used just these 2 tables in a query, things seemed okay. But if I add all my tables to the query, the main table becomes ChangeMadeTo instead of OtherFields and the query pulls the first record over and over. Any suggestions? |
#3
|
|||
|
|||
Tables: Otherfields, ChangeMadeTo, ChangeType, RuleGroups,
RuleNumberandName. Fields: Otherfields: Date, Change Made By (text, combo look up with 3 values, bound to this field), Requestor (text), Change Made To, Type of Change, Rule Groups, Rule Number and Name, Comments (memo) ChangeMadeTo: ChangeID, ChangeMade ChangeType:TypeID, ChangeType, ChangeMade, ChangeID RuleGroups: GroupID, RuleGroups, ChangeType, TypeID RuleNumberandName: RuleNameID, NumberandName, RuleGroups, GroupID. RowSource for all combo boxes: Select queries like SELECT ChangeMadeTo.ChangeID, ChangeMadeTo.ChangeMade FROM ChangeMadeTo ORDER BY [ChangeID]; Combo Box tables are related by Primary key to corresponding foreign key in next table. RecordSouce for form: Otherfields The control source for the combo boxes is the corresponding field in the Otherfields table. Otherfields isn't really related to the tables for the combo boxes. Need to define a relationship so I can set up queries for reports. My goal is to pull various reports based on the tracked data. (Who requested what during certain timeframe. What changes were made in timeframe, etc.) I'm not real picky on how this gets done and I'm using a copy of my current database to try out different methods. Thanks! "Ken Snell [MVP]" wrote: Because we cannot see your database, please provide us with more specific details about the tables, form, and combo boxes. What are the table names and contents (including field names)? What are the Row Source values for the combo boxes? What is the Record Source of the form? And so on. It's not clear from what you posted what you want to achieve, etc. -- Ken Snell MS ACCESS MVP "BadJooJooJen" wrote in message ... Background: my form is for simple data tracking. I have 4 bound combo boxes and another table with fields such as Date, ChangeMadeBy, Requestor and Comments. I added fields to this table for the combo boxes, the combo boxes are bound to these fields, and the form is bound to this table. I use an autonumber ID field in the look up tables for the combo boxes to set a one to many relationship with each of them in a line. But I can't come up with a way to get my data table, Otherfields to relate. I did have a One to One relation ship set, pointing from OtherFields to my first combo box, ChangeMadeTo. If I used just these 2 tables in a query, things seemed okay. But if I add all my tables to the query, the main table becomes ChangeMadeTo instead of OtherFields and the query pulls the first record over and over. Any suggestions? |
#4
|
|||
|
|||
What you're seeking are a series of queries with this general structure, it
seems: SELECT OF.[Date], OF.[Change Made By], OF.Reqeustor, CMT.ChangeMade, CT.ChangeType, RG.RuleGroups, RNN.NumberAndName, OF.Comments FROM (((Otherfields AS OF INNER JOIN ChangeMadeTo AS CMT ON OF.[Change Made To] = CMT.ChangeID) INNER JOIN ChangeType AS CT ON OF.[Type of Change] = CT.TypeID) INNER JOIN RuleGroups AS RG ON OF.[Rule Groups] = RG.GroupID) INNER JOIN RuleNumberandName AS RNN ON OF.[Rule Number and Name] = RNN.RuleNameID WHERE "a where clause goes here"; -- Ken Snell MS ACCESS MVP "BadJooJooJen" wrote in message ... Tables: Otherfields, ChangeMadeTo, ChangeType, RuleGroups, RuleNumberandName. Fields: Otherfields: Date, Change Made By (text, combo look up with 3 values, bound to this field), Requestor (text), Change Made To, Type of Change, Rule Groups, Rule Number and Name, Comments (memo) ChangeMadeTo: ChangeID, ChangeMade ChangeType:TypeID, ChangeType, ChangeMade, ChangeID RuleGroups: GroupID, RuleGroups, ChangeType, TypeID RuleNumberandName: RuleNameID, NumberandName, RuleGroups, GroupID. RowSource for all combo boxes: Select queries like SELECT ChangeMadeTo.ChangeID, ChangeMadeTo.ChangeMade FROM ChangeMadeTo ORDER BY [ChangeID]; Combo Box tables are related by Primary key to corresponding foreign key in next table. RecordSouce for form: Otherfields The control source for the combo boxes is the corresponding field in the Otherfields table. Otherfields isn't really related to the tables for the combo boxes. Need to define a relationship so I can set up queries for reports. My goal is to pull various reports based on the tracked data. (Who requested what during certain timeframe. What changes were made in timeframe, etc.) I'm not real picky on how this gets done and I'm using a copy of my current database to try out different methods. Thanks! "Ken Snell [MVP]" wrote: Because we cannot see your database, please provide us with more specific details about the tables, form, and combo boxes. What are the table names and contents (including field names)? What are the Row Source values for the combo boxes? What is the Record Source of the form? And so on. It's not clear from what you posted what you want to achieve, etc. -- Ken Snell MS ACCESS MVP "BadJooJooJen" wrote in message ... Background: my form is for simple data tracking. I have 4 bound combo boxes and another table with fields such as Date, ChangeMadeBy, Requestor and Comments. I added fields to this table for the combo boxes, the combo boxes are bound to these fields, and the form is bound to this table. I use an autonumber ID field in the look up tables for the combo boxes to set a one to many relationship with each of them in a line. But I can't come up with a way to get my data table, Otherfields to relate. I did have a One to One relation ship set, pointing from OtherFields to my first combo box, ChangeMadeTo. If I used just these 2 tables in a query, things seemed okay. But if I add all my tables to the query, the main table becomes ChangeMadeTo instead of OtherFields and the query pulls the first record over and over. Any suggestions? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Database Shapes - Entity Relationship (US units) - Can't anchor Relationship shape on Entity shape | [email protected] | Visio | 1 | March 28th, 2005 04:47 AM |
Deleting a foreign key relationship in SQL | Stevio | Running & Setting Up Queries | 2 | December 22nd, 2004 02:51 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Setting dual relationship with tool connector | Carlos | Visio | 0 | May 20th, 2004 12:51 AM |