PING: Microsoft Access Guru's

[gottimd]

I am using the Interrogator built by gstelmack to automate some of my reporting process within access rather than using excel sumifs and vlookups. The problem is, is that I can't seem to get a simple query working. My query looks like this:



I am just trying to pull games played over the career here, and the result is:



Why is it showing so many games played? We only have had 10 seasons, (2004 to week 6 in 2013). What am I doing wrong here?

[cartman]

You are getting a cartesian join, which means it is summing up the total games played for a player times the number of players on the team. In SQL I would know how to write the query, but Access is just enough different that it wouldn't work. You might try to put the GamesPlayed before the TeamName column and see if that helps.

[gottimd]

Thanks for the quick reply, but that didn't work either. Still showing as playing thousands of games.

Man this is frustrating!

[cartman]

Do you really need the team name in the query? I think if you drop the Teams table from the query, then you'll get the results you are looking for.

[gottimd]

Nope, I tried that too. Thanks for the suggestions though.

[lordscarlet]

go to the "SQL" view and paste that here.

[knolysis]

I would guess the Mappings table has more than 1 row in it. From the output of the query, it doesn't look like you are using the table. Try removing it from the query to see if that helps the situation.

[henry296]

I think it is the mappings table that is causing the multiple counts.

[Antmeister]

Originally Posted by henry296

I think it is the mappings table that is causing the multiple counts.

Yes, please delete Mappings from the query. On top of that, please check to see if you set the relationships between the tables prior to creating the query.

If neither of those work, send me a PM and I will give you my email to send it to me.

[gottimd]

Thanks all, got it to work! It was the mappings file.

Ok, so now I have a ton of queries to do all the league, career and rookie leaders and macros to import the data from the interrogator. Next question is, I used the Macros in Access and used the "Transfertext" from the drop down to import the csv's, how do I erase the contents of the table without deleting the tables in the macro? They way I have it, everytime I import it will append to the bottom and the data will grow exponentially in duplicate.

How easy is it to set up an SQL to link to this Access database?

[henry296]

You can use a delete query to delete all records but maintain an empty table with the same structure.

[marcmoustache]

Originally Posted by henry296

You can use a delete query to delete all records but maintain an empty table with the same structure.

Gottimd, you can put these delete queries in your macros too.

[marcmoustache]

Originally Posted by gottimd

How easy is it to set up an SQL to link to this Access database?

What SQL d/b are you trying to link from?

[gottimd]

I am using the interrogator and importing them into a DB for now on my local computer. Now I just need help getting them online

[GoldenEagle]

Access should generate a SQL statement for you, but you will probably need some minor changes to get it to work for the web. In Access, go to your query and then go to SQL view and paste that statement here.

[gottimd]

Current Year QB TD Passes:

SELECT TOP 15 [Player Game Stats].Year, Teams.TeamName, [Historical Player].LastName, [Historical Player].FirstName, Mappings.Position, Sum([Player Game Stats].TDPasses) AS SumOfTDPasses
FROM (Teams INNER JOIN (([Player Game Stats] INNER JOIN [Active Player] ON [Player Game Stats].PlayerID = [Active Player].PlayerID) INNER JOIN [Historical Player] ON [Active Player].PlayerID = [Historical Player].PlayerID) ON Teams.Index = [Player Game Stats].Team) INNER JOIN Mappings ON [Active Player].Position = Mappings.Index
GROUP BY [Player Game Stats].Year, Teams.TeamName, [Historical Player].LastName, [Historical Player].FirstName, Mappings.Position, [Active Player].Experience, [Active Player].PlayerID
HAVING ((([Player Game Stats].Year)=2013) AND ((Sum([Player Game Stats].TDPasses))>0))
ORDER BY Sum([Player Game Stats].TDPasses) DESC;

So do I just copy, import my Access DB from my local machine to the site on the network? If so, how? Its rather large with all of the queries, should I just keep a template of the three standard queries I want (Career, Rookie and League Leaders)?

[GoldenEagle]

The size is fine. Are you just making it where others can download the Access DB or you wanting to put the information on to a website?

[gottimd]

Originally Posted by GoldenEagle

The size is fine. Are you just making it where others can download the Access DB or you wanting to put the information on to a website?

I want to put it on our website.

[GoldenEagle]

What type of programming language are you going to use?