Help with query and form

[hparker]

For a golf handicap database, I have a query that results in the most recent
20 golf scores. I use this to display these rounds on a form from the most
recent to include the last 20 rounds. I have another query based on the
first query that results in the 10 best scores of those last 20 rounds. How
do I display a single result (the average of the 10 best scores) on the form
with the 20 most recent rounds? The form's record source is the first query.
I can make this happen if I use the second query as the record source, but
it only displays the best 10 rounds instead of the last 20 rounds.

Thanks in advance.

 

[BruceS]

hparker,

1) Have both queries produce identical fields with identical field names.
2) In design mode, set the datasource of the form to the "last 20" query.
3) Add a "Best 10" button to the form with the following in the OnClick
event:

Private Sub cmdBest10_Click()
If cmdBest10.Caption = "Best 10" Then
Me.RecordSource = "Best10Qry" 'or whatever you call it
cmdBest10.Caption = "Last 20"
Else
Me.RecordSource = "Last20Qry" 'or whatever you call it
Me.cmdBest10.Caption = "Best 10"
End If
Me.Requery
End Sub

When the button is clicked, it will toggle the record source and change the
button.

HTH,
Bruce

 

[hparker]

Thanks, that helped, however I need to have the "last 20" results to display
all the time. I only need to display the average of the best 10 on the same
form...I'm still a little stuck.

 

[johngo]

Thanks, that helped, however I need to have the "last 20" results to display
all the time. I only need to display the average of the best 10 on the same
form...I'm still a little stuck.

You could use the DAvg domain aggregate function in a textbox.

Or you could put a subform in with the recordsource for the 10 best
rounds and have a textbox in the subform footer with the Avg function
applied to the score.

Or you could have a combo or list box with the rowsource for the
average of the 10 best rounds and show the result in a textbox with
formula, =[combo].[Column](0).

Or you could have 3 saved queries, qryLast20, qry10bestOfLast20 and
qtotAvgOfTenBest, with qryLast20 nested in qry10bestOfLast20 and
qry10bestOfLast20 nested in qtotAvgOfTenBest. Use qryLast20 and
qtotAvgOfTenBest, unlinked, for the form recordsource.
qtotAvgOfTenBest will have only one row so you don't need a link,
(could check out cartesion product in help for query educ.). Make sure
the avg field in qtotAvgOfTenBest is in the recordsource query grid.

There is a possible subquery solution too.


Bon Appetit

 

[BruceS]

Easy! Two different approaches, depending upon how much screen space you
have available on the main form. (This assumes your main form is "Single
Form", not "Continuous"):

1) Use two subforms, one for Last20 and one for Best10. Each subform would
have its specific query as its recordsource and a parent/child link to the
selected record on the main form.

2) If you don't have that much screen space, or your "main" for is a
Continuous form, use a button on the main form. When the button is clicked,
open a second form (you can make it Dialog or simply pop-up, depending upon
which is best for you) that has Best 10 as its recordsource and is filtered
by the PlayerID. It would then be visible only when you need to see it. The
open form statement would look something like this:

DoCmd.OpenForm "Best10", , , "[PlayerID]=" & Me![PlayerID], , acDialog

Bruce

 

[hparker]

Thanks to all for your help...I used the subform method. I'm also trying to
figure out how to do it with an array, and how to pass the info from the
query into an array in VB.

 

[BruceS]

You're welcome! If it was helpful, please "rate" the post accordingly.

Transferring query results to array: (Assumes query named "InfoGet" that
has 3 fields, FldA, FldB and FldC)

Option Compare Database
Option Explicit
Option Base 1
....
Dim MyAry() As Variant ' Declare dynamic array.
....

Private Sub Load_Array()

Dim rst As Recordset
Dim x As Long
Dim y As Long

Set rst = CurrentDb.OpenRecordset("InfoGet")
y = rst.RecordCount
If y = 0 Then
MsgBox ("Nothing to do!")
Else
ReDim myAry(y, 3)
With rst
.MoveLast
.MoveFirst
For x = 1 To y
myAry(y, 1) = ![FldA]
myAry(y, 2) = ![FldB]
myAry(y, 3) = ![FldC]
If x < y Then .MoveNext
Next x
End With
End If
rst.Close
Set rst = Nothing
End Sub

HTH,
Bruce