By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Aggregate help

P: n/a
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
Jun 27 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
<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

Jun 27 '08 #2

P: n/a
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
Jun 27 '08 #3

P: n/a
On Jun 16, 10:58 am, Salad <o...@vinegar.comwrote:
troy_...@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 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?
Jun 27 '08 #4

P: n/a
tr******@comcast.net wrote:
On Jun 16, 10:58 am, Salad <o...@vinegar.comwrote:
>>troy_...@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 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
Jun 27 '08 #5

P: n/a
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_...@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
Jun 27 '08 #6

P: n/a
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_...@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
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.
Jun 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.