Finding a value in several different columns

Hi Nick,

Pretty simple. Create the table as mentioned and the second numbers
query; which you will name ""qryNumbers 0-999". Then your query will be:

SELECT [qryNumbers 0-999].The_Number, Subplots.RecordNumber, Subplots.
[Character 1], Subplots.[Character 2], Subplots.[Character 3]
FROM Subplots, [qryNumbers 0-999]
WHERE (((Subplots.[Character 1])=[The_Number])) OR (((Subplots.[Character 2])
=[The_Number])) OR (((Subplots.[Character 3])=[The_Number]))
ORDER BY [qryNumbers 0-999].The_Number, Subplots.RecordNumber;

For the future, it may be easier to tell us your actual table and field
names instead of trying to be generic.

If you are still confused, post back on where you need further
clarification.

Clifford Bass

Nick Xylas wrote:
On 27 Apr, 21:58, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote:
Hi Nick,

Â* Â* Â*I am rather curious; is there some reason that you have not tried my
solution?

Mostly because I wasn't sure how to adapt it from the generic to the
specific (ie what to replace with my actual field and table names).

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201004/1

On 28 Apr, 18:55, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote:
Hi Nick,

* * *Pretty simple. *Create the table as mentioned and the second numbers
query; which you will name ""qryNumbers 0-999". *Then your query will be:

SELECT [qryNumbers 0-999].The_Number, Subplots.RecordNumber, Subplots.
[Character 1], Subplots.[Character 2], Subplots.[Character 3]
FROM Subplots, [qryNumbers 0-999]
WHERE (((Subplots.[Character 1])=[The_Number])) OR (((Subplots.[Character 2])
=[The_Number])) OR (((Subplots.[Character 3])=[The_Number]))
ORDER BY [qryNumbers 0-999].The_Number, Subplots.RecordNumber;

* * *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?

Hi Nick,

The nature of your question associated with the way you set up your
table(s) makes it less than simple.

It should not prompt you for anything unless I or you have mistyped
something. Let's back up a bit. Did you create the "tblNumbers" table? If
not, do so following my instructions in my first reply to you. Next, did you
create the "qryNumbers 0-999" query by copying and pasting my code into the
SQL view of a new query? If not do so, and verify that it works by running
it. If you have all that done and still get prompted for parameters when
running the final query, what are the "parameters" being requested?

Clifford Bass

Nick Xylas wrote:
On 28 Apr, 18:55, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote:
Hi Nick,

[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?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

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 Xylas wrote:
On 28 Apr, 18:55, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote:
Hi Nick,

[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?

--
Message posted via http://www.accessmonster.com

Nick:

Thinking a little more about your requirements, it occurred to me that you
might like to consider using a 'bill of materials' model. In your case each
character would be the equivalent of a 'base part' and each subplot the
equivalent of an 'assembly' made up of base parts and/or other assemblies.
This would mean you could have subplots of subplots right up to the overall
top-level plot, the equivalent of the complete product in a bill of materials.


The classic method of modelling a bill of materials is by means of an
adjacency list, with columns such as MajorPart and MinorPart each of which
reference the key of a Parts table, which includes assemblies as well as base
parts.

You'll find a demo of mine for a bill of materials database in Access at:

http://community.netscape.com/n/pfx/...apps&tid=23133


The file is attached to the second message in the above thread.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1