I am having problems defining an object with MS Access database. I am trying to use count(distinct (field1)) but for some reason count and distinct is not working together. I checked the help for MS Access and the function distinct is somewhat different than the Oracle one. It is distinctrow for MS Access then I tried that too. My new expression looks like this count( distinctrow field1) but still getting error. This function is pretty easy to define for an object if the universe is based on Oracle Database but finding difficulties for MS Access.
Anyone who can help me in this regard. Thanks in advance and I really appreciate your help…
The situation is pretty simple as I have a simple fact table. For the experimental purposes right now it just contain 3 values. It has application_ID, application_version_no,technology_ID, technology_version_number. The entries in the fact table are given below
and the application_Id and app_ver_no values are coming from application dimension table and the same with the technology_id and technology_ver_no. Now the problem is when I am trying to run a query
which is calculating which technology is using how many number of applications, the results are not appropriate.
Results which I am getting is for tech_ID = 1 , No. of applications = 2
tech_ID = 2 , No. of applications = 1
I defined a measure object as No. of applications = > count(application_ID.application dimension table)
What I am expecting is it should give me this result
tech_ID = 1,No. of Applications = 1
tech_ID = 2, No. of Applications = 1
Probably confliction occuring due to the version numbers . For this I am trying to use a count(DISTINCT application_ID.application dimension table) function but its not working for MS Access database.
Any solutions for this problem. Thanks in advance…
Create a query in Access to do a select distinct. you will then be able to import this query into your universe in the same way that you would import a view in Oracle or SQL Server.
I’m not sure you’re getting what you think you want… there is a difference between
select distinct (count(x))
and
select count(distinct x)
One will give you unique rows, which is supported. That is the same thing that would happen if you clicked “No Duplicate Rows” on the query panel. But what you really want is to count the distinct values of a column within a set of rows, and Access - to the best of my knowledge - does not offer that feature.
I opened the “club” database directly in MS Access and ran this SQL
SELECT Country.country, Count(Customer.cust_id) AS CountOfcust_id
FROM (Country INNER JOIN Region ON Country.country_id = Region.country_id) INNER JOIN (City INNER JOIN Customer ON City.city_id = Customer.city_id) ON Region.region_id = City.region_id
GROUP BY Country.country;
It returned rows. But when I attempted to add the “distinct” keyword inside the count() function it generated an error.
It sounds like you want a measure to show the distinct number of application IDs for each tech id, irrespective of the version no of either. If that is incorrect, ignore the rest of this and let me know.
If not, read on
In Access, create a query to return Application ID and Tech ID only.
Edit the SQL and add the word DISTINCT after the first SELECT, so you will have something like:
SELECT DISTINCT APP_ID, TECH_ID
FROM TABLE;
Save this query with whatever name you want - Bob perhaps?
Now boot up Designer and open the Universe. Double clicking in the data schema space will bring up the insert table dialog. Find the query that you just created and drag it into your schema.
Create one object, Tech ID which will select Tech ID from this query and another object Count, which will select count(App_ID) from this query. The query itself does the distinct before the count so you don’t have to try and do what Access can’t.
Thats a good solution for the problem. Actually I wasnt aware that we can do anything like this as well. But now I am having different problems. I replace that query with the fact table and ran the query. It wasnt working well. When I tried to run the query like No. Applications with the tech_ID it is giving me the same results as i was getting earlier and wen i ran it little differently like No. of technologies with the App_Name. The query gave me cartesion product. Still trying to figure out the solutions. MS Access makes this simple thing harder to achieve…
Yes I added a join between the application table and the query. My second join is between the technology table and the query. Initially it was like application table and technology table connected to the fact table named application_technology . I replace this fact table with the query table and created joins i mentioned above. I am assuming that I am doing the right approach but I may be wrong in conencting the query table.
Check where your objects are defined from. Are any still linked to the fact table (pulling this into the query without joins may be causing your cartesian problem.
I checked my schema. The query table is connected properly.I replaced this query table with the fact table and made all neccesary joins with this new query table but still getting the wrong values. My all new queries are pulling the data now from this new query table and its related tables and they all properly join together. This MS Access database made this simple thing hard to achieve. I am probably thinking to migrate the database to Oracle but till then I have to deal with this issue.
(BOB member since 2004-09-23)
