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
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 ;).
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
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
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.
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?
Thanks for the reply.
I have taken the screen shots.
Attached below please find the screen shots for the same.
Screen shot for relationships window
Rudy (r937) ... take a look at that Relationships screenshot (!?!?!). I don't know where to begin :eek: .
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)
Sorry
The query is:
SELECT COUNT(CustomerID) FROM AccessMainTable GROUP BY CUSTOMERID
It agree with you it is a very complicated design structure.
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
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)
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
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
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)