I need help converting SQL queries to MS Access queries for an Access database. I have an SQL query sequence to pull data from an SQL database and creates some named data fields in the process etc. I am creating a VB6 application/utility to pull this data out in MS Access. The database I'm using is the Lahman Baseball Database----it's the historical database of Major League Baseball statistics that's freely accessible to the public.
The 2012 version of the Lahman database (13.1mb ZIP) is attached to this post. This is the exact database I will be connecting to.
Here are my queries in SQL:
SELECT Batter = (Master.nameFirst + Master.nameLast), Team = (Batting.teamID) + CAST(Batting.yearID AS nvarchar),
Batting.POS, Batting.G, Batting.AB, Batting.R, Batting.H, Batting.[2B], Batting.[3B],
Batting.HR, Batting.RBI, Batting.TB, Batting.BB, Batting.SO, Batting.SB, Batting.CS,
OBP = ROUND(((SELECT CAST(Batting.HBP AS float) + CAST(Batting.BB AS float) + CAST(Batting.H AS float)) / (CAST(Batting.AB AS float) + CAST(Batting.BB AS float) + CAST(Batting.HBP AS float))),3),
SLG = ROUND(((SELECT CAST(Batting.H AS float) + CAST(Batting.[2B]*2 AS float) + CAST(Batting.[3B]*3 AS float) + CAST(Batting.
*4 AS float)) / (CAST(Batting.AB AS float))),3),
AVG = ROUND(((SELECT CAST(Batting.H AS float)) / (CAST(Batting.AB AS float))),3),
FPCT = 0
FROM Master INNER JOIN Batting ON Master.playerID = Batting.playerID
WHERE (((Master.nameLast)Like '%') AND ((Batting.yearID)=1951) AND ((Batting.teamID)='bro') AND AB > 99)
SELECT Batter = (Master.nameFirst + Master.nameLast), Team = (Pitching.teamID) + CAST(Pitching.yearID AS nvarchar), POS = '' + 'P', Pitching.BAOpp,
Pitching.ERA, Pitching.G, Pitching.GS, Pitching.CG, Pitching.SHO, Pitching.SV, Pitching.BFP,
Pitching.IPOuts, Pitching.H, Pitching.R, Pitching.ER, Pitching.HR, Pitching.HBP, Pitching.BB,
FROM Master INNER JOIN Pitching ON Master.playerID = Pitching.playerID
WHERE (((Master.nameLast)Like '%') AND ((Pitching.yearID)=1951) AND ((Pitching.teamID)='bro') AND IPouts > 99)
ORDER BY POS
SELECT *,FPCT = ROUND(((CAST(PO AS float) + CAST(A AS float)) / (CAST(PO AS float) + CAST(A AS float) + CAST(E AS float))),3)
SELECT Fielding.PlayerID, Master.nameFirst, Master.nameLast, Fielding.yearID,
Fielding.teamID, PO = SUM(Fielding.PO), A = SUM(Fielding.A), E = SUM(Fielding.E)
FROM Master INNER JOIN Fielding ON Master.playerID = Fielding.playerID
85) AND (nameLast LIKE '%') AND (Fielding.teamID)='kca' AND PO > 0
GROUP BY Fielding.playerID, nameFirst, nameLast, yearID, teamID) as t1
If it helps any, my ultimate goal is to fill a series of 20 data field columns in an MSFlexGrid by selecting a year and a team from a couple of dropdown lists.
MSFlexGrids would have the following column header names...
For batters/position player data:
Batter, Team, POS, G, AB, R, H, 2B, 3B, HR, RBI, TB, BB, SO, SB, CS, OBP, SLG, AVG, FPCT
For pitcher data:
Pitcher, Team, POS, AVG, ERA, G, GS, CG, SHO, SAV, PA, IP, H, R, ER, HR, HBP, BB, SO, FPCT
***** Any help is GREATLY appreciated..thanks *****