query for current winning streak

Hi I am trying to run a query to show what a player's current and
winning streak is. I was told to use this query to start. This gives
the player and the last game he lost:

SELECT playerID, MAX(gameID) AS mgame
FROM  myData
WHERE NOT isWin
GROUP BY playerID
----------------------------------------
saved as qlatestLost

Then use the below query to give the number of games after the last
loss

-------------------------------
SELECT playerID,
    COUNT(qlatestLost.PlayerID)  AS actualWinStreak
FROM myDataLEFT JOIN qlatestLost
    ON myData.playerID = qlatestLost.playerID
    AND myData.gameID > qlatestLost.mgame


Problem is this query is not working. When i run it as is I get an
error stating that playerID could come from more than one table. So
then i changed it to

SELECT myData.playerID,
    COUNT(qlatestLost.PlayerID)  AS actualWinStreak
FROM myDataLEFT JOIN qlatestLost
    ON myData.playerID = qlatestLost.playerID
    AND myData.gameID > qlatestLost.mgame

and got an error message stating that i tried to execute a query that
does not include the specified expression 'playerID' as part of an
aggregate function. so i changed it to

SELECT qlatestLost.PlayerID,
    COUNT(qlatestLost.PlayerID)  AS actualWinStreak
FROM myDataLEFT JOIN qlatestLost
    ON myData.playerID = qlatestLost.playerID
    AND myData.gameID > qlatestLost.mgame

and got the same error.

Can someone tell me what the problem is?

Thanks



0
pat67
1/8/2010 2:01:00 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
718 Views

Similar Articles

[PageSpeed] 5

>>got an error message stating that i tried to execute a query that does not 
include the specified expression 'playerID' as part of an aggregate function.
What this means is that you have a 'Totals' query but did not GROUP BY the 
'playerID'.
In a totals query all fields in the output must be either GROUP BY or some 
function such as - First, Last, Min, Max, Sum, Count, Avg, etc.

-- 
Build a little, test a little.


"pat67" wrote:

