Relationship Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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!
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top