I have a database with a table named Fielding that contains (among
others) the following records and fields:
playerID = unique with records for each change in POS, year, team
POS = P, C, 1B, 2B, 3B, SS, LF, CF, RF, OF, DH
Games = number of games played at each POS
For example, using this data:
playerID yearID stint teamID POS Games
-----------------------------------------------------
lakeed01 1943 1 BOS SS 63
lakeed01 1944 1 BOS 2B 3
lakeed01 1944 1 BOS 3B 1
lakeed01 1944 1 BOS P 6
lakeed01 1944 1 BOS SS 41
lakeed01 1945 1 BOS 2B 1
lakeed01 1945 1 BOS SS 130
lakeed01 1946 1 DET SS 155
lakeed01 1947 1 DET SS 158
lakeed01 1948 1 DET 2B 45
I need an output like this for each playerID:
playerID =P <>P
---------------------------
lakeed01 6 597
Can this be done straightforward ly? Thanks,
Cliff
2  1253 
You can use a crosstab query like this:
TRANSFORM Sum(Fielding.Ga mes) AS SumOfGames
SELECT Fielding.Player ID
FROM Fielding
GROUP BY Fielding.Player ID
PIVOT [POS]="P";
HTH
- Turtle
"Cliff" <ba*********@ho tmail.com> wrote in message
news:a5******** *************** ***@posting.goo gle.com... I have a database with a table named Fielding that contains (among
others) the following records and fields:
playerID = unique with records for each change in POS, year, team
POS = P, C, 1B, 2B, 3B, SS, LF, CF, RF, OF, DH
Games = number of games played at each POS
For example, using this data:
playerID yearID stint teamID POS Games
-----------------------------------------------------
lakeed01 1943 1 BOS SS 63
lakeed01 1944 1 BOS 2B 3
lakeed01 1944 1 BOS 3B 1
lakeed01 1944 1 BOS P 6
lakeed01 1944 1 BOS SS 41
lakeed01 1945 1 BOS 2B 1
lakeed01 1945 1 BOS SS 130
lakeed01 1946 1 DET SS 155
lakeed01 1947 1 DET SS 158
lakeed01 1948 1 DET 2B 45
I need an output like this for each playerID:
playerID =P <>P
---------------------------
lakeed01 6 597
Can this be done straightforward ly? Thanks,
Cliff
"MacDermott " <ma********@nos pam.com> wrote in message news:<gS******* **********@news read3.news.atl. earthlink.net>. .. You can use a crosstab query like this:
TRANSFORM Sum(Fielding.Ga mes) AS SumOfGames
SELECT Fielding.Player ID
FROM Fielding
GROUP BY Fielding.Player ID
PIVOT [POS]="P";
Thanks, Turtle. That got me on track. Didn't know about pivot tables and Access.
Cliff This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: David |
last post by:
Consider this SQL Query:
-----------------------------------------------------------------
SELECT c.CASE_NBR, DATEDIFF(d, c.CREATE_DT, GETDATE()) AS Age,
c.AFFD_RCVD, c.PRV_CRD_ISS, x.RegE, x.Type, x.Fraud,
c.CUST_FN + ' ' + c.CUST_LN AS CustFullName,
c.ATM_CKCD_NBR, x.TotalLoss, x.Queue, x.Status,
c.QUEUE AS Expr1, x.CHECK_ACT_NBR, c.CUST_LN, c.SSN,
c.CREATE_DT
FROM (
SELECT TOP 9999999 cl.CASE_NBR, cl.SSN, cl.CREATE_DT,
|
by: Todd D. Levy |
last post by:
I have a query based report where a number of the fields in each record
are Yes/No data fields.
I would like this report to behave as follows:
If ALL of the Yes/No data fields in a given record have a value of YES,
I would like that particular record to be suppressed (I.e. NOT appear in
the report).
and
|
by: Brian |
last post by:
Hello,
Basically, I'm running a query on a form's activation, and I'd like to
have the results of the query be placed into other fields on the same
form automatically.
Does anybody know how this can be done?
I've tried setting the default value of the text fields on the form to
be equal to ! using Access' expression
|
by: chuy08 |
last post by:
Basically I am using PHP 5.1.2 with Apache 2.0.5 on a FreeBSD 5.4 box
with Mysql 4.1.1 running.
I am attempting to write information to a Mysql table called Jabber. I
can connect successfully, query data, and write data pretty much
without problem. My problem is this.
I am trying to do some duplicate checking on this table so I query the
dbase with the following:
|
by: googleboy |
last post by:
I am trying to create a query that will perform a set of queries in a sequence. If the results of the first query are null, then I want the query to continue to the next query. In all, I believe the squence will be about four layers deep. As soon as a query is not null, then I want that result to be output as the solution.
I believe my issue is understanding how to store the results of the first query to then use it as part of a conditional...
| | |
by: dancole42 |
last post by:
So I have an invoicing database based on two main forms: Orders and
OrderLines. Orders has fields like:
OrderID
BillingMethod
OrderDate
CreditCard
CCExp
OrdSubTotal
ShippingCharge
|
by: mickey22 |
last post by:
Hi all,
I have a template function which is member of this class and I am calling this function in of my other member functions of the same class. But I am getting the warning as
see reference to function template instantiation 'T script_out::SUMMING<float>(T,std::string,T)' being compiled
1> with
1>
Summing is my template function and I am calling it in another member function of my class as
|
by: NewlytoSQL |
last post by:
Hi all, im fairly new to SQL and im stuck half way through a query,
im using DB2 here is what im tryng to do.
i have a query that brings back an item number , shelf req, sum of all orders columns, based on the shelf req column the item number column has more than one row for the same item number therefore my sum of all orders is summing them based on shelf req which i need, what i cant get is how to keep this info plus get the sum of all...
|
by: VbaNewbee |
last post by:
I have a form with a few filters. Once the user clicks "search button", the code first evaluates my filters, then shows the query results in a List Box" titled backschedule.
I have a few text boxes below the list box that add sum values in the query columns using Nz() Function.
Example
"openpoqtytotal = openpoqtytotal + Nz(Forms!!backschedule.Column(4, i), 0)"
My Problem , The Nz() Function works fine for summing up all values in the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
