Form/Table help

[dragons life]

I have a working database that I created for one of my RPG games. I would
like help on one thing though. Here is the situation.

Tables—

Characters: (5 records)
Character ID
Character Name

Missions-Main: (240 records)
Mission ID
Mission Name
Complete (Yes/No)
Bonus (Yes/No)

Missions: (1200 records)
Character Name
Mission Name
Complete (Yes/No)
Bonus (Yes/No)


At this moment I have to enter missions into both tables. Missions-Main,
just to keep a clean copy of Missions entered and Missions, I have to enter
all missions here 5 times (for each character). Is there a way to enter all
missions into just one table just once yet have the following?
I need to keep track of which character has completed which missions, but
need all missions to show in the list. So I would like to have this happen
if possible. Enter all missions into the Missions-Main table once, and have
them show up on the form so that if a character has not completed a mission,
it is pulling from the Main table, but if the character has completed the
mission, it adds it to the Missions table and shows from there.

Is that understandable?

Please help, thanks.

 

[Smartin]

I have a working database that I created for one of my RPG games. I would
like help on one thing though. Here is the situation.

Tables—

Characters: (5 records)
Character ID
Character Name

Missions-Main: (240 records)
Mission ID
Mission Name
Complete (Yes/No)
Bonus (Yes/No)

Missions: (1200 records)
Character Name
Mission Name
Complete (Yes/No)
Bonus (Yes/No)


At this moment I have to enter missions into both tables. Missions-Main,
just to keep a clean copy of Missions entered and Missions, I have to enter
all missions here 5 times (for each character). Is there a way to enter all
missions into just one table just once yet have the following?
I need to keep track of which character has completed which missions, but
need all missions to show in the list. So I would like to have this happen
if possible. Enter all missions into the Missions-Main table once, and have
them show up on the form so that if a character has not completed a mission,
it is pulling from the Main table, but if the character has completed the
mission, it adds it to the Missions table and shows from there.

Is that understandable?

Please help, thanks.

