Nick:
Your problems stem from a flawed design. Its not difficult to correct this,
however, and things will then become very much simpler.
What you have here are two main entity types, Characters and Subplots, and a
many-to-many relationship type between them, i.e. each character can be in
one or more subplot, and each subplot can involve one or more characters. So
starting with the two maim tables:
Characters
….CharacterID
….Character
Subplots
….SubplotID
….Subplot
The relationship type between them is modelled by a third table:
SubplotCharacters
….SubplotID
….CharacterID
Each of the two columns is a foreign key referencing the primary key of
Subplots and Characters respectively. The primary key of this table is a
composite one made up of both columns. You can of course add other non-key
columns to this table representing other attributes of a character's role in
a subplot if necessary.
Once you've created the SubplotCharacters table filling it is easy with three
'append' queries:
INSERT INTO SubplotCharacters(SubplotID, CharacterID)
SELECT SubplotID, [Character 1]
FROM Subplots
WHERE [Character 1] IS NOT NULL;
INSERT INTO SubplotCharacters(SubplotID, CharacterID)
SELECT SubplotID, [Character 2]
FROM Subplots
WHERE [Character 2] IS NOT NULL;
INSERT INTO SubplotCharacters(SubplotID, CharacterID)
SELECT SubplotID, [Character 3]
FROM Subplots
WHERE [Character 3] IS NOT NULL;
Once you are happy that the table has been filled correctly you can deleted
the redundant Character 1-3 columns from Subplots
You can now have as few or as many characters per subplot simply by inserting
a row for each into SubplotCharacters.
To return which characters are in which subplots you now just need a simple
query:
SELECT Subplot, Character
FROM Characters, SubplotCharacters, Subplots
WHERE Subplots.CharacterID = Characters.CharacterID
AND Subplots.SubplotID = Subplots.SubPlotID
ORDER BY Subplot, Character;
This will group order the results by subplot and then characters in each. To
order it by character, then those subplots in which each is involved just
change the SELECT clause to:
SELECT Character, Subplot
And the ORDER BY clause to:
ORDER BY Character, Subplot
If you want to return the subplots in which a particular character is
involved you can add a parameter:
SELECT Character, Subplot
FROM Characters, SubplotCharacters, Subplots
WHERE Subplots.CharacterID = Characters.CharacterID
AND Subplots.SubplotID = Subplots.SubPlotID
AND (Character = [Enter character:]
OR [Enter character:] IS NULL)
ORDER BY Character, Subplot;
This will return the subplots in which the character entered at the prompt is
involved, or if no character is entered at the prompt, all subplots.
Similarly if you want to return characters is involved in a particular
subplot:
SELECT Subplot, Character
FROM Characters, SubplotCharacters, Subplots
WHERE Subplots.CharacterID = Characters.CharacterID
AND Subplots.SubplotID = Subplots.SubPlotID
AND (Subplot = [Enter subplot:]
OR [Enter subplot:] IS NULL)
ORDER BY Subplot, Character;
In a developed application, rather than simply opening queries, you'd
probably design a dialogue form with combo or list boxes to select characters
or subplots and open a form or report based on one of the queries from the
dialogue form, changing the simple parameters to ones which reference the
combo or list box on the form .
For data entry an appropriate solution would be a form base on Subplots, in
single form view, and within it a subform in continuous forms view, based on
SubplotCharacters. The parent form and subform would be linked on SubplotID
and would include a combo box set up as follows:
ContrlSource: CharacterID
RowSource: SELECT CharacterID, Character FROM Characters ORDER BY
Character;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
The combo box would show the character by name, but its underlying value
would be the hidden CharacterID for the selected character. You can of
course include other controls in the subform bound to any other non-key
columns from SubplotCharacters. Assigning a character to a subplot is then
simply a case of inserting a new row in the subform by selecting a character
from the combo box in the empty row at the bottom on the subform.
Ken Sheridan
Stafford, England
Nick said:
[quoted text clipped - 10 lines]
For the future, it may be easier to tell us your actual table and field
names instead of trying to be generic.
I realise that now. I thought the solution would be a lot simpler than
it actually was.
If you are still confused, post back on where you need further
clarification.
Clifford Bass
I ran the query and it asked me to input parameters. What am I
supposed to enter into these boxes?
