Query Question

I am trying to create a query that will pull data in where the following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's, d's, and so
on. My query currently contains a column that counts the results but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I hope I am
communicating this so it is understandable.

Rather than using the actual data in the column that has the a1, a3, b3, etc,
use only the part that you want to group by. So, in the query builder where
you have the field name (lets call it [TheField] for example sake), use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's, d's, and so
on. My query currently contains a column that counts the results but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I hope I am
communicating this so it is understandable.

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3, b3, etc,
use only the part that you want to group by. So, in the query builder where
you have the field name (lets call it [TheField] for example sake), use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's, d's, and so
on. My query currently contains a column that counts the results but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I hope I am
communicating this so it is understandable.

Since I can't see your actual code, it is hard to tell. Let me go over it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3, b3, etc,
use only the part that you want to group by. So, in the query builder where
you have the field name (lets call it [TheField] for example sake), use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's, d's, and so
on. My query currently contains a column that counts the results but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I hope I am
communicating this so it is understandable.

Alright, well I did it again and I am still getting the same message which is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3, b3, etc,
use only the part that you want to group by. So, in the query builder where
you have the field name (lets call it [TheField] for example sake), use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's, d's, and so
on. My query currently contains a column that counts the results but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I hope I am
communicating this so it is understandable.

Probably a references issue as Klatuu suggested. You also seem to have some
weird bracketing with this expression
Left([MasterAvailabilityData.PMCode],1)
I would expect to see
Left([MasterAvailabilityData].[PMCode],1)
or
Left(MasterAvailabilityData.PMCode,1)

For references help, check http://www.allenbrowne.com/ser-38.html
--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

Try this --
SELECT Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005") AND
((MasterAvailabilityData.CustAcct)="802670398"))
GROUP BY Left([MasterAvailabilityData.PMCode],1);


"Lucien" wrote:

Alright, well I did it again and I am still getting the same message which is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3, b3, etc,
use only the part that you want to group by. So, in the query builder where
you have the field name (lets call it [TheField] for example sake), use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's, d's, and so
on. My query currently contains a column that counts the results but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I hope I am
communicating this so it is understandable.

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

I tested her syntax for the date on a date field, and it works correctly.
The bracketing is certainly a problem, but that should not cause the Left
Function to fail. I think she has a reference problem.

"Duane Hookom" wrote:

Is the OrderDate field a real date or is it a text field. If it is a
date/time field, you should treat it like a string.

--
Duane Hookom
MS Access MVP
--

"Lucien" wrote in message
...
Alright, well I did it again and I am still getting the same message which
is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over
it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give
it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field
name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be
a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate
window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a
referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from
there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3,
b3, etc,
use only the part that you want to group by. So, in the query
builder where
you have the field name (lets call it [TheField] for example sake),
use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the
following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's,
d's, and so
on. My query currently contains a column that counts the results
but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I
hope I am
communicating this so it is understandable.

Karl, using your code, I still get the same error message:

"Undefined function 'Left' in expression"




"KARL DEWEY" wrote:

Try this --
SELECT Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005") AND
((MasterAvailabilityData.CustAcct)="802670398"))
GROUP BY Left([MasterAvailabilityData.PMCode],1);


"Lucien" wrote:

Alright, well I did it again and I am still getting the same message which is:

"Undefined function 'Left' in expression"

Here is my code .... hopefully you can tell me what is wrong.

SELECT MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1) AS GrpValue,
Count(MasterAvailabilityData.PMCode) AS CountOfPMCode,
Avg(MasterAvailabilityData.LineAvailPassFail) AS AvgOfLineAvailPassFail
FROM MasterAvailabilityData
WHERE (((MasterAvailabilityData.OrderDate) Like "12/*/2005"))
GROUP BY MasterAvailabilityData.CustAcct,
Left([MasterAvailabilityData.PMCode],1)
HAVING (((MasterAvailabilityData.CustAcct)="802670398"));




"Klatuu" wrote:

Since I can't see your actual code, it is hard to tell. Let me go over it
again and see if I left out anything.

First, in your query builder at the bottom, there is a row called Fields.
Usually, you put the name of the field in one of the columns of this row.
Since we want only the first character, we create an expression. We give it
a name followed by a :

So, instead of putting [TheField] in that cell we would put

AName: [TheField]
This would still use the entire field, but now in the query the field name
becomes AName.

Now to include only the 1st character, it would be:

AName: Left([TheField], 1)

If this is what you are doing, and it is getting the error, there may be a
reference problem in your database. If you get the same error after
verifying you have coded it correctly, try doing this in the immediate window
of your VB Editor:

?Left("ABC",1)
If it print back A, then there is a syntax problem in your query. If you
get a message box with Sub or Function not defined, then you have a referencr
problem.

Post back with EXACTLY what you put in the cell so we can take it from there.

"Lucien" wrote:

Ok, I tried this and Access gives me an error message:

"Undefined function 'Left' in expression"

Can you tell by this what I did wrong?




"Klatuu" wrote:

Rather than using the actual data in the column that has the a1, a3, b3, etc,
use only the part that you want to group by. So, in the query builder where
you have the field name (lets call it [TheField] for example sake), use an
expression like this:

GrpValue: Left([TheField],1)

So, in that column, instead of getting a1, a3, b3, etc, you will get
a, b, e

"Lucien" wrote:

I am trying to create a query that will pull data in where the following is
in the table:

a1
a3
b2
b3
b6
e1
e3
etc.

I want all of the A's to be grouped together, all the b's, c's, d's, and so
on. My query currently contains a column that counts the results but it is
counting all the a1 lines, all the a3 lines, etc. seperately. I hope I am
communicating this so it is understandable.