Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Breaking down a query field (2002)

    Hi,

    I've been working for hours trying to find the best way to break down a field in a query into separate columns in the query. I have a table with [payclassd] which includes 11 different names. Once a week these 11 names have [dollars] and [total hours] applied to them. I want to set up the query so that each of the 11 names is its own column containing the [dollars] and [total hours] that correspond to it. My ultimate goal is find the % breakdown in [dollars] and again in [total hours] for each week. Up until this point I've been doing this in excel in pivot tables without a problem. I'm trying to get away from using excel for this.

    If there is a better approach to this I'm open to any suggestions. This is the only way I could come up with to do it.

    Thanks,
    Leesha

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

    Re: Breaking down a query field (2002)

    It isn't clear whether those 11 names are stored all together in one field in your table, whether you have concatenated them into a single field in the query, or what else you might be working with. If you have a table with a payclassd field and 11 records, each for a different person, you can use a crosstab query to break them into separate columns. Have you tried using the crosstab query wizard?
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Breaking down a query field (2002)

    Hi Charlotte,

    To clarify, the names are stored in a single field in the table and are not concatenated. In essence what I want to do is deconcatenate them if there is such thing.

    I have a 2 crosstab querys that provide me with the 11 fields across the the column headings, the weeks are used as rows and the [dollars] in one [total hours] in the other are the values. They work beautifully. What my boss likes to look at is the % mix/ week for these 11 fields. I've tried getting at this in a crosstab but with no luck.

    Leesha

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

    Re: Breaking down a query field (2002)

    A single field, yes, but are they in the same *record* or in 11 different records? And I'm sorry but % mix/week is meaningless as an explanation although I'm sure it seems clear to you. Since we have no idea what you're trying to view in that "% mix", it doesn't help us answer your questions. If you already have two crosstab queries that work, and you're already getting those names in 11 columns, what is it you want to see?
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Breaking down a query field (2002)

    Hi Charlotte,

    They are 11 different names in the single field. Each name has [dollars] and [total hours] applied to it on a weekly basis. The present crosstab queries show each of the 11 names across the top, have the week as a row and the value shows the [dollars] in one and the [total hours] in the the other. My goal is to show the% for each item in the row (total % would equal 100%) in the same manner its used in a pivot table, so that we can the mix of buisenss for dollars and hours. For example I can now look and see what the mix of business is for Medicare (one of the 11 items) as it relates to the number of hours a week that we provide to clients. My boss wants to be able to look at as what % of our business is Medicare.

    I'm not sure if that made it any clearer?

    Thanks!
    Leesha

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking down a query field (2002)

    Hi Leesha,

    Create a select query based on your crosstab query.
    Add the fields you want and create a new field with the expression :
    [dollars]/DSum("dollars","YourCrosstabQueryName")
    and format it as Percent.
    Do the same for total hours.
    If you want you can add the two crosstab queries in one select query, linked on your names field and add two expression with dollars and total hours
    Francois

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Breaking down a query field (2002)

    Hi Francois!

    I'm getting there, I think! Believe it or not I had tried the aproach of creating a query based on the crosstab query (trying to think like you guys!) but that is as far as I got. I didn't know to use DSUM. I just did as you suggested but am running into an issue in that [dollars] does not actually come up in the available fields for the crosstab query. Only the column names, date for the week and office names and year (all of which were used as rows) come up. When I created the field as you suggested I got an error that [dollars doesn't exist]. What am I missing?

    Thanks,
    Alicia

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking down a query field (2002)

    Re-reading your previous post, I mean to understand you want to now the percentage of the total over the row and not the column.
    Then you'll still have to create the select query and add 11 expressions like this one, different for each name.

    [medicare]/([medicare]+[Government]+[Insurance]+[Facility]+[Medicaid]+[Medicaid Waiver]+[Medicare Pt B]+[Medicare Pt B O]+[Pay/Nobill]+[Private Pay]+[Sub-Contract/Ag])
    [Government]/([medicare]+[Government]+[Insurance]+[Facility]+[Medicaid]+[Medicaid Waiver]+[Medicare Pt B]+[Medicare Pt B O]+[Pay/Nobill]+[Private Pay]+[Sub-Contract/Ag])
    ....
    Add the 11 expressions for the dollars
    Add the 11 expressions for the total hours
    (got the names from a previous database)
    Francois

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Breaking down a query field (2002)

    That worked beautifully!!

    Thanks,
    Leesha

Posting Permissions

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