> Hi I am trying to run a query to show what a player's current and
> winning streak is. I was told to use this query to start. This gives
> the player and the last game he lost:
> 
> SELECT playerID, MAX(gameID) AS mgame
> FROM  myData
> WHERE NOT isWin
> GROUP BY playerID
> ----------------------------------------
> saved as qlatestLost
> 
> Then use the below query to give the number of games after the last
> loss
> 
> -------------------------------
> SELECT playerID,
>     COUNT(qlatestLost.PlayerID)  AS actualWinStreak
> FROM myDataLEFT JOIN qlatestLost
>     ON myData.playerID = qlatestLost.playerID
>     AND myData.gameID > qlatestLost.mgame
> 
> 
> Problem is this query is not working. When i run it as is I get an
> error stating that playerID could come from more than one table. So
> then i changed it to
> 
> SELECT myData.playerID,
>     COUNT(qlatestLost.PlayerID)  AS actualWinStreak
> FROM myDataLEFT JOIN qlatestLost
>     ON myData.playerID = qlatestLost.playerID
>     AND myData.gameID > qlatestLost.mgame
> 
> and got an error message stating that i tried to execute a query that
> does not include the specified expression 'playerID' as part of an
> aggregate function. so i changed it to
> 
> SELECT qlatestLost.PlayerID,
>     COUNT(qlatestLost.PlayerID)  AS actualWinStreak
> FROM myDataLEFT JOIN qlatestLost
>     ON myData.playerID = qlatestLost.playerID
>     AND myData.gameID > qlatestLost.mgame
> 
> and got the same error.
> 
> Can someone tell me what the problem is?
> 
> Thanks
> 
> 
> 
> .
> 
0
Utf
1/8/2010 4:30:01 PM
On Jan 8, 11:30=A0am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> >>got an error message stating that i tried to execute a query that does =
not
>
> include the specified expression 'playerID' as part of an aggregate funct=
ion.
> What this means is that you have a 'Totals' query but did not GROUP BY th=
e
> 'playerID'.
> In a totals query all fields in the output must be either GROUP BY or som=
e
> function such as - First, Last, Min, Max, Sum, Count, Avg, etc.
>
> --
> Build a little, test a little.
>
>
>
> "pat67" wrote:
> > Hi I am trying to run a query to show what a player's current and
> > winning streak is. I was told to use this query to start. This gives
> > the player and the last game he lost:
>
> > SELECT playerID, MAX(gameID) AS mgame
> > FROM =A0myData
> > WHERE NOT isWin
> > GROUP BY playerID
> > ----------------------------------------
> > saved as qlatestLost
>
> > Then use the below query to give the number of games after the last
> > loss
>
> > -------------------------------
> > SELECT playerID,
> > =A0 =A0 COUNT(qlatestLost.PlayerID) =A0AS actualWinStreak
> > FROM myDataLEFT JOIN qlatestLost
> > =A0 =A0 ON myData.playerID =3D qlatestLost.playerID
> > =A0 =A0 AND myData.gameID > qlatestLost.mgame
>
> > Problem is this query is not working. When i run it as is I get an
> > error stating that playerID could come from more than one table. So
> > then i changed it to
>
> > SELECT myData.playerID,
> > =A0 =A0 COUNT(qlatestLost.PlayerID) =A0AS actualWinStreak
> > FROM myDataLEFT JOIN qlatestLost
> > =A0 =A0 ON myData.playerID =3D qlatestLost.playerID
> > =A0 =A0 AND myData.gameID > qlatestLost.mgame
>
> > and got an error message stating that i tried to execute a query that
> > does not include the specified expression 'playerID' as part of an
> > aggregate function. so i changed it to
>
> > SELECT qlatestLost.PlayerID,
> > =A0 =A0 COUNT(qlatestLost.PlayerID) =A0AS actualWinStreak
> > FROM myDataLEFT JOIN qlatestLost
> > =A0 =A0 ON myData.playerID =3D qlatestLost.playerID
> > =A0 =A0 AND myData.gameID > qlatestLost.mgame
>
> > and got the same error.
>
> > Can someone tell me what the problem is?
>
> > Thanks
>
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks as soon as I put it as group by it worked.
0
pat67
1/8/2010 4:34:13 PM
On Jan 8, 11:30=A0am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> >>got an error message stating that i tried to execute a query that does =
not
>
> include the specified expression 'playerID' as part of an aggregate funct=
ion.
> What this means is that you have a 'Totals' query but did not GROUP BY th=
e
> 'playerID'.
> In a totals query all fields in the output must be either GROUP BY or som=
e
> function such as - First, Last, Min, Max, Sum, Count, Avg, etc.
>
> --
> Build a little, test a little.
>
>
>
> "pat67" wrote:
> > Hi I am trying to run a query to show what a player's current and
> > winning streak is. I was told to use this query to start. This gives
> > the player and the last game he lost:
>
> > SELECT playerID, MAX(gameID) AS mgame
> > FROM =A0myData
> > WHERE NOT isWin
> > GROUP BY playerID
> > ----------------------------------------
> > saved as qlatestLost
>
> > Then use the below query to give the number of games after the last
> > loss
>
> > -------------------------------
> > SELECT playerID,
> > =A0 =A0 COUNT(qlatestLost.PlayerID) =A0AS actualWinStreak
> > FROM myDataLEFT JOIN qlatestLost
> > =A0 =A0 ON myData.playerID =3D qlatestLost.playerID
> > =A0 =A0 AND myData.gameID > qlatestLost.mgame
>
> > Problem is this query is not working. When i run it as is I get an
> > error stating that playerID could come from more than one table. So
> > then i changed it to
>
> > SELECT myData.playerID,
> > =A0 =A0 COUNT(qlatestLost.PlayerID) =A0AS actualWinStreak
> > FROM myDataLEFT JOIN qlatestLost
> > =A0 =A0 ON myData.playerID =3D qlatestLost.playerID
> > =A0 =A0 AND myData.gameID > qlatestLost.mgame
>
> > and got an error message stating that i tried to execute a query that
> > does not include the specified expression 'playerID' as part of an
> > aggregate function. so i changed it to
>
> > SELECT qlatestLost.PlayerID,
> > =A0 =A0 COUNT(qlatestLost.PlayerID) =A0AS actualWinStreak
> > FROM myDataLEFT JOIN qlatestLost
> > =A0 =A0 ON myData.playerID =3D qlatestLost.playerID
> > =A0 =A0 AND myData.gameID > qlatestLost.mgame
>
> > and got the same error.
>
> > Can someone tell me what the problem is?
>
> > Thanks
>
> > .- Hide quoted text -
>
> - Show quoted text -