My first thoughts (not always right (^: ) are:

Do away with the redundant Missions table.

Add a primary key to Missions-Main (assuming you haven't already).
Remove Complete? and Bonus? fields are they pertain to a character's
implementation of a Mission, not to the Mission itself. Put those fields
in the next table I describe.

Create a joining table to express which characters have played which
missions, e.g., CharacterMissions. This consists of fields for
CharacterID and MissionID. Also add the Complete and Bonus fields you
removed from Missions-Main. See how these will be specific to each
character and mission?. Set up 1:N relationships to this table from
Characters and Missions-Main.

From here, I'm not sure how to proceed. How is your form based? By
Character, or by Mission?

Perhaps, create a left join query to show all records from
CharacterMissions and relevant rows from Characters. This query can be
the basis of a subform of Characters, for example.

Does this give you any ideas?

 

[dragons life]

My form is based on characters. The problem I am having with your suggestion
is that once I added the subform of CharacterMission, it only shows those
missions in which the charcter has completed and not any of those yet to
complete.

 

[Smartin]

My form is based on characters. The problem I am having with your suggestion
is that once I added the subform of CharacterMission, it only shows those
missions in which the charcter has completed and not any of those yet to
complete.

Sorry dragon, as I said I'm "not always right". I worked through my own
suggestions and regret to say I was unable to get it to work as you and
I intended. Hopefully someone with more brain cells than me has an answer.

 

[dragons life]

Don't worry about it.. it is a touch question and I don't even know if it is
possible. Thanks for your help though. )

 

[Tim Ferguson]

My form is based on characters. The problem I am having with your
suggestion is that once I added the subform of CharacterMission, it
only shows those missions in which the charcter has completed and not
any of those yet to complete.




Characters: (5 records)
Character ID
Character Name

Missions-Main: (240 records)
Mission ID
Mission Name
Complete (Yes/No)
Bonus (Yes/No)

Missions: (1200 records)
Character Name
Mission Name
Complete (Yes/No)
Bonus (Yes/No)

If the Missions table is meant to keep track of what missionmains were
carried out by which Character, then it's only nearly right. Try this:

Missions(
CharacterID Long Integer
FK references Characters.CharacterID,
MissionID Long Integer
FK references MissionsMain.MissionID,
Complete,
Bonus,
etc
)

a bit of maths suggests that every combination of MissionMains and
Characters but only one of each (combination). Therefore it would be
logical to make the pk out of (CharacterID, MissionID).

Now, the rest is simplicity itself. If you want the Missions that a
particular Character has _not_ done, then you need

PARAMETERS [Select your characterid:] INTEGER;
SELECT MissionID
FROM MissionMain
WHERE MissionID NOT IN (
SELECT MissionID
FROM Missions
WHERE CharacterID = [Select your characterid:]
)
ORDER BY MissionID;

If you need to see the names, then just join the tables on appropriately.

PS: having spaces and dashes in field names and table names is legal in
Access but it's very not recommended because it _will_ introduce bugs
into your program when you forget to put the [brackets] round everything.

Hope that helps


Tim F

 

[dragons life]

Thank you for your reply. I am pretty much self taught in access so I am not
exacty sure what you mean. I mostly understand the table set up but not sure
of what "fk" and "pk" mean. Also, where in the form would I put:

PARAMETERS [Select your characterid:] INTEGER;
SELECT MissionID
FROM MissionMain
WHERE MissionID NOT IN (
SELECT MissionID
FROM Missions
WHERE CharacterID = [Select your characterid:]
)
ORDER BY MissionID;

Thank you again for your help.

Dragons Life

If the Missions table is meant to keep track of what missionmains were
carried out by which Character, then it's only nearly right. Try this:

Missions(
CharacterID Long Integer
FK references Characters.CharacterID,
MissionID Long Integer
FK references MissionsMain.MissionID,
Complete,
Bonus,
etc
)

a bit of maths suggests that every combination of MissionMains and
Characters but only one of each (combination). Therefore it would be
logical to make the pk out of (CharacterID, MissionID).

Now, the rest is simplicity itself. If you want the Missions that a
particular Character has _not_ done, then you need

PARAMETERS [Select your characterid:] INTEGER;
SELECT MissionID
FROM MissionMain
WHERE MissionID NOT IN (
SELECT MissionID
FROM Missions
WHERE CharacterID = [Select your characterid:]
)
ORDER BY MissionID;

If you need to see the names, then just join the tables on appropriately.

PS: having spaces and dashes in field names and table names is legal in
Access but it's very not recommended because it _will_ introduce bugs
into your program when you forget to put the [brackets] round everything.

Hope that helps


Tim F

 

[Tim Ferguson]

Thank you for your reply. I am pretty much self taught in access so I
am not exacty sure what you mean. I mostly understand the table set
up but not sure of what "fk" and "pk" mean.

fk = foreign key; a number field that contains a value that identifies a
row in the other table (sometimes more than one field, sometimes not
numeric)

pk = primary key; a field, often numeric, sometimes more than one field.
Its job is to identify exactly one record in the table.

Also, where in the form would I put:

PARAMETERS [Select your characterid:] INTEGER;
SELECT MissionID
etc

Well: that is a query that returns all the missionIds that are not
associated with a particular characterId. You could run it as a querydef,
in which case it would present you with an input box requesting the
characterId. Or you could put it into the rowsource of a list box, but in
that case you would want to change the parameter so that it gets
characterId from a control on the form.

I'm afraid that my newsreader has lost track of the first part of this
thread. I remember that you had been somewhat sidetracked by some other
contributions, so I thought that it would be helpful to suggest a basic
design that would actually model what you wanted. I still think it would
be best for you to get your head around the three tables and how they
work. Once the light dawns on that, then the query I suggested above
becomes, well, easier!

Hope that helps


Tim F