Query

[GMD]

Hi, I have 3 excel files that I have linked to Access. One is Total Stats, one is vs_Left Hand Pitching, the last is vs Right Hand Pitching. I then created a query to show a line that has all three sets of stats showing. When I run it I get the results I expected. Some lines have all 3 sets of stats while others will only have Total and LHP or RHP. The ones where there are only two sets of stats one of the sets is all null values. I'd like it to show Zeros instead of being blank.

I have tried an IIF statement and just a plain IF statement and neither seem to work. Do I need to create another category and perform a function on it to show the zero or can I just perform some action to put the zero there. Any help you can be would be appreciated.



GMD

[CarlettoFed]

Nz(Total;0)
Nz(LHP;0)
Nz(RHP;0)

[CJ_London]

Help to see the query and also where you are running it from- excel or access.

but as a suggestion use the nz function in your query to convert a null to 0

note that nz is not recognised in excel so you might need to use the iif function

iif(myfield is null, 0, myfield)

[Micron]

Being a bit of a baseball fan (Blue Jays of course) I have often wondered what a bb stats db looks like. I mean, I think the game announcers can tell you what a player's batting average is when facing lefties who use brown gloves on a Tuesday with 2 men on and 2 out when player B is in the lineup in the cleanup spot but only in OPCY stadium and between July 1 1995 until now. Or some such things. What they can tell you about a situation is just nuts.

Have to wonder why you want to default a text field to zero. IMO nulls are fine, especially since you can't sum on such a field anyway, so numbers don't seem relevant - especially for a text value of RHP or LHP. I also wonder if RHP an attribute of the pitch or the pitcher. If the latter, what will you do when he's both? Depending on that, maybe this is one table (2 at most) but not 3. Excel spreadsheets as Access tables is often a sign that the db lacks some normalization.

EDIT - then again, maybe I misunderstood and the zero is for a field in the totals table.

[GMD]

Help to see the query and also where you are running it from- excel or access.

but as a suggestion use the nz function in your query to convert a null to 0

note that nz is not recognised in excel so you might need to use the iif function

iif(myfield is null, 0, myfield)

Thanks for the response. I'll try to explain a little better. I have 3 excel files. I have them linked to my Access Database. In the datatbase I have 3 Tables. Batters Total, Batters_vs_LHP and Batters_vs_RHP. Then I have a query that runs and it shows the Players Name followed by his Total Stats, Vs LHP and Vs RHP. In some cases the batters information is null. So now in the query I'm just merging the 3 tables together. I'm not sure how to do the NZ function, or IIF statement to get a zero to show or N/A to show if it is a text field if the field is NULL or Empty. Lets say I have a category of At Bats. The line will show Total At Bats followed by AB vs LHP and AB vs RHP. Each field will come from a different table. If the AB vs LHP is Null where do I put the NZ statement? I see where to attach a file but I'm not sure I should attach a 16,000kb file.

thanks again for the reply.
GMD

Here is the sql code:

SELECT CTCBL_Master.bats, CTCBL_Master.mlb_last_first, CTCBL_Rosters.primary_pos, CTCBL_Master.Age, [2022_FanGraphs_Batters_Total].Team, [2022_FanGraphs_Batters_Total].G, [2022_FanGraphs_Batters_Total].PA, [2022_FanGraphs_Batters_Total].AB, [2022_FanGraphs_Batters_Total].R, [2022_FanGraphs_Batters_Total].H, [2022_FanGraphs_Batters_Total].[2B], [2022_FanGraphs_Batters_Total].[3B], [2022_FanGraphs_Batters_Total].HR, [2022_FanGraphs_Batters_Total].RBI, [2022_FanGraphs_Batters_Total].SB, [2022_FanGraphs_Batters_Total].CS, [2022_FanGraphs_Batters_Total].BB, [2022_FanGraphs_Batters_Total].SO, [2022_FanGraphs_Batters_Total].Avg, [2022_FanGraphs_Batters_Total].OBP, [2022_FanGraphs_Batters_Total].SLG, [2022_FanGraphs_Batters_Total].GDP, [2022_FanGraphs_Batters_Total].HBP, [2022_FanGraphs_Batters_Total].SH, [2022_FanGraphs_Batters_Total].SF, [2022_FanGraphs_Batters_Total].IBB, [2022_FanGraphs_Batters_Total].WAR, [2022_FanGraphs_Batters_Total].Year, CTCBL_Master.[B/P], [2022_Batters_vs_LHP].Avg, [2022_Batters_vs_LHP].OBP, [2022_Batters_vs_LHP].SLG, [2022_Batters_vs_LHP].PA, [2022_Batters_vs_LHP].AB, [2022_Batters_vs_LHP].H, [2022_Batters_vs_LHP].[2B], [2022_Batters_vs_LHP].[3B], [2022_Batters_vs_LHP].HR, [2022_Batters_vs_LHP].RBI, [2022_Batters_vs_LHP].BB, [2022_Batters_vs_LHP].SO
FROM ((CTCBL_Master LEFT JOIN CTCBL_Rosters ON CTCBL_Master.mlb_id = CTCBL_Rosters.id) LEFT JOIN 2022_FanGraphs_Batters_Total ON CTCBL_Master.fg_id = [2022_FanGraphs_Batters_Total].playerid) LEFT JOIN 2022_Batters_vs_LHP ON CTCBL_Master.fg_id = [2022_Batters_vs_LHP].playerid
WHERE ((([2022_FanGraphs_Batters_Total].Year)=2022))
ORDER BY CTCBL_Master.mlb_last_first;

