Query with sub query for aggregating values

[katjoe]

Hi I am not able to share the code due to the database in on a secured network. Perhaps I can modify it so I will get back to you with this.
On that note could you help with a query though?
What I am trying to do is:

1. Get the distinct values of the field 1.
2. Find the percentage of the Distinct Values by dividing the distinct value by the total value of field 1.
3. Counting all values in the three fields of the table then divide it by the distinct values to get the overall percentage.

Desired results:

Field 1	Distinct_Field	Percentage_Field	Total_Percentage_Field
X	3	1%	.01%
Y	1	.01%	.001%
Z	2	.02%	.02%

Modified version of Query one that will count the values in three columns.

<CODE>
SELECT Count([Field Table].[Field 1]) as Total_Field, Count([Field Table].[Location]) AS Total_Location, Count([Field Table].[Cars]) AS Total_Cars FROM Dealer;
</code>
Second query that will pull the results from query 1:
<code>


SELECT [Field Table].[Field 1], Count([Field Table].[Field 1]) As Distinct_Field, ([Distinct_Field] / Total_Field]) As Percentage_Field, ([Distinct_Field] / ( [Total_Field], [Total_Location], [Total_Cars])) AS Total_Percentage_Field
From Query
Where [Field Table].[Field 1] Is Not Null
GROUP BY [[Field Table].[Field 1];

Access does not like this. Not sure how to combined the distinct and apply the aggregate functions to get the above results.
Any Help is appreciated!!

[June7]

CODE tags use [] not <>.

[Field 1] is not in query 1. Query 1 aggregates and assigns alias name to expressions. Why not put filter criteria in query 1?

Provide sample raw data. What has db being on a secured network got to do with sharing code?

Strongly advise not to use spaces nor special characters in naming convention.

[CJ_London]

why are you starting a new thread when you have already asked the question here?
https://www.accessforums.net/showthread.php?t=90342