Counting post code incodes

  • Thread starter Thread starter gavin
  • Start date Start date
G

gavin

I have a database of name and addresses and I want to count how many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode, Count(*) AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time when I run the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I can see the two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code field? If
so, is there a way of getting round that?



Thanks for any help,



Gavin
 
Hi Gavin,

The first part is the outcode, not the incode.

The problem is probably caused by records that are null or that don't
contain a space as or after the second character. Use a WHERE condition
to exclude these (or a more complicated one if you want to include
records that have an outcode but no incode). This works in my test
database:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;



I have a database of name and addresses and I want to count how many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode, Count(*) AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time when I run the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I can see the two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code field? If
so, is there a way of getting round that?



Thanks for any help,



Gavin
Click to expand...
 
Many thanks to John Nurick for the reply. You are absolutely right - I did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin
 
I was hoping a kind soul could give me a little more help with this?

John's solution of:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;

works perfectly. But I was wondering how I could tweak this to count the
outcodes but only looking at the letters, not the numbers. So for example,
M27, M44 and M6 would all be counted as Ms. Of course the letter part of the
outcode can be either one or two characters.

Thanks in advance for your help.



Gavin



gavin said:
Many thanks to John Nurick for the reply. You are absolutely right - I did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin


gavin said:
I have a database of name and addresses and I want to count how many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode, Count(*) AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time when I run the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I can see the two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code field? If
so, is there a way of getting round that?



Thanks for any help,



Gavin
Click to expand...
Click to expand...
 
Is it always a single letter? If so,

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



gavin said:
I was hoping a kind soul could give me a little more help with this?

John's solution of:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;

works perfectly. But I was wondering how I could tweak this to count the
outcodes but only looking at the letters, not the numbers. So for example,
M27, M44 and M6 would all be counted as Ms. Of course the letter part of the
outcode can be either one or two characters.

Thanks in advance for your help.



Gavin



gavin said:
Many thanks to John Nurick for the reply. You are absolutely right - I did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin


gavin said:
I have a database of name and addresses and I want to count how many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode, Count(*) AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time when I
Click to expand...
run
the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I can see the two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code
Click to expand...
field?
If
so, is there a way of getting round that?



Thanks for any help,



Gavin
Click to expand...
Click to expand...
Click to expand...
 
Alas, some have one and some two letters. Perhaps

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "[A-Z][0-9]*"
GROUP BY Left([postcode],1)
UNION
SELECT
Count(PostCode) AS Entries,
Left([postcode],2) AS Incode
FROM AddrNew
WHERE PostCode LIKE "[A-Z][A-Z][0-9]*"
GROUP BY Left([postcode],2)
;


Is it always a single letter? If so,

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],1)
Click to expand...
 
It can be either one or two letters. Thanks to everyone for your help in
this matter - much appreciated!!!


Gavin




Douglas J. Steele said:
Is it always a single letter? If so,

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



gavin said:
I was hoping a kind soul could give me a little more help with this?

John's solution of:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;

works perfectly. But I was wondering how I could tweak this to count the
outcodes but only looking at the letters, not the numbers. So for example,
M27, M44 and M6 would all be counted as Ms. Of course the letter part of the
outcode can be either one or two characters.

Thanks in advance for your help.



Gavin



gavin said:
Many thanks to John Nurick for the reply. You are absolutely right - I did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin


I have a database of name and addresses and I want to count how many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode,
Click to expand...
Count(*)
AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time when I run
the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I can see
Click to expand...
the
two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code field?
If
so, is there a way of getting round that?



Thanks for any help,



Gavin
Click to expand...
Click to expand...
Click to expand...
 
John Nurick suggested this which works great:

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE


"[A-Z][0-9]*"
GROUP BY Left([postcode],1)
UNION
SELECT
Count(PostCode) AS Entries,
Left([postcode],2) AS Incode
FROM AddrNew

WHERE PostCode LIKE "[A-Z][A-Z][0-9]*"
GROUP BY Left([postcode],2)

;
However, when I go to the database window and click on Queries the query
that this SQL creates has symbol to the left of the query name - the symbol
is like two interlinked circles. What does this mean?Gavin"gavin"
It can be either one or two letters. Thanks to everyone for your help in
this matter - much appreciated!!!


Gavin




Douglas J. Steele said:
Is it always a single letter? If so,

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



gavin said:
I was hoping a kind soul could give me a little more help with this?

John's solution of:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;

works perfectly. But I was wondering how I could tweak this to count the
outcodes but only looking at the letters, not the numbers. So for example,
M27, M44 and M6 would all be counted as Ms. Of course the letter part
Click to expand...
of
the
outcode can be either one or two characters.

Thanks in advance for your help.



Gavin



Many thanks to John Nurick for the reply. You are absolutely right -
Click to expand...
I
did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin


I have a database of name and addresses and I want to count how many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode, Count(*)
AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time when
Click to expand...
I
run
the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I can see the
two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code field?
If
so, is there a way of getting round that?



Thanks for any help,



Gavin
Click to expand...
Click to expand...
Click to expand...
 
That's normal: it simply means you've got a UNION query.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


gavin said:
John Nurick suggested this which works great:

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE


"[A-Z][0-9]*"
GROUP BY Left([postcode],1)
UNION
SELECT
Count(PostCode) AS Entries,
Left([postcode],2) AS Incode
FROM AddrNew

WHERE PostCode LIKE "[A-Z][A-Z][0-9]*"
GROUP BY Left([postcode],2)

;
However, when I go to the database window and click on Queries the query
that this SQL creates has symbol to the left of the query name - the symbol
is like two interlinked circles. What does this mean?Gavin"gavin"
It can be either one or two letters. Thanks to everyone for your help in
this matter - much appreciated!!!


Gavin




Douglas J. Steele said:
Is it always a single letter? If so,

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I was hoping a kind soul could give me a little more help with this?

John's solution of:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;

works perfectly. But I was wondering how I could tweak this to count the
outcodes but only looking at the letters, not the numbers. So for example,
M27, M44 and M6 would all be counted as Ms. Of course the letter
Click to expand...
Click to expand...
part
of
Click to expand...
right -
I
did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin


I have a database of name and addresses and I want to count how many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode, Count(*)
AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time
Click to expand...
Click to expand...
when
I see
the
Click to expand...
Click to expand...
 
If I go to Design View I can't amend the query design - I guess that's meant
to happen, but can anyone explain why? I assume I would be able to amend the
SQL - trouble is, I'm not very good with SQL (time to learn!) :-)



Gavin




Douglas J. Steele said:
That's normal: it simply means you've got a UNION query.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


gavin said:
John Nurick suggested this which works great:

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE


"[A-Z][0-9]*"
GROUP BY Left([postcode],1)
UNION
SELECT
Count(PostCode) AS Entries,
Left([postcode],2) AS Incode
FROM AddrNew

WHERE PostCode LIKE "[A-Z][A-Z][0-9]*"
GROUP BY Left([postcode],2)

;
However, when I go to the database window and click on Queries the query
that this SQL creates has symbol to the left of the query name - the symbol
is like two interlinked circles. What does this mean?Gavin"gavin"
It can be either one or two letters. Thanks to everyone for your help in
this matter - much appreciated!!!


Gavin




Is it always a single letter? If so,

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I was hoping a kind soul could give me a little more help with this?

John's solution of:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;

works perfectly. But I was wondering how I could tweak this to
Click to expand...
count
the
outcodes but only looking at the letters, not the numbers. So for
example,
M27, M44 and M6 would all be counted as Ms. Of course the letter
Click to expand...
part
of
the
outcode can be either one or two characters.

Thanks in advance for your help.



Gavin



Many thanks to John Nurick for the reply. You are absolutely
Click to expand...
right -
I
did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin


I have a database of name and addresses and I want to count
Click to expand...
how
many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using this SQL:

SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode,
Count(*)
AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this time
Click to expand...
when
I
run
the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I can see
the
two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code
field?
If
so, is there a way of getting round that?



Thanks for any help,



Gavin
Click to expand...
Click to expand...
Click to expand...
 
When you open the query in Design mode, you should be presented with the SQL
which you can then change.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



gavin said:
If I go to Design View I can't amend the query design - I guess that's meant
to happen, but can anyone explain why? I assume I would be able to amend the
SQL - trouble is, I'm not very good with SQL (time to learn!) :-)



Gavin




Douglas J. Steele said:
That's normal: it simply means you've got a UNION query.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


gavin said:
John Nurick suggested this which works great:

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE


"[A-Z][0-9]*"
GROUP BY Left([postcode],1)
UNION
SELECT
Count(PostCode) AS Entries,
Left([postcode],2) AS Incode
FROM AddrNew

WHERE PostCode LIKE "[A-Z][A-Z][0-9]*"
GROUP BY Left([postcode],2)

;
However, when I go to the database window and click on Queries the query
that this SQL creates has symbol to the left of the query name - the symbol
is like two interlinked circles. What does this mean?Gavin"gavin"
It can be either one or two letters. Thanks to everyone for your
Click to expand...
Click to expand...
help
in
this matter - much appreciated!!!


Gavin




Is it always a single letter? If so,

SELECT
Count(PostCode) AS Entries,
Left([postcode],1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I was hoping a kind soul could give me a little more help with this?

John's solution of:

SELECT
Count(PostCode) AS Entries,
Left([postcode],InStr([postcode]," ")-1) AS Incode
FROM AddrNew
WHERE PostCode LIKE "?* ?*"
GROUP BY Left([postcode],InStr([postcode]," ")-1)
;

works perfectly. But I was wondering how I could tweak this to count
the
outcodes but only looking at the letters, not the numbers. So for
example,
M27, M44 and M6 would all be counted as Ms. Of course the letter part
of
the
outcode can be either one or two characters.

Thanks in advance for your help.



Gavin



Many thanks to John Nurick for the reply. You are absolutely right -
I
did
mean OUTCODES!!! I really appreciate your solution to my problem.


Regards,



Gavin


I have a database of name and addresses and I want to count how
many
instances of each incode (the bit before the space) there are.

I already have this working in a separate database using
Click to expand...
this
SQL:
SELECT Left$([post_code],InStr([post_code]," ")-1) AS Incode,
Count(*)
AS
Total
FROM tblcustomer
GROUP BY Left$([post_code],InStr([post_code]," ")-1)

In my new database I have done exactly the same but this
Click to expand...
time
when
I
run
the
query I am getting this error:

"Data type mismatch in criteria expression"

Can anybody help me figure out what is wrong. As far as I
Click to expand...
can
see
the
two
databases are the same (but obviously they're not!!!).

Also would it matter if there were blank records in the post code
field?
If
so, is there a way of getting round that?



Thanks for any help,



Gavin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top