How to build query/subform based on dynamic, unrelated list?

[planetjeff]

Best way I know how to describe it, sorry!
I am tracking Events and Adjudicators (judges) - each event is of an event type, and each Adjudicator can judge a selection of these types. For example, judge1 may judge three types of events, whereas judge 2 may judge only one type.

The events can only be one of one event type, and the Events table has a field Type that contains this. Each record in the Adjudicator table has a one-to-many relationship with another table called PreferredTypes, that has AdjudicatorID and Type combining as the primary key.

Now, on the Adjudicator_Assignment form, I'd like to list all the events the Adjudicator can judge based on his/her preferred types. The only way I was able to do this was to first create a subform to show the preferred types, and then another subform within that to show the events of that type. Well, that's messy - I'd like it to be all one subform level rather than multilevel.

Any ideas? I've looked here but have yet to find a related problem. Thanks in advance,
jeff

 

[grande]

If I'm understanding you correctly, you can just change the Recordsource of your subform to include all of the information that you want to go on that form...

-------------------------
Just call me Captain Awesome.

 

[planetjeff]

Problem is, I don't understand how! (just started using Access about a week ago...)

The RecordSource of the first subform showing the types an adjudicator can judge just has "PreferredTypes". The RecordSource of the subform within this subform showing all shows that match these types has:

SELECT [Events].[Name], [Events].[Type], [Events].[StartDate], [Events].[EndDate] FROM [Events]

But, nothing there is (visibly) tying it to the parent list of preferred event types - that subform 'magic' with parent/child tying together is escaping me I guess. I would like to show this information without needing to have nested subforms. The main form and first subform are tied by AdjudicatorID, and the first subform and the subform below that are tied by event type.

jeff

 

[PHV]

In your first subform RecordSource JOIN the PreferredTypes table with the Events table on the Type fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Since I don't know what your data is, this isn't exact, but it's a start. Hopefully this will get you going...

SELECT [WhateverYourOtherTableIsCalled].[PreferredTypes], [Events].[Name], [Events].[Type], [Events].[StartDate], [Events].[EndDate] FROM [Events] INNER JOIN [WhateverYourOtherTableIsCalled] ON [WhateverYourOtherTableIsCalled].[Event] = [Events].[Type]

Let me know if you need anything else.

-------------------------
Just call me Captain Awesome.

 

[planetjeff]

I don't think that works, I might have not been very clear:
Tables:
Events (with fields EventID [pk], Name, Type, Start Date, End Date)
Adjudicators (with fields AdjudicatorID [pk], Name, Address, etc.)
PreferredTypes (with fields AdjudicatorID & Type both forming [pk])

The Adjudicators has a one-to-many relationship with the PreferredTypes table, as any adjudicator can judge only certain types of events. There is also a many-to-many relationship (with associated table) between Events and Adjudicators to keep track of assigned events to judge. But there is no relationship of the Events table and the PreferredTypes table besides a common field Type, so I don't think I'm allowed to JOIN them, am I?
jeff

 

[PHV]

But there is no relationship of the Events table and the PreferredTypes table
Isn't PreferredTypes.Type related to Events.Type ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[planetjeff]

So, basically, you're saying that I will need a nested subform? I did get the information I needed already in that manner, but I'd like to view it like one subform, not nested. Is there a way to change how it's viewed?
jeff

 

[PHV]

you're saying that I will need a nested subform
Where have I said such thing ?

In your first subform RecordSource JOIN the PreferredTypes table with the Events table on the Type fields

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[planetjeff]

Oh, I guess I just suck at this... I am attempting to create the subform using the wizard, and the closest I get on the RecordSource is the following:

SELECT [PreferredTypes].[Type] AS [PreferredTypes_Type], [Events].[Name], [Events].[Type] AS [Events_Type], [Events].[StartDate], [Events].[EndDate], [PreferredTypes].[AdjudicatorID] FROM (([Adjudicators] INNER JOIN ([Events] INNER JOIN [Assignments] ON [Events].[EventID] =[Assignments].[EventID]) ON [Adjudicators].[AdjudicatorID] =[Assignments].[AdjudicatorID]) INNER JOIN [PreferredTypes] ON [Adjudicators].[AdjudicatorID] =[PreferredTypes].[AdjudicatorID])

It keeps picking up on the many-to-many relationship (tracked with the Assignments table) between Adjudicators and Events when I don't want it to, and I can't figure out how to do the join on the respective Type fields. I don't feel comfortable enough (yet) messing with SQL statements directly. Have any good online pointers to sql structuring?
jeff

 

[PHV]

HAve you tried to simply add this clause ?
WHERE [PreferredTypes].[Type] = [Events].[Type]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[planetjeff]

Okay, I figured it out, albeit using a slightly different path. First I created the first query to show preferred events per AdjudicatorID:

SELECT Theaters.TheaterID, Theaters.Name AS Theaters_Name, Events.EventID, Events.Name AS Events_Name, Events.Type AS Events_Type, Events.StartDate, Events.EndDate, PreferredTypes.AdjudicatorID, PreferredTypes.Type AS PreferredTypes_Type
FROM Theaters INNER JOIN (Adjudicators INNER JOIN (Events INNER JOIN PreferredTypes ON Events.Type = PreferredTypes.Type) ON Adjudicators.AdjudicatorID = PreferredTypes.AdjudicatorID) ON Theaters.TheaterID = Events.TheaterID;

This got me what I needed, now I wanted to add in a count of how many judges each event has:

SELECT DISTINCTROW Events.EventID, Count(Assignments.EventID) AS [Count Of Assignments]
FROM Events LEFT JOIN Assignments ON Events.EventID = Assignments.EventID
GROUP BY Events.EventID;

Then, a query to combine the two to give preferrered events for each AdjudicatorID and the number of current judges assigned:

SELECT Events_Preferred_Query.Theaters_Name, Events_Preferred_Query.Events_Name, Events_Preferred_Query.Events_Type, Events_Preferred_Query.StartDate, Events_Preferred_Query.EndDate, Events_NumAssignments_Query.[Count Of Assignments], Events_Preferred_Query.AdjudicatorID
FROM Events_Preferred_Query INNER JOIN Events_NumAssignments_Query ON Events_Preferred_Query.EventID = Events_NumAssignments_Query.EventID;

Finally, I just added this as a subform to a form showing each Adjudicator, and it filters per AdjudicatorID. Maybe not the most efficient way to do it, but thanks for all the feedback anyway.
jeff