One quick question. First off I can't believe I am a dummy and didn't
see what was wrong. Anyway, here is my question. Along with the win
streak query I have a loss streak query as follows:

SELECT myData.PlayerID, Count(qlatestWon.PlayerID)
AS currentLossStreak
FROM myData LEFT JOIN qlatestWon
ON myData.PlayerID =3D qlatestWon.PlayerID
AND myData.gameID > qlatestWon.LastWonGame
GROUP BY myData.PlayerID;

The question is there are 3 players with 0 wins so when I run the last
won query

SELECT PlayerID, MAX(GameID) AS LastWonGame
FROM myData
WHERE  isWin
GROUP BY PlayerID;

No gameID comes up because they haven't won any. Do I need to join the
rosters table and insert an iif statement so that the gameID for those
players shows as 0 so when I run the streak query it looks for >0
gameID?

it isn't a big big deal but i would like to know.

Thanks
0
pat67
1/8/2010 5:02:12 PM
I'll let someone else analyze this.

-- 
Build a little, test a little.


"pat67" wrote:

> On Jan 8, 11:30 am, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
> > >>got an error message stating that i tried to execute a query that does not
> >
> > include the specified expression 'playerID' as part of an aggregate function.
> > What this means is that you have a 'Totals' query but did not GROUP BY the
> > 'playerID'.
> > In a totals query all fields in the output must be either GROUP BY or some
> > function such as - First, Last, Min, Max, Sum, Count, Avg, etc.
> >
> > --
> > Build a little, test a little.
> >
> >
> >
> > "pat67" wrote:
> > > Hi I am trying to run a query to show what a player's current and
> > > winning streak is. I was told to use this query to start. This gives
> > > the player and the last game he lost:
> >
> > > SELECT playerID, MAX(gameID) AS mgame
> > > FROM  myData
> > > WHERE NOT isWin
> > > GROUP BY playerID
> > > ----------------------------------------
> > > saved as qlatestLost
> >
> > > Then use the below query to give the number of games after the last
> > > loss
> >
> > > -------------------------------
> > > SELECT playerID,
> > >     COUNT(qlatestLost.PlayerID)  AS actualWinStreak
> > > FROM myDataLEFT JOIN qlatestLost
> > >     ON myData.playerID = qlatestLost.playerID
> > >     AND myData.gameID > qlatestLost.mgame
> >
> > > Problem is this query is not working. When i run it as is I get an
> > > error stating that playerID could come from more than one table. So
> > > then i changed it to
> >
> > > SELECT myData.playerID,
> > >     COUNT(qlatestLost.PlayerID)  AS actualWinStreak
> > > FROM myDataLEFT JOIN qlatestLost
> > >     ON myData.playerID = qlatestLost.playerID
> > >     AND myData.gameID > qlatestLost.mgame
> >
> > > and got an error message stating that i tried to execute a query that
> > > does not include the specified expression 'playerID' as part of an
> > > aggregate function. so i changed it to
> >
> > > SELECT qlatestLost.PlayerID,
> > >     COUNT(qlatestLost.PlayerID)  AS actualWinStreak
> > > FROM myDataLEFT JOIN qlatestLost
> > >     ON myData.playerID = qlatestLost.playerID
> > >     AND myData.gameID > qlatestLost.mgame
> >
> > > and got the same error.
> >
> > > Can someone tell me what the problem is?
> >
> > > Thanks
> >
> > > .- Hide quoted text -
> >
> > - Show quoted text -
> 
> One quick question. First off I can't believe I am a dummy and didn't
> see what was wrong. Anyway, here is my question. Along with the win
> streak query I have a loss streak query as follows:
> 
> SELECT myData.PlayerID, Count(qlatestWon.PlayerID)
> AS currentLossStreak
> FROM myData LEFT JOIN qlatestWon
> ON myData.PlayerID = qlatestWon.PlayerID
> AND myData.gameID > qlatestWon.LastWonGame
> GROUP BY myData.PlayerID;
> 
> The question is there are 3 players with 0 wins so when I run the last
> won query
> 
> SELECT PlayerID, MAX(GameID) AS LastWonGame
> FROM myData
> WHERE  isWin
> GROUP BY PlayerID;
> 
> No gameID comes up because they haven't won any. Do I need to join the
> rosters table and insert an iif statement so that the gameID for those
> players shows as 0 so when I run the streak query it looks for >0
> gameID?
> 
> it isn't a big big deal but i would like to know.
> 
> Thanks
> .
> 
0
Utf
1/8/2010 6:27:01 PM
Reply:

Similar Artilces:

Query Problem 03-27-10
Hi, I have 2 tables, tbBaby and tbVaccineInjection. tbBaby stores the babies' personal data like Name, BirthDate etc., tbVaccine stores VaccineName, DoseNumber (1st, 2nd, 3rd, etc.) and InjectionDate, etc. These 2 tables are one to many in relation, because each baby may have several vaccinations on different dates. I'd like to build a query to retrieve babies with their most recent injection date, i.e. only one record for each baby with latest injection date. my query is as follow SELECT tbBaby.*, tbVaccine.* FROM tbBaby INNER JOIN tbVaccine ON tbBaby.ID=tbVaccine.B...

Append query bloat
I have an access database where I use an append query quite often. I will use this same query over and over by changing the table I'm appending from, then I save the query. During the save process, the database bloats from ~50KB to ~150KB. Our company recently upgraded to 2007 and that is when I noticed the problem. Is there a way to stop this from happening? I can manage it by compacting and repairing after each save, but recently I added 4 tables in one day, so this was a pain. Thanks Don't even worry about it. An Access file can be up to 2 GB in size. 150 KB ...

Does SQL Run Behind Queries?
Access 2007 When I run a query does Access, "go away" and run the SQL for what I see in the GUI? If the answer is, "yes" is there anything that I can switch on to see the SQL that it is running? What I would like to do, if it is possible, is to see the query in the GUI and, at the same time, see the SQL that is being used to select the data from the database; that way I can understand things (exactly what is happening) more clearly and more quickly. TIA for any replies. trip_to_tokyo wrote: > Access 2007 > > When I run a query does...

