Aggregate help

[troy_lee]

I am trying to count the total number of units for a given part number
that have a Priority rating of 1. Based upon some research, this is
what I came up with for my query. Access says that I have invalid
bracketing in the Select Distinct statement. The only brackets I
inserted were to delimit the table's field names. Access did the rest.
Also, Access created the second AS bracket group - [%$##@_Alias].

Am I barking up the right tree or is my query statement way off base?
I am not sure where to put the WHERE clause and I don't understand the
bracketing error Access is giving.

Query Statement:
SELECT COUNT(*) AS ["Planned Ships"] FROM [SELECT DISTINCT [Part No
2540] FROM RMAs WHERE [Priority] =1] AS [%$##@_Alias] GROUP BY [Part
No 2540];

Thanks in advance.

Troy Lee

[Keith Wilby]

<tr******@comcast.netwrote in message
news:a5**********************************@26g2000h sk.googlegroups.com...

>I am trying to count the total number of units for a given part number
that have a Priority rating of 1. Based upon some research, this is
what I came up with for my query. Access says that I have invalid
bracketing in the Select Distinct statement. The only brackets I
inserted were to delimit the table's field names. Access did the rest.
Also, Access created the second AS bracket group - [%$##@_Alias].

Am I barking up the right tree or is my query statement way off base?
I am not sure where to put the WHERE clause and I don't understand the
bracketing error Access is giving.

Query Statement:
SELECT COUNT(*) AS ["Planned Ships"] FROM [SELECT DISTINCT [Part No
2540] FROM RMAs WHERE [Priority] =1] AS [%$##@_Alias] GROUP BY [Part
No 2540];

Thanks in advance.

Troy Lee

Try something like

Select Distinct Count(*) as "Planned Ships" from RMAs Where [Priority] =1
GROUP BY [PartNo 2540]

Keith.
www.keithwilby.com

[Salad]

tr******@comcast.net wrote:

I am trying to count the total number of units for a given part number
that have a Priority rating of 1. Based upon some research, this is
what I came up with for my query. Access says that I have invalid
bracketing in the Select Distinct statement. The only brackets I
inserted were to delimit the table's field names. Access did the rest.
Also, Access created the second AS bracket group - [%$##@_Alias].

Am I barking up the right tree or is my query statement way off base?
I am not sure where to put the WHERE clause and I don't understand the
bracketing error Access is giving.

Query Statement:
SELECT COUNT(*) AS ["Planned Ships"] FROM [SELECT DISTINCT [Part No
2540] FROM RMAs WHERE [Priority] =1] AS [%$##@_Alias] GROUP BY [Part
No 2540];

Thanks in advance.

Troy Lee

Select Count(*) As "Planned Ships" From RMAs WHere Priority = 1

If you're going to group by [Part No 2540], why don't you show it as a
column? It'd be kinda ridiculous to see a list of 1, 20, 12, 15 with no
idea what the value is of the field [Part No 2540] since you'll be
breaking on the different values of the [Part No 2540] field.

You might consider going thru help and reading to understand SQL, Totals
queries, Aggregate functions, where clauses, etc.

Or search Google for info. I entered Access Queries in google and came
up with http://www.fmsinc.com/tpapers/queries/ for a starter, lots more
available. You really should get a firm background on how to create a
query before designing one.

Wave Insanity
http://www.youtube.com/watch?v=8LoH6oQzJZs

[troy_lee]

On Jun 16, 10:58 am, Salad <[e-mail]:

[e-mail] wrote:

I am trying to count the total number of units for a given part number
that have a Priority rating of 1. Based upon some research, this is
what I came up with for my query. Access says that I have invalid
bracketing in the Select Distinct statement. The only brackets I
inserted were to delimit the table's field names. Access did the rest.
Also, Access created the second AS bracket group - [%$##@_Alias].

Am I barking up the right tree or is my query statement way off base?
I am not sure where to put the WHERE clause and I don't understand the
bracketing error Access is giving.

Query Statement:
SELECT COUNT(*) AS ["Planned Ships"] FROM [SELECT DISTINCT [Part No
2540] FROM RMAs WHERE [Priority] =1] AS [%$##@_Alias] GROUP BY [Part
No 2540];

Thanks in advance.

Troy Lee

Select Count(*) As "Planned Ships" From RMAs WHere Priority = 1

If you're going to group by [Part No 2540], why don't you show it as a
column? It'd be kinda ridiculous to see a list of 1, 20, 12, 15 with no
idea what the value is of the field [Part No 2540] since you'll be
breaking on the different values of the [Part No 2540] field.

You might consider going thru help and reading to understand SQL, Totals
queries, Aggregate functions, where clauses, etc.

Or search Google for info. I entered Access Queries in google and came
up withhttp://www.fmsinc.com/tpapers/queries/for a starter, lots more
available. You really should get a firm background on how to create a
query before designing one.

Wave Insanityhttp://www.youtube.com/watch?v=8LoH6oQzJZs

Access doesn't recognize the Count Distinct function, does it?
Everywhere I have been reading, it says that I have to use a subquery
to use the Distinct function. Is this not true?

[Salad]

tr******@comcast.net wrote:

On Jun 16, 10:58 am, Salad <[e-mail]:

>>[e-mail] wrote:

>>>I am trying to count the total number of units for a given part number
that have a Priority rating of 1. Based upon some research, this is
what I came up with for my query. Access says that I have invalid
bracketing in the Select Distinct statement. The only brackets I
inserted were to delimit the table's field names. Access did the rest.
Also, Access created the second AS bracket group - [%$##@_Alias].

>>>Am I barking up the right tree or is my query statement way off base?
I am not sure where to put the WHERE clause and I don't understand the
bracketing error Access is giving.

>>>Query Statement:
SELECT COUNT(*) AS ["Planned Ships"] FROM [SELECT DISTINCT [Part No
2540] FROM RMAs WHERE [Priority] =1] AS [%$##@_Alias] GROUP BY [Part
No 2540];

>>>Thanks in advance.

>>>Troy Lee

Select Count(*) As "Planned Ships" From RMAs WHere Priority = 1

If you're going to group by [Part No 2540], why don't you show it as a
column? It'd be kinda ridiculous to see a list of 1, 20, 12, 15 with no
idea what the value is of the field [Part No 2540] since you'll be
breaking on the different values of the [Part No 2540] field.

You might consider going thru help and reading to understand SQL, Totals
queries, Aggregate functions, where clauses, etc.

Or search Google for info. I entered Access Queries in google and came
up withhttp://www.fmsinc.com/tpapers/queries/for a starter, lots more
available. You really should get a firm background on how to create a
query before designing one.

Wave Insanityhttp://www.youtube.com/watch?v=8LoH6oQzJZs

Access doesn't recognize the Count Distinct function, does it?
Everywhere I have been reading, it says that I have to use a subquery
to use the Distinct function. Is this not true?

I have no idea what you are counting. Do you want a count of 1 for each
value of [Part No 2540]? Or do you want the count for each value in
[Part No 2540] with a priority of 1? If so...

SELECT Table1.Part, Count(Table1.Priority) AS CountOfPriority
FROM Table1
WHERE Table1.Priority)=1
GROUP BY Table1.Part;

If you simply want a count of priority 1s without regard to the part
value...
SELECT Count(Table1.Priority) AS CountOfPriority
FROM Table1
WHERE Table1.Priority=1

I really think you need to study how to create queries in the query builder.

Who Knew
http://www.youtube.com/watch?v=cJmghwq7k2I

[lyle fairfield]

Based on your first sentence:

Assuming "Part No 2450" is a field name?

SELECT [Part No 2540], Count(*) FROM RMAS WHERE Priority = 1 GROUP BY
[Part No 2540]

or if we want "[Part No] = 2450" then

SELECT Count(*), FROM RMAS WHERE Priority = 1 AND [Part No] = 2540

On Jun 16, 10:28*am, [e-mail] wrote:

I am trying to count the total number of units for a given part number
that have a Priority rating of 1. Based upon some research, this is
what I came up with for my query. Access says that I have invalid
bracketing in the Select Distinct statement. The only brackets I
inserted were to delimit the table's field names. Access did the rest.
Also, Access created the second AS bracket group - [%$##@_Alias].

Am I barking up the right tree or is my query statement way off base?
I am not sure where to put the WHERE clause and I don't understand the
bracketing error Access is giving.

Query Statement:
SELECT COUNT(*) AS ["Planned Ships"] FROM [SELECT DISTINCT [Part No
2540] FROM RMAs WHERE [Priority] =1] AS [%$##@_Alias] GROUP BY [Part
No 2540];

Thanks in advance.

Troy Lee

[troy_lee]

On Jun 16, 12:18 pm, lyle fairfield <[e-mail]:

Based on your first sentence:

Assuming "Part No 2450" is a field name?

SELECT [Part No 2540], Count(*) FROM RMAS WHERE Priority = 1 GROUP BY
[Part No 2540]

or if we want "[Part No] = 2450" then

SELECT Count(*), FROM RMAS WHERE Priority = 1 AND [Part No] = 2540

On Jun 16, 10:28 am, [e-mail] wrote:

I am trying to count the total number of units for a given part number
that have a Priority rating of 1. Based upon some research, this is
what I came up with for my query. Access says that I have invalid
bracketing in the Select Distinct statement. The only brackets I
inserted were to delimit the table's field names. Access did the rest.
Also, Access created the second AS bracket group - [%$##@_Alias].

Am I barking up the right tree or is my query statement way off base?
I am not sure where to put the WHERE clause and I don't understand the
bracketing error Access is giving.

Query Statement:
SELECT COUNT(*) AS ["Planned Ships"] FROM [SELECT DISTINCT [Part No
2540] FROM RMAs WHERE [Priority] =1] AS [%$##@_Alias] GROUP BY [Part
No 2540];

Thanks in advance.

Troy Lee

Thanks for all the replies and constructive criticisms. Please
understand that I am working with a database not originally created by
me- thus all the crazy field names. As for my question, I did do some
more research and have a better grasp on what I am trying to
accomplish. I got my query to work- (aggregate values for each Part
Number type with a Priority of 1).

This is the code found in SQL view of the Query Design window.

SELECT RMAs.[Part No 2540], RMAs.[Unit Type], Count(RMAs.[RMA #]) AS
[CountOfRMA #], RMAs.Priority
FROM RMAs
GROUP BY RMAs.[Part No 2540], RMAs.[Unit Type], RMAs.Priority
HAVING (((RMAs.Priority)=1));

Now, I want to create a calculated field called Total for all of the
units found by the query. I would like to do this within the same
query, but can't quite figure it out. BTW, the field that I am
currently counting, which is also the PK for the table, is a text
field.

For clarity's sake, the field name [Part No 2540] is in reference to
the fact that we preface all of our units by the first four digits
2540. The stored values for the unit types are an additional 4 digits
appended to this number.