Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Pivot Queries and Tables

    I want to make sure that I am going in the right direction on a few reports that I'm going to have to develop for my db.

    The end-users requested to be able to see like "trends" for specific data. So after some research, I've figured that Pivot Queries and Pivot Tables would be the best methods for this.

    For example: the end-user would like to see how many people come through a specific Point-of-Entry. The conditions that I'm going to have on the form are:

    1. User selects the Point-of-Entry
    2. User can select specific time intervals (30, 60, 90, 120, or 180 days) via a combo box
    OR
    3. User can select a specific date using entries via a PopUp-Calendar form
    OR
    4. If the user does NOT select a date interval or selected dates, then it will DEFAULT to 1 year prior to the current date for the selected Point-of-Entry.

    When the user selects the "Show Results" command button. A Pivot Table / Graph will be displayed in a subform which the user will then need to be able to have the ability to PRINT.

    Do you think I'm going in the right direction for this? Would it be easier to do something else? If so, what?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Aside from the suitability of using a pivot vs a chart vs a standard report, it sounds like you are heading in the right direction

    Personally, I'd go with a chart for trends, I don't like pivot objects, they tend to confuse my users
    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
    Aug 2006
    Posts
    559
    Quote Originally Posted by StarTrekker
    Aside from the suitability of using a pivot vs a chart vs a standard report, it sounds like you are heading in the right direction

    Personally, I'd go with a chart for trends, I don't like pivot objects, they tend to confuse my users
    Now I've gotta figure out what and how I have to write these select queries to get what I need from them for the charts.

    Am I wrong by saying that the ones I need for like Country, Region, Point-of-Entry, Visa Type will need to be a Select -> Case statement since I have to have the user first pick the specific above from a combobox, then they can either pick a predetermined set of days, select specific date intervals, or default to 1-year prior to the current date?

    I'm also developing the form for that now. I've already put the combobox for the "Country" on it and that pulls up the country list, no problem. Would it be easier to make another table and put in the 30, 60, 90, 120, or 180 days and allow the user to select like that OR put a textbox and have the user ONLY be able to enter in those numbers?

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Alright, I've been doing some research and thinking and this is what I've come up with so far...

    SELECT DISTINCT tblCountry.CountryID, tblCountryNM, tblPassport.PassportID, tblPassport.IndID FROM tblCountry WHERE cmbCountry = "" AND CASE 1 cmbDays = "" OR CASE 2 txtStartDate = "" and txtEndDate = "" ELSE ((=NOW()-365))

    Does that make any sense? **It does in my head!!**

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'll leave that to the SQL gurus around here. I tend to use the Query Builder more than anything.

    However, the latter part of that SQL doesn't make sense in my head -- not sure what you're trying to do with the CASE thing.

    Would it be easier to make another table and put in the 30, 60, 90, 120, or 180 days and allow the user to select like that OR put a textbox and have the user ONLY be able to enter in those numbers?
    I'd make a Value List type combo box... depending on what/how you are going to use this in your report/chart.
    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

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by StarTrekker
    I'll leave that to the SQL gurus around here. I tend to use the Query Builder more than anything.

    However, the latter part of that SQL doesn't make sense in my head -- not sure what you're trying to do with the CASE thing.


    I'd make a Value List type combo box... depending on what/how you are going to use this in your report/chart.
    Trek,

    It's like I said, The idea is in my head and it sounds good there, although many things sound good in my head, so I'm just having the user select a country / region, then the number of days (either pre-determined, between specifically chosen dates, or default to 365 if the previous two aren't chosen), then it needs to return the results for like Vertical Axis= #of days , Horizontal Axis = # of people.

Posting Permissions

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