#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0

    Ms Access-Display unique records


    I have joined 3 Tables Manually.

    I have written the following Query in SQL View to count all the unique CustomerID records in Table1.

    SELECT COUNT(CustomerID) FROM Table1 GROUP BY CUSTOMERID

    The result counts the unique records in TABLE 2 AND TABLE 3 AS WELL.

    Can someone help me to modify this Query so that I can perform the search on Table 1.

    Imp: I can't unjoin the table to get the result.

    Ashi
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62

    Re: Ms Access-Display unique records


    I have joined 3 Tables Manually.
    What do you mean, manually? Do you mean you have imported records from other tables into this table? Or do you mean you have made a Relationship (foreign key) with two other tables. There is no such thing as a "manual" join; A join is only possible in a query. Now, if you take the joined results of that query and make another table out of that, then that is a separate table, containing copies of records from the tables that were joined by the query.

    I have written the following Query in SQL View to count all the unique CustomerID records in Table1.

    SELECT COUNT(CustomerID) FROM Table1 GROUP BY CUSTOMERID

    The result counts the unique records in TABLE 2 AND TABLE 3 AS WELL.
    Since I have no idea what Table1 is, not to mention Table2 and Table3, it's really impossible to explain why you are getting these results. It does sound strange, though.

    Imp: I can't unjoin the table to get the result.
    Again, I am assuming you are talking about a Relationship. That should have nothing to do with the actual count of records in one table.

    Please explain whether my assumptions are wrong here, and describe everything you have done with these tables.

    Perhaps r937, our Access/SQL guru could help out here .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0
    Thanks for the reply.

    I am new to MS access. One of the ex-employee has built the database. What I see is that he has built relationship between the tables.

    When I run the query on a particular table in SQL View it also counts the extra records in the other table.

    Instead of diplaying the count result as 578 .. it displays 970.

    Please let me know if I am still no clear.

    I have attached a Screen shot of the table
    Attached Images
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279

    Re: Re: Ms Access-Display unique records


    Originally posted by rycamor
    Perhaps r937, our Access/SQL guru could help out here .
    thanks, rycamor, but i'm totally discombobulated by the description of the problem

    there's a join, or isn't there?

    ashi2001, your screenshot didn't help

    i'm not an access guru, i just know the (standard) sql language, and a bit about the access extensions to the language (e.g. IIF instead of CASE, etc.)

    however, i don't understand whatever it is that's producing that weird looking layout in your screenshot

    i can fix sql, but i have to see the actual query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0
    I have pasted the Query.

    SELECT COUNT(CustomerID) FROM Table1 GROUP BY CUSTOMERID


    I have built realtionship with other tables that can't be dropped.
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62

    Re: Re: Re: Ms Access-Display unique records


    Originally posted by r937
    however, i don't understand whatever it is that's producing that weird looking layout in your screenshot
    I seem to remember that this sort of layout is the result of viewing a table (or query) output, and clicking on the + sign next to any row expands out to show the rows in the foreign key relationship. I still don't see how that is relevant to the problem, though.

    Either of two things must be happening:

    1. ashi2001 is really not running this query against a table, but against another Query (which is really just Access's term for a view). This means the query is joining rows of the existing tables, giving a larger rowcount.

    2. There is some weird bug in Access, which crops up if you do a sufficiently convoluted set of table relationships.

    ashi2001, can you open the database, click on "Tools", then on "Relationships", and give us a screen capture of that? Also, could you give us a screenshot of the Design view of Table1?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0
    Thanks for the reply.
    I have taken the screen shots.
    Attached below please find the screen shots for the same.
    Attached Images
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0
    Screen shot for relationships window
    Attached Images
  16. #9
  17. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Rudy (r937) ... take a look at that Relationships screenshot (!?!?!). I don't know where to begin .

    One thing I will say is: you are showing me a table called "AccessMainTable", but your query mentions Table1. What is Table1? (you really should name your tables better)

    Absent a better explanation, I would suggest there is a chance that Access is corrupting your tables. This looks like a fairly complex database... something I wouldn't even attempt in Access. (Yes, Access is a good tool for certain needs, but I don't trust it for complex designs)
    Last edited by rycamor; September 11th, 2003 at 01:24 PM.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0
    Sorry

    The query is:

    SELECT COUNT(CustomerID) FROM AccessMainTable GROUP BY CUSTOMERID

    It agree with you it is a very complicated design structure.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    oh my $deity, that's one horrible diagram

    ashi, i really want to help out a fellow canadian, but let's go back to your original question -- you want to count customers

    it's not clear (probably not to you either) whether you want the number of customers, or the number of customers involved in some relationship

    for example, if you have a customer table and an order table, counting the number of customers is one query, but counting the number of customers who have placed an order is totally different -- in fact, you usually have to use COUNT DISTINCT, which access does not support (not sure about the latest versions, though), because a single customer could place multiple orders, so you don't want to count any customer more than once, hence the DISTINCT

    normally what you have to do is create a view (in access, a query) that counts orders by customer, i.e. GROUP BY customer, and then write a query on that which counts rows and gives one number as the result

    make sense?

    rudy
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0
    In the following statement;

    normally what you have to do is create a view (in access, a query) that counts orders by customer, i.e. GROUP BY customer, and then write a query on that which counts rows and gives one number as the result

    Can U please clarify how the query would be written for the above as this is our exact situation and even though the report is "Grouped By" <Customer ID> the report still wants to count orders by (BusinessListingName/orders)
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    take your query --

    SELECT COUNT(CustomerID) FROM AccessMainTable GROUP BY CUSTOMERID

    save it and give it a name, e.g. CustomerCountQuery

    then run this query --

    select count(*) from CustomerCountQuery

    that will be the number of customers, since your GROUP BY query produced one row (containing a count) per customer

    rudy
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    18
    Rep Power
    0
    I want to tell you first off how MUCH your help is being appreciated. My name is Vera, i am Ashima's boss and i created the database. I am aware of being able to count the customers in the main table via the method you described. My dilema is as follows;

    I am attempting to count the customers that are in my report. This report tells me how many customers meet the criteria specified in my query. This particular query tells me how much each customer spent in total to date. Many of our customers have more then one BusinessListingName(business name) and therefore i designed the database to 'uniquely' identify the customer via the <CustomerID> field which is a 'Primary Key' and is an 'Auto Number' Field in the customer table. The report pulls data from the AccessMainTable. I have MANY reports that require me to quantify a customer as upposed to a business name. This is the method by which we will value a customer in $s. Upto this point i have been 'Analysing' the report in excel and MANUALLY creating formulas in excel that does the counting. As you can imagine, this is a huge time intensive task now that we are litterally talking about thousands of customers.

    IF you know of a way for me to (in the report footer???) have a field that counts the customers that are in the report output i am thinking this will work??? perhaps not.... any ideas?

    thanks for you help again, Ashima has told me how helpful you have been.

    V
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    yikes -- listen, i hope you did not take offence when i said the relationship diagram is horrible

    of course, what i meant to say was, it's fantastic, and most probably very comprehensive and totally appropriate for the application

    based on your requirement, and suggestion that the count be done in the report footer, i would imagine this to be fairly simple and straightforward for someone who knows access reports

    unfortunately, that's not me, i only know the sql

    perhaps you could post a note in the Project Help Wanted forum (http://forums.devshed.com/f40/s)

    good luck

    rudy
    http://r937.com/

IMN logo majestic logo threadwatch logo seochat tools logo