A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Show all from tables in my junction...



 
 
Thread Tools Display Modes
  #1  
Old December 28th, 2009, 08:19 PM posted to microsoft.public.access.tablesdbdesign
Maarkr
external usenet poster
 
Posts: 240
Default 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  
Old January 4th, 2010, 08:00 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.