Auto population

[Andyjones]

Hi Guys and girls. My database is a little to big to cut down to size but if somone can help with this issue can email the database to them so that they can look at it.



I want to create a team selection form, however to create this form i want the form to have all the fields in place. when i click select team, it should then run a query and search for the top 8 players for sunday and top 12 players for a saturday and populate them into the fields.

The fields wouild consit of captain, vice captain 5 more fields for the remaining players plus space to select the reserves.

These feilds where the players names are i want them to be editable so if i do not agree with what the query populates i can change them, like a drop down box etc. the query will sort from highest to losest of player points games played win percentage, but just need the player names o populate.

would any one be so kind in leaving there email address so that i can forward them a copy of the database for them to create this form for me.

Cheers.

[June7]

Would need to know more about your data structure. Are the players saved to 8 fields of one record or would there be 8 records? Either way will require code executing query.

Copy file, remove confidential data, run Compact & Repair, zip if still large (up to 2mb zip file allowed), attach to post. Otherwise, upload to fileshare site such as box.com and post link to the file.

[Andyjones]

link here http://www.box.com/s/6uyexcp3bj0p5glvn678

[June7]

Why did you use alias table name in qryRanking? This had me confused for a bit. I find it odd there are no dates of play. Is all this data from one weekend? There won't be other weekends? Why do you bother to join Player tables to Match tables in query? You already have the player names saved in the Match tables. The join is meaningless because there is no other information in the Player tables. If you will never have two or more players with same name, saving name will work, otherwise should save the player ID and then the table join has purpose. Also, saving name as one field First Last means can't sort by last name. If you want this capability need to save name parts in separate fields or if in one field as Last, First.

On what field do you want to base the criteria for top 8 and top 12? Sort the records on that field then use the TOP keyword in query. You can set this in the query property sheet. Switch to SQL View to change the number in the SQL statement.

If you want to save this selection as records in Match table need to run an INSERT SELECT or SELECT INTO FROM sql statement
Review:
http://www.w3schools.com/sql/sql_alter.asp
http://msdn.microsoft.com/en-us/library/ms188263.aspx

Then open form filtered to these new records.

[Andyjones]

I use the query ranking so that when the averages report is produced it places them in like a league table with pos 1 2 3 4 etc. I have set the tables up like they are as the database works.

What I want is from the query called player stats sunday/saturday or averages sunday/saturday is it for to select the top 8 players from the sunday and populate a form this form will be called team selection where a sunday team consists of 8 players, this form will be editable so that i can change the player selection if i don agree with what the query shows. and vice versa for a saturday. there were no dates as i did not put them in did not think they were needed. and the matches and data is from over a season between and april and septembe.

if you could be so kind in set this form up for me i would be gratefull.

[June7]

I will let you build the form and set it's RecordSource to the query. You will probably want it to apply filter to the records that don't yet have team assignment. These would be the new player records saved by the procedure to select TOP 8/12. The real trick is figuring out how to execute the procedure to select and save the records.