If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Show all from tables in my junction...
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. |
#2
|
|||
|
|||
Show all from tables in my junction...
I noticed that nobody answered. You talked a lot about other tasks and
DBjargon, but skipped the important thing which is a clear description of your table structure. Andsince you used the word "juction" with "junction setup" and "junction query" , but never with "table" it's not even clear whether or not you have a junction TABLE. |
Thread Tools | |
Display Modes | |
|
|