M
Maarkr
I've had this situation designing two different dbs recently and wanted to
see if there was a better way to do it.
This db has a Roster table with names and player info, an SDates table with
a weekly play date for the next few months, and a junction setup which works
like its supposed to. The db needs to manage a list of all active players
who may show up weekly and have the option to count them as present and if
they paid. So my query shows the junction and other tables, with a right
join to show all members for every record in the jct.
What it does: The junction query properly shows when I enter a name and date
in the jct query. It shows all players not in the query that have not
attended yet, with no SDate. Not exacltly what I want.
SELECT RSJct.JID, RSJct.StID, Roster.Active, Roster.LFName, RSJct.SID,
SDates.SDate, RSJct.Present, RSJct.Paid
FROM SDates RIGHT JOIN (Roster LEFT JOIN RSJct ON Roster.STID = RSJct.StID)
ON SDates.SID = RSJct.SID
WHERE (((Roster.Active)=-1))
ORDER BY Roster.LFName;
If I query it for each week, those that don't have a date won't show up. I
understand that. I can't do a form/sub based on the date or I get only those
present/paid. In a previous scenario I coded an append query to dump the
players into the junction table for each week (then delete old duplicates),
but I was wondering if there was a different relationship that i'm missing or
if something else may work. I also tried a cartesian join of the two tables
against the junction which showed the proper data (all names, all dates,
present, paid) but it was not updateable.
What i need: I need it to show all active players for each week in a
datasheet filtered by the play date so I can check them off as present and/or
paid.
see if there was a better way to do it.
This db has a Roster table with names and player info, an SDates table with
a weekly play date for the next few months, and a junction setup which works
like its supposed to. The db needs to manage a list of all active players
who may show up weekly and have the option to count them as present and if
they paid. So my query shows the junction and other tables, with a right
join to show all members for every record in the jct.
What it does: The junction query properly shows when I enter a name and date
in the jct query. It shows all players not in the query that have not
attended yet, with no SDate. Not exacltly what I want.
SELECT RSJct.JID, RSJct.StID, Roster.Active, Roster.LFName, RSJct.SID,
SDates.SDate, RSJct.Present, RSJct.Paid
FROM SDates RIGHT JOIN (Roster LEFT JOIN RSJct ON Roster.STID = RSJct.StID)
ON SDates.SID = RSJct.SID
WHERE (((Roster.Active)=-1))
ORDER BY Roster.LFName;
If I query it for each week, those that don't have a date won't show up. I
understand that. I can't do a form/sub based on the date or I get only those
present/paid. In a previous scenario I coded an append query to dump the
players into the junction table for each week (then delete old duplicates),
but I was wondering if there was a different relationship that i'm missing or
if something else may work. I also tried a cartesian join of the two tables
against the junction which showed the proper data (all names, all dates,
present, paid) but it was not updateable.
What i need: I need it to show all active players for each week in a
datasheet filtered by the play date so I can check them off as present and/or
paid.