Aggregate help

[troy_lee@comcast.net]

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]

Re: Aggregate help

<troy_lee@comca st.netwrote in message
news:a54372a8-85d1-42c9-add1-732977225805@26 g2000hsk.google groups.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.

https://www.keithwilby.com

[Salad]

Re: Aggregate help

troy_lee@comcas t.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@comcast.net]

Re: Aggregate help

On Jun 16, 10:58 am, Salad <o...@vinegar.c omwrote:

troy_...@comcas t.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 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=8LoH6oQ zJZs

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]

Re: Aggregate help

troy_lee@comcas t.net wrote:

On Jun 16, 10:58 am, Salad <o...@vinegar.c omwrote:
>

>>troy_...@comc ast.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 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=8LoH6oQ zJZs

>
>
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.Pr iority) 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.Pr iority) 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]

Re: Aggregate help

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, troy_...@comcas t.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

[troy_lee@comcast.net]

Re: Aggregate help

On Jun 16, 12:18 pm, lyle fairfield <lyle.fairfi... @gmail.comwrote :

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, troy_...@comcas t.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

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.Priorit y)=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.