Query on part of a postcode field

[shopoholic]

Still need help with query on part of a postcode field

Hello all!

I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.

What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.

Does anyone have any suggestions of how I can do this?

Any help most gratefully received!

 

[andrew93]

Hello

You can extract the part of the postcode before the space with a new field in your query like this :

Left([PostCode],InStr([PostCode]," ")-1)

Make sure you use your actual field name where I have assumed 'PostCode'. You can then group by this field in a totals query and count the number of customer names (using Count instead of Group By).

HTH, Andrew

 

[shopoholic]

thanks for the advice, but unfortunately there is no space in the postcode field, its just one string of characters.

Any other ideas?

 

[Mile-O]

The last part of a post code is always three characters. Once you eliminate these then you have the left side of a post code.

=Left$([PostCode], Len([PostCode]) - 3)

 

[shopoholic]

good point - thank you!

Just one more (perhaps dumb) question: where do I put that in my query? I have tried looking on the Help files on Access and although they both contain info about the Left and Len funtions, they do not indicate where to use them.

I have tried this (please don't laugh if its hiddeously wrong!):

SELECT Count(owners.field8) AS Total
FROM owners
WHERE owners.Field8=Left$([Field8], Len([Field8])-3);

Ps - Field8 is the postcode field (I didn't create this database!)

 

[Mile-O]

SELECT Count(owners.field8) AS Total, Left$([Field8], Len([Field8])-3) AS Code;
FROM owners;

 

[shopoholic]

Hmm....

Tried your code but it returned a message saying "you tried to execute a query that does not include the specified expression 'Left$([Field8], Len([Field8])-3)' as part of an aggregate funtion.

What does this mean?

 

[andrew93]

Can you post your SQL for this query? You can get to it when in the query design screen by clicking on View -> SQL View.
Andrew

 

[Mile-O]

Hmm....

Tried your code but it returned a message saying "you tried to execute a query that does not include the specified expression 'Left$([Field8], Len([Field8])-3)' as part of an aggregate funtion.

What does this mean?

It means you are trying to perform this function on a group summarised by one of the aggregated functions (Sum, Max, Avg, etc.) so that something is being grouped and the expression I've given works on a record by record basis and not on aggregated data.