Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Simplest way to perform this multi-row update!

    I have a table that contains all NBA games from 2006-2007 season.

    Table has:
    Primary Key - Game Number
    Home_Team
    Away_Team
    Date
    Home_Team_Score
    Away_Team_Score
    ...and some other fields (unimportant for this question)

    I added fields H_NO_OF_H_GAMES and H_NO_OF_A_GAMES

    These fields should be calculated and contain Number of Home games played by the home team before this particular game!

    So if this is the first game of the season that field should read 0, if it's the last home game of the season it should read 40!

    Now, what is the easiest way to calculate those fields? Is there a simple Update Query method to perform this?

    The only idea that I have is to use VBA and do something like (in pseudocode):

    Code:
    Open Recordset - select all games from NBA2007 table
    
    Cycle through all records
    
    Use Dcount function to return number of records where home_team 
    is equal to current game's home_team and the date is < than the current date
    Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Without looking too much into this, YES, there would be an update query that would give the appropriate numbers and put them into your new fields.

    It would involve using a DCount with the same criteria as your pseudocode to represent the value to update the new field to.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its derived information, then why store it.. I'd argue that unless you have a really really good reason then you should calcualte that as required

    so your query is going to be something like.

    select count(GameNumber), Home_Team as NoHomeGames form mytablename
    Where GameDate between (startdate and enddate)
    Group By Home_Team

    that may work.. I forget the syntax on between and group by.
    if you run the query again for AwayTeam that will give you the 3 values you require (home games, awaygames & totalgames (home games + away games)

    if you must persist in updating your values then you could modify that query to be an update query. It may be that you cannot update a group by query, but you could use the output of that query as the feed to the update query.

    incidentally I'd strongly suggest you do not call columns after Access/Jet/SQL reserved words... Date is almost certainly a reserved word

    Id also suggest you get out of the habit of having spaces in your column names eg use GameNumber in place of Game Number. Personally I don't like the underscore character but who cares thats personal preference, but I would also consider developing a common naming shorthand eg "GameNo", but again thats personal preference.

  4. #4
    Join Date
    Apr 2007
    Posts
    108
    Here's the query that performs the trick (at least in the select department), guess I'll use another query on top of it to update the table with newly produced NumberOFHomeGames field.

    Code:
    SELECT NBA2007.ID, (NBA2007.Home_Team) AS Host, NBA2007.Away_Team, (NBA2007.DatumUT) AS Gameday, 
    DCount("[Home_Team]","NBA2007","[DatumUT] <#" & Format([Gameday],"mm\/dd\/yyyy") &
     "# AND [Home_Team] = '" & [Host] & "'") AS NumberOFHomeGames
    FROM NBA2007;
    Can't say it was exactly intuitive constructing this query, sitload of crap to swallow from Access before getting it to work!

    to healdem

    incidentally I'd strongly suggest you do not call columns after Access/Jet/SQL reserved words... Date is almost certainly a reserved word
    Valuable input, will respect it in the future!

    if its derived information, then why store it.. I'd argue that unless you have a really really good reason then you should calcualte that as required
    There is a good reason to use calculated data!

    First, the data is used in analytical process, and both books and experience slowly teach me that one should use different levels of normalisation and implement various levels of calculated fields depending on the purpose of the database, having ultra-normalised tables for multi-user, heavily updated transaction databases and having highly denormalised star-schema (dimension schema or whatever you call it) for analytical databases.

    The purpose of this particular database is to analyze past performance of NBA teams and try to find patterns useful for predicting future outcomes. Thus it creates sitload of statistics that are known to you before each game is played - you just don't know the outcome.

    Hence, it's single user, static data (past results don't change) type of database.

    Instead of getting lost in 25 expressions every time I want to analyze the data it's easier to have it calculated, stored and named via fields in a table.



    So, I really don't want to "drown" in that "anti-calculated super-normalised" hysteria that is so often repeated.

    If you ask me good part of that hysteria belongs to history for 3 reasons:

    1. DISK SPACE

    That was a huge factor back in the days of emerging relational databases, but now (in the days of cheap TBs) I'm willing to sacrifice 50 bytes of disk space per row instead of being constantly forced to write calculated expression

    2. PERFORMANCE

    Hardly a factor for any home computer in the case of databases I'm using, even if I included every NBA game ever played that still wouldn't present a problem for most modern computers.

    3. DATA VALIDITY, UPDATE, DELETE ANOMALIES

    That even today remains a problem assuming you have data you update. If you use historic data (meant not to be updated and changed ever again) and calculate values and store them in fields you have data anomalies only if your original data was wrong!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Can't say it was exactly intuitive constructing this query, sitload of crap to swallow from Access before getting it to work!
    thats SQL in JET... either get used to it or don't use it, theres no point griping about it. Every SQL engine has it own idiosyncracies and problems

    Rules is rules....
    They are there for a reason...
    But like any rule it can always be set aside or ignored... PROVIDING you are doing that for a good reason...... and it looks like you understand the reasons and have addressed them.

    if its for performace reasons, then thats often a good enough reason. but you need to fully understand what problems you are getting into by ignoring one of the fundamental rules of relational db's.

    data validity and consistency is the main reason why you shouldn't store derived values in the db. Arguably in a single user or single developer environment its not that significant as the developer should be aware that every time they update the underlying data they also update the running totals. its more of a problem in the Access/JET environment than server products. essentially there is no way to automatically force Access/JET to update the derived values.. it has to be done programmativally. It can be done automatically in server products if they support a trigger / stored procedure.

    your point 3 is valid if you are switching away from the relational model to data warehouse model. One of the first things done in data warehousing is to de-normalise the data precisely for performance reasons.

    I dont want to start a flame war......
    in my view
    point 1 is irrelevant..... its never been about storage its about data integrity.. the whole driving ethos behind relational databases

    point 2 performance.. storing derived values is usually about performance or accounting / legal requirements. IE once an invoice is raised then it must remain "as is" in case of query (by the customer, auditors etc..)

    the 25 expressions isn't actaully an issue.. you could use the results of the count() expression query joined to another query. think of the query as a function in SQL. Using it as a function reduces the complexity of other queries.

  6. #6
    Join Date
    Apr 2007
    Posts
    108
    I dont want to start a flame war......
    I see this as very valuable discussion, I'm in no way an expert and I accept the fact that most know much more than me!

    I was just expressing my opinion and I always like to hear why I'm wrong in argumented way!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •