Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design Flaw (97:SR2)

    I'm having difficulty designing a query where the end results should look like:
    <table border=1><td>Dept</td><td>Name</td><td>CurPeriod</td><td>PriorPeriod</td><td>Variance</td>
    <td>0100</td><td>Aaaaaaa</td><td>Sept</td><td>blank</td><td>+1</td><td>0100</td><td>Bbbbbbb</td><td>Sept</td><td>June</td><td>+0</td></tr><td>0100</td><td>Cccccccc</td><td>blank</td><td>June</td><td>-1</td></tr></table>
    The main table's fields consist of Dept, Name and Period. I created two separate queries "Current and Prior" extracting data for the periods Sept and June. This is where I got stuck <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> and I'm not sure If I need a 3rd table or if it would be better trying to do a report. The variance column tests whether or not the individual was added or removed from the department as compared to the prior period.

    Any advice would be appreciated.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design Flaw (97:SR2)

    So in your main table you have records like this?

    <table border=1><td>Dept</td><td>Name</td><td>Period</td><td>0100</td><td>Aaaaaaa</td><td>Sept</td><td>0100</td><td>Bbbbbbb</td><td>June</td><td>0100</td><td>Bbbbbbb</td><td>Sept</td><td>0100</td><td>Cccccccc</td><td>June</td>
    </table>



    And you created a query to return all the records for Sept as the current period query and all the records for June as the prior period? Now you need a query that returns just the unique department and name combinations. Put that in the middle with an outer join to each of the other queries on dept and name and only include the period from each of the other queries.

    I'll leave you to figure your variance, since I'm not clear on what's involved, but I suspect it's something like "IIf(IsNull([PriorPeriod]), "+1",IIf(IsNull([CurrentPeriod]), "-1", "+0+)).
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design Flaw (97:SR2)

    Charlotte,

    So far so good. I created the new query but am having an issue with the "Unique" department and name. I'm not sure how to filter them. Otherwise I'm almost there.

    Thanks,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design Flaw (97:SR2)

    In the query grid, right click the gray area where the tables are displayed and bring up the properties dialog. You'll see two properties, Unique Records and Unique Values. The one you want to set to Yes is Unique Values. That will pop the DISTINCT keyword into your query.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design Flaw (97:SR2)

    Please excuse me for butting in, but I see that you can create a table in a post.
    Are there any instructions on the site explaining how to do this.

    Thanks
    Dave

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Tables in Posts (Any)

    Not to but in here too, but there are a couple of excellent "star posts" in Lounge Matters you may want to check out:

    <!post=Making Tables in the Lounge,162644>Making Tables in the Lounge<!/post> by Jefferson Scher aka jscher2000

    <!post=Copying a Word or Excel table to a post,164109>Copying a Word or Excel table to a post<!/post> by HansV

    A "Star" post is one that a Moderator has deemed to be particularly noteworthy or worthwhile.

    HTH

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design Flaw (97:SR2)

    After setting the "Unique Values" to Yes and running the new query I received numerous lines which are blank except for the prior period field. This does not make any sense to me as Curr Period and Prior Period contain 2400 and 2100 records accordingly. I would expect the new query to produce records between 2100 and 2400 not exceeding the 2400 as in my returned results.

    John

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design Flaw (97:SR2)

    The Unique Values is for the query in the middle, the one that gives you only the base records you want to join to the other two queries. Why don't you post the SQL for your queries and maybe someone can figure out what's causing your problem. If you join the base query to just one of the other two queries, do you get the right records? You may need some criteria in the top level query to get the records you need, but it's hard to guess what it might be without seeing the query itself.
    Charlotte

Posting Permissions

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