Trying to Create a Query that shows Percentages

[KingOf206]

I have this list of items that I am tracking.



I want to create 2 different queries:

1) The first is about percentages and items remaining with respect to my "Status" field. There are a number of status's including "Done", "Pending", "INcomplete" (These are found under that "STATUS" field)

What I would like to see is create a query that will show the following (table with numbers are just an example & is simple to show):

Status	# Of Items	Percentage
DONE	5	10%
INCOMPLETE	10	20%
Pending	20	40%
etc...	15	30%
Totals	50	100%

2) The second is about percentages and items remaining with respect to my "Priority Level" Field. There are a number of Priorities including "High", "TBD", "Low", etc... (These are found under that "Priority Level" field)

What I would like to see is create a query that will show the following (table with numbers are just an example & is simple to show):

Priority Level	# Of Items	Percentage
High	5	10%
Low	10	20%
TBD	20	40%
etc...	15	30%
Totals	50	100%

How can I make these 2 queries?


PS:

I could not get my 5 mb Zip file to attach due to limits. SO i used HJ Spilt and broke it up into 3 files. However, in order to upload it I have to change the file extension of these 3 files so currently the file names end in "FileName...001.zip", "FileName...002.zip", & "FileName...003.zip". When you download these files to your desktop you will need to change the file extension back to "FileName.zip.001", "FileName.zip.002", & "FileName.zip.003". THen once this is done, they should be able to be re-joined again.

Here are those files:
ALT-EIMS-IssuesAndToDoList001.zip
ALT-EIMS-IssuesAndToDoList003.zip
ALT-EIMS-IssuesAndToDoList002.zip

[ItsMe]

The first query, you will need the total count of records prior to getting the Count of Status. You could use a Dcount function within your query and perform a percentage of that. It may be better, performance wise, to create a second query that has a total count of records and JOIN your first query on that second query. You want to start by getting a SELECT query (a main query with a subquery) to retrieve data like
Status - CountOfStatus - TotalRecords

I suppose if I downloaded your samples I would have insight to your data structure. As for the second query, I suspect the same answer as query one applies. Simply, go after Priority Level instead of Status.

[KingOf206]

Yes please feel free to download it. I am not that familiar with the functions you are referring to

[KingOf206]

Is anyone able to assist? I thought this would be a fairly easy thing to do. Any help would be much appreciated

[Bulzie]

I did a little sample SQL with 1 table tblStatus with fields ID(autonumber) and Status ( values of "Done", "Complete", "Incomplete") using the query Designer, maybe try something like this changing names for your names. So if you create my sample table, then stick this in the SQL code of a query designer and then go to design, etc.

SELECT tblStatus.Status, Count(tblStatus.Status) AS CountOfStatus, DCount("[ID]","tblStatus") AS TotalRecs, [CountOfStatus]/[TotalRecs] AS vPercent
FROM tblStatus
GROUP BY tblStatus.Status;

You might want to look at using a crosstab query also for this.