Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Organization (2k)

    My question is to get suggestions for going about a problem... I've attached an excel spreadsheet to help explain what it is I am trying to do since i think its kind of complex, atleast for me to explain.

    I have a large table of data that has 2 key fields for uniqueness. The first level of these 2 key fields is the item #, which in my example is ABC123 and DEF456. For each item, there are several sub-categories that vary from 1 to n (n being an integer). The items originate from an already written query that outputs in the form shown under "Original Output" in the excel sheet. Basically what i'm trying to figure out is how to perform the analysis i did in the excel example using access (sum, weighting, total, then the complexity factor and grading). the current query output i get from access is about 6000 lines so I don't think it would be smart to do this by hand in excel... Each item gets its own grade, which is my end result.

    Is this feasible to do in access? and any suggestions on how to go about doing this (crosstab query maybe, or multiple queries)?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Organization (2k)

    You say you have a large table. What is the structure of that table? If I understand your description correclty, the spreadsheet you attached contains the result of a query, not the table itself.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Organization (2k)

    The table's structure is that of the query, except the 0's and 1's are OK's and X's. And yes that is the structure of a query which is large due to the large table.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Organization (2k)

    It would be much more efficient if your table looked like this

    <table border=1><td align=center>Item</td><td align=center>Category</td><td align=center>Status</td><td align=center>Value</td><td>ABC123</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td>...</td><td align=right>...</td><td align=right>...</td><td align=right>...</td><td>ABC123</td><td align=right>6</td><td align=right>27</td><td align=right>0</td><td>DEF456</td><td align=right>1</td><td align=right>1</td><td align=right>0</td><td>..</td><td align=right>...</td><td align=right>...</td><td align=right>...</td><td>DEF456</td><td align=right>10</td><td align=right>27</td><td align=right>0</td><td>..</td><td align=right>...</td><td align=right>...</td><td align=right>...</td></table>
    You could then use a crosstab query. But even with the present structure, the grouping options in a report should enable you to do what you want, although the formulas will be more tedious.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Organization (2k)

    well the problem is the people using access are inputting "OK" and "X" in the forms, they are against any other type of input for whatever reasons. So i have a query that converts that info into 0's and 1's so i can find sums of the data, meaning i can add up all the X's (which were converted to 1's) to find out how many exist for a particular item.

    I'm not sure I get what you are trying to say, are you saying have a row that adds up the items? is that what the 6, 27 represent?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Organization (2k)

    The values can be OK and X, you can use a combo box to convert these to 0 and 1 when entered (the users will not see the 0 or 1, just the OK or X).

    In your spreadsheet, you have columns status1 through status27; it would be more efficient to replace this with a column (field) named Status (with values 1 through 27) and a column named Value (or something similar) with values 0 and 1. The Category column corresponds to the second column in your "original output" table.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Organization (2k)

    alright Hans, I'll see if I can restructure my data storage, hopefully that will help.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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