Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2009
    Posts
    95

    Question Unanswered: Subform Results Help

    I am working in Access 2000.

    Currently I have a form with a combobox where I selected the company name. On that form the subform then displays all records that involve that company (address, city, state, zip, and other fields).

    I need to somehow clean up the data and make it only display the record if it has a different address and give me a new column counting how many times that address would have showed up if I had not made it only display each address once.

    In essence go from this....
    Company1-Address1-City1-etc
    Company1-Address2-City2-etc
    Company1-Address1-City1-etc

    To This (Taking away the dupilicate record that has the same address)
    Company1-Address1-City1-2-etc
    Company1-Address2-City2-1-etc
    (The 2 & 1 being how many times that address appears next to the company)

    Thank you for any help or assistance,
    CT

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    CT, the query you want is:
    Code:
    SELECT company-col, address-col, city-col, COUNT(*) AS CNT
    FROM table-name
    GROUP BY company-col, address-col, city-col
    This will get you 1 line for each Unique occurrence of the company-col, address-col, city-col combination of values and the Count of how many rows were summarized into that one row.

  3. #3
    Join Date
    Jun 2009
    Posts
    95
    I am confused as to how to translate that code into a query.

    I apologize for my lack of knowledge.

    Thanks,
    CT

  4. #4
    Join Date
    Jun 2009
    Posts
    95
    I think a problem I am running into is when I try to group by only the company name and address I get the following error....

    "You tried to execute a query that does not include the specified expression 'Column I am not trying to group by' as part of an aggregate function."

    I have a total of 13 columnns and only two of them need to be grouped.

    Also, how do I set up the count?

    Thanks again,
    CT

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    CT, the way GROUP BY works is it summarizes duplicate values. If you Group by Company and Address (and only have them in the Select along with any aggregate functions), then you will get one row per unique Company/Address value. However, if you add a third column City then you have to consider three columns for unique value combinations (Company/Address/City). The more columns you add the more Unique the rows will get and less summarization will occur.

    If you just want a count of the distinct Company / Address values then:
    Code:
    SELECT company-col, address-col, COUNT(*) AS CNT
    FROM table-name
    GROUP BY company-col, address-col
    Will get you that.

    What are the 13 columns you also want?

  6. #6
    Join Date
    Jun 2009
    Posts
    95
    First, I have no idea why I said 13, haha

    My columns are:
    Company
    Address
    City
    State
    Zip
    Date_ On
    Date_Off
    Status

    I need to have each company-address combination to appear only one time in my subform and have another column with the count of how many occurances there are of each company-address combo.

    Example of what I need to display on the subform if my table had 3 occurances of the company1-address1 combination
    Company Address City Etc Occurances
    Cmpy1 Add1 Cty1 .... 3


    Thank you for your help,
    CT

  7. #7
    Join Date
    Jun 2009
    Posts
    95
    That came up really hard to read...

    I need column headings to display - Company Address City Etc Occurances

    With information below it.

    All information past address is unique as the information is obtained through user input and simply, different information.

  8. #8
    Join Date
    Jun 2009
    Posts
    95
    Figured it out... Thank you a lot for your help.

    CT

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    CT, the question is are all the other columns (City, State...Status) Unique within the Unique Company/Address values.

    If they are, just add all the columns to the Select and Group By.

    If they are NOT (and from your original examples this is the case), how do you determine which non-unique row you want to display?

    Company1, Address1, City1, State1
    Company1, Address1, City2, State1

    In the above example, the unique combination is Company1, Address1, Count 2. Now, which City do you want displayed (City1 or City2) and how do you determine this. NOTE: you will need to make this determination for all the extra columns (unless you want to base in on one column like City and just accept the rest of the columns.

  10. #10
    Join Date
    Jun 2009
    Posts
    95
    One problem... How do I go about combining the query data with the data on the table?

    I cannot add the other data to the Group By query as it would make each record unique...

    Sorry for the tease that I figured everything out and thanks for the help,
    CT

  11. #11
    Join Date
    Jun 2009
    Posts
    95
    Just realized that what I said I wanted did not make sense...

    Let me form a new question...

    I got a subform now that displays the queried information and I get
    Company-Address-Occurances for each unique combo of company&address

    Is there a way that I can incorporate something that when the record on the subform is selected displays a unique number for each occurance along with the unique data with that occurance?

    Sorry for the confusion, I had not though it out enough myself.

    Thanks,
    CT

  12. #12
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Can you post your solution for others who may need the answer for similar situations?

  13. #13
    Join Date
    Jun 2009
    Posts
    95
    Solution to get unique values and count:

    Code for query:

    SELECT [TableName].[Company], [TableName].[Address], Count(*) AS CNT
    FROM TableName
    GROUP BY [TableName].[Company], [TableName].[Address];

Posting Permissions

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