Refering to the current record in a query

[Mariot]

It is possible to refer in a SQL statement to the "current" or "actual" record?

I want to base the querry in the present value of two related tables. I will try to show you what I have to do, maybe you can give an alternative solution.

These are the tables:

tblTeam (idTeam, TeamName, DayCreated)
tblTeamPerson (idPerson, idTeam)
tblPerson (idPerson, PersonName, MusicPreference)


Every person have a music preference, and the teams have to be formed by people with similar music preference.

So, one way to filter that is to use the following method: everytime I create a team, I choose a person, ok. That firts guy is the one that will give the team his "MusicalPreference". The next members with different MusicalPreferences will be excluded from the query.

But in order to do that, I need to refer to the current team that have 1 member, and use the value of the field MusicPreference in that Person in particular.

I hope you have understand what I have to do, and I hope you can help me with this.

P.S.: I´m using a combo-box with a special query in a subform to select the team members. In the main form I create the team. So it would be great to be able to use that "idTeam" value in relation with the firts team member of that team.

 

[Mariot]

.............................Please?

 

[Simon_MT]

Either you have a pre-determined IdTeam or you make the IDTeam based on the MusicPreference lets take a stab ...

I like Classical Music on the you can either Allocate a Team based on my Preference or have a IDTeam already created.

On a subform list all the IDPerson binding the Parent Form and Child with My Preference. On the Subform add a Tick Button to set the IDTeam to the parent ie MyTeam, that is if the Person is not allocated to a Team.

On the Parent Form you could have

IDPerson SMT
IDTeam TeamC
IDPreference Classical

On the SubForm linked by IDPreference all the other (if any) who like Classical Music

The Button onClick set the IDTeam to the Parent IDTeam.

Hopes this helps.

Simon

 

[Mariot]

Either you have a pre-determined IdTeam or you make the IDTeam based on the MusicPreference lets take a stab ...

I like Classical Music on the you can either Allocate a Team based on my Preference or have a IDTeam already created.

On a subform list all the IDPerson binding the Parent Form and Child with My Preference. On the Subform add a Tick Button to set the IDTeam to the parent ie MyTeam, that is if the Person is not allocated to a Team.

On the Parent Form you could have

IDPerson SMT
IDTeam TeamC
IDPreference Classical

On the SubForm linked by IDPreference all the other (if any) who like Classical Music

The Button onClick set the IDTeam to the Parent IDTeam.

Hopes this helps.

Simon

Thank you Simon, you are really kind.

I read your solution and I kind of figure it out or understand it parcially, but my brain can´t compute it right now.



This part

On the Parent Form you could have

IDPerson SMT
IDTeam TeamC
IDPreference Classical

Let´s see... you say I have to create a field in tblTeam that refers to the "MusicalPreference" of the Team?

I don´t know either how to implement tick buttons I sux

 

[Mariot]

Right now I´m trying to do this:

In the "Team creation Form" I´m adding an unbound control with the Musical Preference, so it can be use to filter the People on a combo-box located in the associated continuos subform (sorry my english sux)

But it doesn´t work... I can´t figure out how to refer to things in forms in a query... a parameter field pop up...

 

[Simon_MT]

OK. Unless you haven't create a Table for Musical Preference. Then I would be tempted to create Music Preference on the Team as this is what the Team is all about. In theory by the very nature of the Teams Music Preference anyone with that specific Musical Taste by inference will be in that Team. This does assume that each person has a single Preference and there is no Team for each Preference and this may not be the case.

In an ideal world you cater for multiple individual preferences and Teams.

Simon

 

[Mariot]

I didn´t want to created a "music preference" for the team because it would be to strict, I mean, maybe the person creating the teams would want to choose a team member with a different music preference.

Well, I came up to this solution: I put a unbound combobox in the Team with all the possible musical preference. This combobox is use, with VBA code, to determine the RowSource of the combo box that display "people". It just change the SQL statament.

Is usefull to choose and it´s no "bound" in the relational model of my tables, so, I can´t change it any time.

The little akward thing is this: if you left the combobox with the criteria "rock" and then move to a record with a team that has "classical", is left blank (but they are there). I tried to fix it with vba code in the events "load", etc, but it was fixed partially... so I decided leave the way it is now. I hope, when I learn to work better in this utiliy, I will overcome this kind of issue with a different aproach.

So, that´s sort of it. Eitherway thank you Simon