ACCESS FEED: Re: [MS_AccessPros] Sorting a form?

Jumat, 30 Januari 2015

Re: [MS_AccessPros] Sorting a form?

Hi Bill,

> "I see the event ID 50 times"

on the property sheet for that control:

Hide Duplicates = Yes

(near the bottom of the Format tab)

Warm Regards,

Crystal

Access QAT (Quick Access Toolbar): Alignment Icons (cc)
https://www.youtube.com/watch?v=ZF4pb7e1QSc
- icons you can add to the Quick Access Toolbar to be faster at designing forms and reports

~ have an awesome day ~

On Friday, January 30, 2015 12:52 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

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

http://www.viescas.com/

(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 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

http://www.viescas.com/

(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 1^st 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

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

http://www.viescas.com/

(Paris, France)

On Jan 27, 2015, at 9:25 PM, 'Bill Singer' Bill.Singer@at-group.net <mailto:Bill.Singer@at-group.net> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:

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