Jumat, 30 Januari 2015

Re: [MS_AccessPros] Sorting a form?

 

Bill-


No, the Hide Duplicates property is not available on a form.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jan 30, 2015, at 8:04 PM, 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
To answer a few of your questions, with one more question.
 
The HOME and AWAY designations (values) are input when the original schedule is down loaded so I do not have to put it in here.
Yes, I have a form put together that uses the combo boxes for the  Won/Lost.
 
You said… You can do what you want in a Report by setting the Hide Duplicates property of the FscID to Yes.
 
 
I looked for this property on a continuous FORM, which is what I am working on.  I could not find it.   Did I miss it or is this not available for a from/subform?
 
I will attempt to input the code you sent along and see how it goes.
 
Thanks for the help.
Bill
 
 
  
 
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, January 29, 2015 3:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Sorting a form?
 
 
Bill-
 
You can do what you want in a Report by setting the Hide Duplicates property of the FscID to Yes.
 
On a form, why aren't you using combo boxes for Home / Visitor and Won / Lost?
 
Row Source Type: Value List
Row Source: 1;"Home";2;"Visitor"
Column Count: 2
Bound Columns: 1
Column Widths: 0";1"
 
.. or use a lookup to the related Foreign Key tables.
 
You will have to enter Won / Lost for the first of the two teams, but you could write a bit of code to set it automatically for the second team:
 
Private Sub gtePoints_AfterUpdate()
Dim varScore As Variant
 
    ' See if we can find a score for the other team
    varScore = DLookup("gtePoints", "t_EventParticipants", _
        "gteFscID = " & Me.gteFscID & " AND gteTnaID <> " & Me.gteTnaID)
    ' If we got a score
    If Not IsNull(varScore) Then
        ' If the other score is less,
        If varScore < Me.gtePoints Then
            ' .. then this team won
            Me.gteResID = 1
        ' If the other score is more,
        ElseIf varScore > Me.gtePoints Then
            ' .. then this team lost
            Me.gteResID = 2
        ' Otherwise, it was a tie
        Else
            Me.gteResID = 3
        End If
    End If
End Sub
 
You could do something similar in the AfterUpdate of the gteDesID to set Home / Visitor for the second team.
   
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)
 

 

 
On Jan 29, 2015, at 9:28 PM, 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
 
John, I will try to explain this.  I am tracking scores, win, losses, and standings in Basketball league with 650 kids and about 70 teams.  Hopefully you are a sports fan.
 
The record source of my report is a query  (q_EventScoreInputMultiGame)
 
It pulls the event number from the 1st table below (t_facilitySchedule) and the participant information from the second table below (t_EventParticipants)
 
 
My events are stored in a table called
t_facilitySchedule
 
The fields are
fscfscID , which is the PK, which is also the Event number. I will eventually hide this.
fscFacID, which is a FK to the Facility Name
There are 4 more fields that indicates the Sport, Date, Start time, and  Stop Time.
 
I have a second table that lists event participants.  As you suggested the participants are in a separate table.
The table name is
t_EventParticipants
 
The fields are
gteGteID, which is the PK
gteFscID, which is the FK that related to the table above.
gteTnaID, which is FK to the Team Name table
gteDesID, which is a FK which designates HOME team or VISITOR team.  1 or 2.
gtePoints, which is how many points a team scored in an event.
gteResID, which is a FK to a table which indicates if the team won the event, 1=Win, 2=Loss, 3=Tie.  I have to put this in manually since I can't figure out how to do it manually.
 
 
So the report looks like this, it is hard to pick out the games.
 
61255 ,   Lakers , 1 ,   21 , 2   (This reads- In event 61255 the Lakers were the Home team and scored 21, they lost)
61255 ,   Bulls     , 2 ,   32 , 1   (This reads- In event 61255 the Bulls were the Visitor team and scored 32, they won)
61256,   Cavs     , 1 ,     22, 1
61256,   Spurs   , 2 ,    19, 2
61257,   Heat    , 1  ,    27, 1
61257,  Bucks   , 2 ,    24, 2
 
 
I was hoping to have it look like, it is easier to read.
 
61255 ,   Lakers , 1 ,   21 , 2   
                 Bulls     , 2 ,   32 , 1 
 
61256,   Cavs     , 1 ,     22, 1
                Spurs   , 2 ,    19, 2
 
61257,   Heat    , 1  ,    27, 1
                Bucks   , 2 ,    24, 2
 
 
The point is that I am trying to find an efficient way to input the scores that is not prone to error.   I have to put in the score and then the 1 or 2 to determine a win or loss.
 
Ideally I would like the score inputting all on one line like below,  but when I do that I get a record that is not editable. 
 
61257    Heat  ,  27  1  ,  Bucks ,  24 ,  2
 
It would also be great if I could have the program determine who was the winner and input a 1 or 2  based on the score that I put in.  Putting in a 1 or 2 was the only way I could calculate wins and losses and team standings for the end of the year playoffs.   So now I just added another question.
 
Clear as Mud
 
Thanks for any tips you can provide.
 
Bill
MN
 
 
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, January 27, 2015 2:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Sorting a form?
 
 
 
Bill-
 
What is the Record Source of your form?  If you want to see only events, don't include participant info.  I assume events have their own table.  If not, then perhaps that's your problem.
 
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
 
 
 
 
 
I am wondering if there is a way to GROUP records on a form like I can GROUP records in a report.   I have 25 events and 50 participants.   I am using a continuous form so that I can see all the events and participants.
I would like to see only 25 event IDs ( 1 for each event)  but now, since the event ID is on the continuous form I see the event ID 50 times, once with each participant.
Any thought?
Bill
MN
 
 
 
 

__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (6)

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)