Database Query #4
I have created a data table in a worksheet and named it “data”. Then I created a separate worksheet, in the same workbook, and created a database query of the data table, by using Data…Import External Data…New Database Query…Excel Files* and defining the database name by browsing to the same workbook. The query worked fine. However, when I moved the workbook (which included the data source) to a different folder and tried to refresh the database query, I got the following error message: “[Microsoft] [ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘data’...

DNS clients Does not query the secondary DNS
Hi, I have 2 w2003 Dcs - both DNS servers Primary and secondary. I have Clients running XP static Ip and in both DNS servers IP is configured as Primary and Secondary DNS. My Primary DNS server went down, all clients are not quering the secondary! using NSLOOKUP it showed that clients are still querying the primary with a tomeout error !! all name resoultiond stoped in the organization! any idea? tx NsLookup does not automatically fall through to alternate DNS servers, it is not a good way to test server redundancy. If you ping a host, which will use the DNS Cl...

Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

How to use colmn headings in paramter query
Sorry if this has been asked before, but I am not able to locate it if it has. But here is my delima: I need to be able to select the salesperson sales based on the month (1-12) from table 1. I* can create a parameter query, but am not sure hwo to query onlya certain month. table1 ID salesperson 1 2 3 4 5 6 7 8 9 10 11 12 1 joe 12 0 0 2 12 0 1 1 5 7 8 10 2 steve 2 5 6 18 0 0 2 3 4 11 12 1 select * from table1 where salesperson =joe and .... that is where I get stuck. I need to be able to select a column he...

Mutual Funds Winning Guide
Free Gadget Fund Teller to let you know funds' real-time price before you buy or sell. http://fundteller.somee.com/ Keep winning. ...

subtotal for due bills of current month
Is it possible to have a total for the bills due in the current month with money 2006 deluxe? Thanks is advance. Gip ...

Query Does Not Count "2" Records
Good Evening Everyone... I have a bit of a logical puzzle that I need to solve. I have various queries which have various criteria for them. One query involves retirees or spouses being under the age of 65 and the other involves retirees or spouses being over the age of 65. Both the retiree and spouse are listen in the same records and on one line of the master table and queries. The situation that I am running into is that I can have a retiree who is over 65 and a spouse under 65 and the record comes up twice in the two different queries b/c it meets the criteria, which is perfect. What...

Extracting invoice data from GP 9 using a SQL query (ODBC)
I'm working to extract invoice detail information from a client's GP 9 MSSQL database, with the goal of being able to mock up their invoices in external systems. Just to keep things interesting, they aren't using GP to it's maximum ability - quotes are generated from another software package, and when converted to a sale, minimal data is entered into GP and processed (such as shipping address, job name, job number, PO number, and occasionally a line it of what appears to be free-form text. I have hard copies of several invoices, various web resources, and access to t...

Web Query Help...
If I try and use a web query with the following site http://www.sportsline.com/mlb/stats/playersort/regularseason/yearly/MLB/P I get a message that says the web query returned no data. I click on the arrow right by player and it highlights all the players names and their stats. Anyone know why it doesn't import the data into excel? Is there too much data? Thanks Works fine for me, although one have to do some editing since it imports some trash as well (I would import it, then select the table itself from the import and copy and paste into a new sheet, then import the next 50 and...

Pasteing In text into a current text box
When I cut text from a website and attempt to paste it into a current text box, Publisher 2003 defaults to create a new text box instead of inserting it into the preexisting one. How can I fixt this? Firstly you do not say which version of Publisher you are using. Secondly when you copy a webpage you are copying HTML. Use menu EDIT > Paste Special > Unformatted Text or paste into Notepad which will remove all the formatting and then re-copy and paste into Publisher. "��MS-Publisher��" wrote: > Firstly you do not say which version of Publisher you are using. ========...

SQL report as dashboard to filter for Current user cannot user CRMAF_ #2
First off I cannot use the default filters because the report is being generated for a dashboard and not a CRM Report, however it is being created via an SRS Report. I simply need a way to filter the report for the current user only, I found this query which gives the system user but this will not match up to the Account owner because of its format. select fullname from FilteredSystemUser where domainname = SUSER_SNAME() Gives: CRM Admin I need, Admin, CRM ...

Fetch XML to Query Expression
I'm trying to convert the following Fetch XML statement to a Query Expression to post using the RetrieveMultiple webservice: <fetch mapping="logical"> <entity name="role"> <attribute name="name" /> <link-entity name="systemuserroles" from="roleid" to="roleid"> <filter> <condition attribute="systemuserid" operator="eq-userid" /> </filter> </link-entity> </entity> </fetch> I haven't been able to find an documenta...

Query Output
Help, I am working with make table queries. I have a A make table query and a B make table query each with various information that the query has preformed. I am trying to get the following output into a text file. A Make Table Information B Make Table Information A Make Table Information B Make Table Information etc. There are over 300 items in each table. Each A Corresponds with a B. I need to keep the 2 records separate for where i have to upload them. I tried having a common identifier for the two tables and creating a big query with all the information on, but i ...

Win CE 6.0 and Bluetooth
Hi All, i tried to enable the Bluetooth function on a Windows Embedded CE 6.0 R2 (i. MX-based device). For connecting an USB-BT dongle I activated these SYSGENs: -SYSGEN_BTH_USB_ONLY -SYSGEN_BTH_PAN I also tried to additionally add the SYSGEN_BTH, but this seems to make no difference... After a clean SYSGEN I copied the image to the CE-system, started the system, put the USB-dongle (Anycom USB-500 or Delock EDR) in the port and oppend the BT-manager and pressed 'scan'. There were some BT devices found. So the 'basics' of the BT connection should work. But if i...

Query-Based E-mail Groups
I have created query-based email group that contains 1,600 users. When I send an email to that group, about 350 users don't get the message. I as the owner of the group get an undeliverable stating: The e-mail system limits the number of recipients that can be addressed in a single message. Send the message multiple times to fewer recipients. For all 350 users. So I check my "Message Delivery Properties" in ESM. I have my "Recipient Limits" set to 2,000. I double checked the query-based email group to make sure it was under 2,000 recipients and ...

Suggestions for table or query to track movement
I have a table for employees that includes the usual description fields for employees. This is one of my master tables with the employee ID being the primary key. Then I have another table that stores all employee hours that I add on to monthly via an import from Excel (emplID is foreign key). I want to keep track of all of the employees that are new or terminate. I’d like Access to recognize if an employee is missing or new and later be able to create a report for recent new hires or terminations. I may be expecting too much and if so please give me your opinion on this. ...

XP & Vista & Win 7 My Doc Directory Structure.
Hi, I'm developing using Access XP Office on Windows 7. My users are running Access XP and Access 2007 on Widows XP, Vista, and Windows 7. I not quite in which forum this question belongs, but since it came up in a form, I'm asking the question in this forum. I have "blob" type files (.tiff, .jpg, etc.) that were stored in different sub-directories under C:\Documents and Settings\<User Name>\My Documents\....." Rather than store the file in the database, I store the full path and file name in my database. As you know MS, "in their inf...

Update current record of recordset
I have a simple recordset (select * from stores). Is there a way to update the current record I am processing? example: update table1 set field1 = "ABC" where ??CURRENT RECORD OF RECORDSET?? Do you have an easy example? If you are using bound forms, this is automatic, just type "ABC" into the proper textbox. If you need to update another recordset, set the recordset to pull only the 1 record: "Select * From Stores Where ID = '" & Me.PrimaryKeyValue & "'" for a text Primary Key, or: "Select * From Stores Where ID = " &am...

Calculating the Difference in a Query
I need to calculate the difference between two fields in a query. The query is called “qryRemote” and within the query are the fields “10-8Time” and “10- 97Time”. The data entered into these fields are in the format “0000” through to “2400”. I need to subtract the “10-8Time from the “10-97Time” and then sum the results and display the result in a report. For example, after the query is run the results are: 10-8Time 10-97Time --------------------------------- 1307 1331 1858 1909 2018 2023 First the difference is determined: 10-8Time is subtracted form t...

Outlook 2000 Calendar Text Entry query. Help!
Husband and I are both retired and have never used Outlook before, even though it's installed on our home computer. Now that we have more time on our hands, we're using the Outlook monthly calendar: we print out a "blank" month at a time, pop the page into a ring bind folder, and then write (by hand) notes on the various days. We would like to be able to do this on-screen though, rather than with a pen! We're planning a day-by-day schedule for a holiday next February but we cannot work out how to "write" timetable notes into the squares for each separate...

Moving queries
hello! I need help! I am trying to copy a long list of queries from one access database to another. I tried "get external data", but it brought the actual data from the old database with the queries. Export will only allow me to move one query at a time... How do I move a whole list of queries? Thank you! Go to the other database and import them. "Amanda P" <Amanda P@discussions.microsoft.com> wrote in message news:74881BCB-09D3-44A6-9A06-198EB1486E69@microsoft.com... > hello! > I need help! > I am trying to copy a long list of queries from one ac...

Offset Formula Query
I am using an offset formula to bring back data. I am trying to bring back the cell next to the one that has a label that matches the file name e.g. the file name is Place and then column a contains a list of places and column b contains text about those places. I am using the following formula: =OFFSET(Data!A15,MATCH(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5),Data!$A$15:Data!$A$37,0),1) Where Data! refers to the sheet which contains the data and t...