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

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.


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.

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

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.

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 .

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