[Micron]

If you want to post a file it has to be zipped. Maybe 'how to attach files' link at the top of the page will help you with that.

[GMD]

Micron,

I'm looking purely at Stats. At the end of the year MLB will put out the final stats for all the players. I have 3 sets of stats. One that has total stats, one that has the stats for the batter vs Left handed pitchers and one for the batter vs Right handed Pitchers. So if we break it down I'll have a player with 150 overall At Bats, 25 At Bats vs Left handed Pitchers and 125 At bats vs Right hand Pitchers. So that player will appear in all 3 tables. The next batter may have 45 overall At Bats with none vs Left hand Pitchers and 45 At Bats vs Right hand Pitchers. That player will only appear in two of the tables. So when I merge them there will be no value in any of fields vs Left Hand Pitching. I'd like it to realize that and put zeros in there.

Nothing fancy...but I do want most of the fields to be numbers Just in case I want to do any calculations on them. Like adding At Bats + Walks to get Plate Appearances or some such thing.

Thanks for the reply
GMD

[Micron]

Interesting to a baseball fan like me; perhaps not so much for European members. Maybe one day I'll see if there are any bball db templates out there to look at. Not that I'd be interested in creating anything, but just curious as to how the myriad of stats are organized. Your comment makes me wonder if the data is parceled out too much. I mean, I think I see a problem in breaking out rhp AB's into one table and lhp AB's into another. If they were in the same table, then the overall AB's are simply a count of rhp and lhp's no? In that case the field name might be "Laterality" and its values RH or LH. If this table would be about AB's you could also capture HBP's (hit by pitch, which is an AB stat but is not a factor in batting average) without resorting to another table. In case I'm not being clear, I'm saying that breaking out certain data into separate tables may create more problems than it solves. Anyway, I think you probably have the solution to your question and can ignore my pondering. I just find the subject matter very interesting. If you can't implement the suggestion(s) then by all means, check out the link for uploading files to see if it helps you.

[ArviLaanemets]

When using linked Excel tables, I myself use them always only to get data from Excel to Access/Sql Server Database. I.e. data from linked Excel Table(s) is read into separate Access/Sql Database table(s) (by On Open event, by Scheduled Task, or by procdeure run by user/event), and any further operating with this data is done based on those Access/Sql tables only. Import procedure will handle all differences in data presentation of Excel and target database.

[CJ_London]

Since this is in access, you can use the nz function or you can simply use the format property

assuming these are whole numbers in the format property put

;;;o

[GMD]

Interesting to a baseball fan like me; perhaps not so much for European members. Maybe one day I'll see if there are any bball db templates out there to look at. Not that I'd be interested in creating anything, but just curious as to how the myriad of stats are organized. Your comment makes me wonder if the data is parceled out too much. I mean, I think I see a problem in breaking out rhp AB's into one table and lhp AB's into another. If they were in the same table, then the overall AB's are simply a count of rhp and lhp's no? In that case the field name might be "Laterality" and its values RH or LH. If this table would be about AB's you could also capture HBP's (hit by pitch, which is an AB stat but is not a factor in batting average) without resorting to another table. In case I'm not being clear, I'm saying that breaking out certain data into separate tables may create more problems than it solves. Anyway, I think you probably have the solution to your question and can ignore my pondering. I just find the subject matter very interesting. If you can't implement the suggestion(s) then by all means, check out the link for uploading files to see if it helps you.

Thanks Micron. I did get it to work using NZ. I don't create the tables. I don't have any idea how FanGraphs or MLB or BaseBall Reference make thier data bases I only know how they spit out data. I use FanGraphs to get the initial data for my purposes. I can setup a custom user stats export. It gives you Total Stats for every player. I'm using the database to help in selection of players for Strat-o-Matic Baseball. It is a game produced in NY and it uses the LH/RH approach. So I need to know how a player performs as a Batter vs LH Pitching and vs RH Pitching. Without getting too deep in how the game works, suffice it to say you will get those 3 tables from any of the reputable stats keeping services. So I download them usually every day and replace the files with a new download and then run the queries again. There are also the same 3 files for pitchers also...Total Pitching stats, Pitchers vs LH Batters and Pitchers vs RH Batters. Then I run a Pitching query.

Just to clarify...The game does take some liberties. Plate Appearances for the purposes of Strat-o-Matic are AB + BB. It doesn't include HBP or IBB. Just to keep it simple for the game. You as a Baseball fan might like the game as it is the most statistically accurate at reproducing a season. We don't play it like that though. We put all the MLB players in a pool and we draft our own teams and play against each other in what are known as Draft Leagues. Take a peek at www.strat-o-matic.com

Thanks again to you all for pointing me in the right direction
GMD