Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be mich appreciated.
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
I have two tables, one being an 'Address' file and the other an 'Area' file which is a list columns ranging from 1-400 with groups of district level postcodes. For Example; column 1 will have postcodes such as BN1, BN2, BN3, BN4, BN5 etc. Each column has a separate group of postcodes.
Now, my 'Address' file also has a column within this labelled 'District' in order for me to create a straight join (between column number and District) in a query to count each group individually, but of course, do to this 400 times becomes quite tedious! (to say the least)
I have a query to count the number of society members in a particular UK Postcode area. It works well for a single Postcode reference but adding further references to the Query Design View generates an 'or' or 'and' statement, rather than the additional counts that I need.
The SQL code I use for a single reference is : SELECT Count([Mail List].[PostCode]) AS CountofPostCodeH FROM [Mail List] WHERE ((([Mail List].PostCode) Like "BN5 ???"));
How do I amend this code to produce counts for multiple references in one query.
Am trying to query a customer database to find a how many instances of each postcode exist to determine geographic distribution. Only interested in the first part of the postcode so the following code was suggested:
SELECT Count(owners.Field8) AS Total, Left$([Field8], Len([Field8])-3) AS Code FROM owners;
However 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.
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? :confused:
I need to know how to be able to make my database accept only postcode format entries like RT24 6QP (english). Like a validation , it would only accept that format. How would i go about doing that. Please explain to be in easy to understand contex as im no expert with access.
Hi, I am inputing addresses into a massive databse, through a form, does anyone know a way, that i can get it to do a search for the address based on the postcode, like how googlemaps does it or something?
I was wondering if anyone had any experience of integrating a product like QuickAddress (QAS) into an Access Database to aid the completion of address details. ThanksRussG
I have searched the forum, and not really found the answer I`m looking for. I used in my Database, a postcode field, which for 90% of the postcodes I input is is fine.
However, som of the postcodes dont conform to standard post codes.
I`ve found some info in this link, http://www.access-programmers.co.uk/forums/showthread.php?t=89393&highlight=links
but this didnt really help.
Most postcodes ( as set in the database properties ) confirm to thiis standard
SK01 1AA
Which if intered in this format works all OK.
However, some postcodes go in as fiollows
M01 1AA
Which leaves me a digit short, and messes up my search sections of the database, and the link to external map programs like M$ Mappoint.
Is there anyway that anyone knows of that will sort this out, and allow both entries withouht screwing up other programs.
This field is also a REQUIRED field, and must be a data field, or my external program links like M$ Map Point wont work.
hello all this thing is really bugging me im trying to come up with a post code validation and i have tried dozens, which should have worked really, but just havent so wondering if you guys knew any examples of postcodes would be
BN3 8JH BN41 8JH
theyre the only two types that are bound to be entered really
the first and second characters have to be a letters the 3rd has to be a number the 4th is sometimes a letter (not always present) then theres a space then there has to be a number letter letter
any suggestive codes would be great, as none of mine have worked its in access
I have a postcode field on a form that allows 8 characters. e.g. ST10 8BY including the space in the middle. The only validation I have been able to use thus far is the above but I would like to know how to validate two letters (AA) then between 1 and 2 numbers (11) etc.
Is there any code/built in functions that allows this. I know there is an input mask but if I use that, I can set the poscode format up like ST10 8BY but it thinks that the rule has been broken if the postcode was changed to S10 8BY by removing the T. This is also a valid postcode.
Just been asked this and not sure if it is possible...
they do not want to pay loads of money to the royal mail for their postcode/address data. but they want a faster way (like typing postcode and address options popup) of entering addresses. There are tons of websites that can be used to find addresses with a postcode, i was wondering if it was possible to somehow link the database to lookup address from a website...?
I've tried to steal an SQL Postcode validator (This is for UK postcodes) and add it to an AfterUpdate event on a field in MS Access. It doesn't work, obviously,The VBA code is:
Private Sub Postal_Code_AfterUpdate() Dim CHECK_Code As Integer Dim LResponse As Integer If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", Postal_Code) = 0 Then CHECK_Code = 0
[code]....
Also, how would I put the Check_Code value into the Message box to see what it's bringing back?
I guess this is pretty fundamental but I cant get it right! I want to search for a member by post code so I need to have validation for my table field post code so that when my parameter query accepts the post code input records will be returned covering all input types e.g. Users can put in CF72 (space)9AD or CF729AD and still get the same records or alternately how do I make users enter Post Codes with the correct UK spacing i.e 4 characters a space then the remaining characters?
The problem with UK postcodes is that there are 6 different formats. These are:
A9 9AA A99 9AA AA9 9AA AA99 9AA A9A 9AA AA9A 9AA
I've seen on similar threads where people have just forced the field into capitals, but my data requires that it is a valid postcode and I cant find an input mask that allows all UK postcode.
I have tried to find this on the forum but it seems all the answers are to do with manipulating the strings based on what the postcode is whereas I just want to extract the postcode as a separate string as simply as possible.
Basically, I have a text field called Add5 which has the last line of the address including the postcode.
Example: "Northampton NN1 7PQ"
I am trying to end up with two strings like this
Add5 = "Northampton" Postcode = "NN1 7PQ"
I only need to do this in one place, I don't think it needs a module.
Any way to extract a postcode when there are double space on some and not others.
SA1 1AB (i need SA1 1AB) HU1 2AB (i need HU1 2AB) HU17 0BG (ok)
For some reason the postcode field returns 2 spaces when the postcode length is only 6 digits and 3 spaces when the postcode length is only 5 digits. Im assuming that the postcode field needs to be 8 characters in length (including a space).
All I want to to remove the addition spaces and return a normal looking postcode!
I have a form which has a number of fields and 2 of them are called [Postcode] and [Cost].
The cost field is defaulted to "£75.00"
What I am trying to is after the user enters the postcode it looks at the first and second value and if they enter a postcode in Scotland like below it changes the cost field accordingly.
The changes will only effect postcodes in Scotland if that makes sense as we have 2 different pricing areas for these
examples of some postcodes AB1 1AU £125.00 EH1 1RT £85.00 G1 1AT £85.00 IV5 1ER £125.00 ML1 1RT £85.00 and so on
so any postcode containing the following would update
AB = 125 EH = 85 G = 85 IV = 125 ML = 85 DD = 85 FK = 125 etc etc
all other postcodes in the uk will show the default value of £75.00
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
how can i count the number of rows in a query. i have a query that return me number of rows. i want to count the rows. when i try to count it, it gave me a column that count only her column. which mean in every row it counts me '1'. i want to count all the rows.
