Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    6

    Unanswered: DB Logic problem

    Im making a database that will eventually migrate to SQL server but for now is using access. The intention is a worldwide system so instead of my usual mickey mouse tables, I decided to try to do it right.

    The problem:
    I would like to get ranks by city for a particular type of music. (this is probably easy but my brain is fried.)

    Database Setup
    Tables
    Cities: CityName, CityID, etc.
    Genre: GenreID, GenreName, Rank
    Events: EventID, CityID, EventName, Description, Etc.

    I will record clicks when a user is in say city #12 and clicks on HipHop but am at a loss as to where to record those and how to query for them after.

    Does this make any sense?
    p.s. (genre = music)
    Last edited by drdream; 10-15-05 at 17:38. Reason: Fixed subject

  2. #2
    Join Date
    Oct 2005
    Posts
    4
    I simply would have a field in the Genre table which will hold the CityID. So the flow should go as follows.

    1) Person selects City, CITYID is now saved (cookie, variable, whatever you like to save it.)

    2) Person selects Genre. Now the field in this table, GenreTable.CityID , You will simply record the CITYID previously recorded.

    You need some kind of relationship between these tables.

    Now just run a query for the Genre table and do a where = "CITYID#" and a count.

  3. #3
    Join Date
    Feb 2005
    Posts
    6

    I dont understand

    By what you are saying or from what I gather, the Genre Table will have a field called CityID.. but that restricts to just one entry per Genre, so if the Genre is HipHop, then the cityID would be constantly replaced by new users in new cities?

    If someone in say chicago clicks hiphop then the GenreTable.CityID would then be replaced by "14" or something.

    I think I need a NEW table called GenreRank with CityID, GenreID, and Rank but it seems this table would get pretty big (there are about 120 genres)

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Add Genre ID to the events table. As far as rank, you will need to go into more detail on what "rank" means to you with regard to this project.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2005
    Posts
    6
    GenreID is already in the events table. Rank is a number that can later be counted, averaged, sorted, etc.

    It will increment for a given set of circumstances (i.e. User clicks on that particular genre)

    It is easy to have one Genre per rank but this limits the entire Genre to the average of every city instead of giving each city its own genre.
    Last edited by drdream; 10-18-05 at 04:25.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why would every city have it's own genre? I'm confused...

    If you want to get the most popular genre for a given city, you can use grouping levels and aggregate functions on the events table to get statistics for each individual city.

    what I meant by rank, is how do you derive a specific value? What makes Rank = SomeNumber?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2005
    Posts
    6
    Why would every city have it's own genre? I'm confused...
    Thats what my original question is.. sort of. There is, and I believe should only be, one Genre table. Im having trouble explaining this. So ill try harder.

    Lets say Rank is now Ciicks. So each time a user in Miami clicks "house" then "1" would get added somewhere, if someone in Paris clicks "house" in paris then there should be a separate place to record that. Clicks will increment.

    My final SQL would be something like.
    Code:
    "Select * From Genre {BY CITY} ORDER BY Clicks Desc"
    If you want to get the most popular genre for a given city, you can use grouping levels and aggregate functions on the events table to get statistics for each individual city
    Yes this is true and thank you for pointing this out, however this would only apply to the events that are there, I want to record clicks on a particular genre.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Replace "events table" with "clicks table" in my original reply, same thing.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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