Postcode 'clipping' 1

[EvilTwinOfAtrus]

Hi All

I'm trying to run a query which tells me how many houses are on our database in each postcode (using the COUNT record function on 'HouseID' and the "WHERE PostCode1 is between x and y"). The postcode on the database is split into PostCode1 and PostCode2 for the 1st and 2nd part of the postcode. We will just be using PostCode1 for this exercise (eg LN6, E4, NW5 etc etc).

Ok, here's the problem - I need the report to tell me how many houses are in a complete postcode area instead of the separate numbers (ie I dont want to know how many houses are in LN1, LN2, LN3 etc etc, i just want to know how many are in LN in total.) How can I do this without having to enter postcodes individually into a WHERE Postcode1 is Between x And y routine? (basically i want the output to be the list of postcodes with the count of houses in each next to them.

If this is not possible in Access, is there a way you can split out the results in Excel, as this will be the final report output?

Sorry for any confusion, you're help would be very much appreciated!

 

[lupins46]

Postcodes are wretched things to deal with because of the variety of formats.
I think what you can do in this case is use the following Where clause.

The query will then ask you for the value and you can enter one or two characters
select * from mytable
where pc1 like [enter pc] & "*"

But the problem is that if you enter N, say, then you do not restrict the selections to N10, N8 etc but it will also include NN1, NG23 and so on.

 

[EvilTwinOfAtrus]

Hmm, I'd already thought of that but it avoids the point, I don't want to keep typing in postcodes, I just want a list of them with the count next to it.

Thanks for your help. Any other ideas? Anyone? ^_^

 

[sha76]

As on a single character anything >9 will be a letter how about:

IIf(Mid(pc1,2,1)>9, Left(pc1,2), Left(pc1,1))

as an extra expression in your query?

"Your rock is eroding wrong." -Dogbert

 

[EvilTwinOfAtrus]

sha76, can you make that a bit more friendly for me. I'm not too hot on thinking straight at the moment with my holiday next week hehe.

Sorry.

Thankyou,
EToA

 

[EvilTwinOfAtrus]

It's ok actually I've worked it out and adapted it. Thankyou so much for your help

- EToA

 

[sha76]

In the design view of your query:

Insert a column to the far left, copy & paste

PostcodeArea:IIf(Mid(pc1,2,1)>9, Left(pc1,2), Left(pc1,1))

(I'm assuming pc1 is your field name, you'll need to change that if not. If there's more than one table in your query you'll need to use TableName!pc1 and don't forget to put any field or table names with spaces in [])

into the "Field:" box. Tick to show. Keep the "Total:" box as Group by.

Does this give you what you're after? If it gives you something, but not what you want, then post back with the problem, if it gives you nothing at all then post the SQL for the query.



"Your rock is eroding wrong." -Dogbert

 

[sha76]

No probs, I've spent all morning doing evil things with postcodes!

"Your rock is eroding wrong." -Dogbert