query to find postcodes

[marc aalders-dunthorne]

Hi there,



i was just wondering if there was something i would be able to do to speed the process that im doing at the minute up. iv been give millions of records that oi now need to split into postcode areas,how would i do this ? at the minute iv got one query that im changing every time to search for a different postcode but i have to search for 33 postcodes in 12 different tables. i didn't know if there was any way for access to use one query to search through all 12 tables and 33 different postcodes and give a result page of how many people are in each postcode that i have got on my database.

[orange]

I added a modified universal search to the code repository here. It will work for field values in multiple tables, but I wouldn't suggest if for millions of records.
You could copy the database, and put a limit of say 5000 records or some number to see what the performance is like. I'd like to know how it works with real volume.

You can use a user defined function to do repeated things.

It would be helpful if you could post a copy of your database -- remove confidential/private info first. We don't need a million records for testing.

[marc aalders-dunthorne]

I added a modified universal search to the code repository here. It will work for field values in multiple tables, but I wouldn't suggest if for millions of records.
You could copy the database, and put a limit of say 5000 records or some number to see what the performance is like. I'd like to know how it works with real volume.

You can use a user defined function to do repeated things.

It would be helpful if you could post a copy of your database -- remove confidential/private info first. We don't need a million records for testing.

thanks for the quick reply but id don't really understand what you mean here. i may not have explained my self fully. i cant upload any of the data as it is all confidential so i will try to explain some more. i have a 12 tables in each they have people contact information including postcodes. i need to separate the postcodes to then find out how many people are in each postcode. so when i do what ever i need to do make this it would show me how many records of data i have within each postcode area like postcodes starting with E. at the minute im doing it manually by creating a query importing one table selecting postcode and in the criteria field writing - like "E*****". and then seeing how much pieces of data that match the searchand then importing the amount of people into an excel sheet for example when i ran that it gave me 3822 records which i then just made an excel sheet so i could keep a note of how many people are in each set of postcodes. hopefully this makes sense and you can help me out

[orange]

Show me the design of one table (take a screen capture of the table design view).
Where you located? What postal code system are you using.
It would be helpful if you could export some fields in the table including the postal code as a csv file that I could load.
Just enough fields to simulate your problem data -- no need for names.

[marc aalders-dunthorne]

thanks for the quick reply but id don't really understand what you mean here. i may not have explained my self fully. i cant upload any of the data as it is all confidential so i will try to explain some more. i have a 12 tables in each they have people contact information including postcodes. i need to separate the postcodes to then find out how many people are in each postcode. so when i do what ever i need to do make this it would show me how many records of data i have within each postcode area like postcodes starting with E. at the minute im doing it manually by creating a query importing one table selecting postcode and in the criteria field writing - like "E*****". and then seeing how much pieces of data that match the searchand then importing the amount of people into an excel sheet for example when i ran that it gave me 3822 records which i then just made an excel sheet so i could keep a note of how many people are in each set of postcodes. hopefully this makes sense and you can help me out

ok so i have attached some of the postcodes that i have with what i want the output to look like. i want it so that i can search for all the postcodes on the left of the image through the 12 files with ease instead of manually searching for each one and transferring the outcome into excell. thanks for the help marc

[orange]

Interesting, but what do the outputs represent? You gave a list of postal codes, but no data to search, group display..?
Also, your postal codes have several characters --- mot=re than in your desired output.

Need details about the search/matching and some sample data.

[marc aalders-dunthorne]

Interesting, but what do the outputs represent? You gave a list of postal codes, but no data to search, group display..?
Also, your postal codes have several characters --- mot=re than in your desired output.

Need details about the search/matching and some sample data.

hi all im doing is in a query selecting postcodes in the criteria writing "like "E*****"" which then finds all the postcode that start with E then in the table that shows the output i see how many postocdes that match the critera and the put the result in the table. for example if you put the postcodes into an acess table and then run a query like i have just explained it will findyou all the postcodes with the criteria which is then what i put into excel i have tried to take screen shots of what im doing/ want to happen

[orange]

OK. In plain English what does your query really represent?

There may be ways to automate somethings, but you're going to have to provide something to work with...home and mobile etc.

You need a plan or strategy if you want to reduce the manual effort, BUT we can't continue to guess at WHAT you're trying to do.

You should be aware that your query with Like E* will not use any indexing and will be relatively slow.

[June7]

If you want to know how many records are assigned to each postal code letter prefix (A*, B*, C*, etc) can do an aggregate query that groups on that character position.

SELECT Left([PostCode],1) AS PostGroup, Count(*) AS CountRecs FROM tablename GROUP BY Left([PostCode],1);

If you need to group by single letter for some records and two letters (SE, SW, AL, etc) for others, this gets complicated.

If you want to show detail information as well as summary calcs, then build a report.

[marc aalders-dunthorne]

OK. In plain English what does your query really represent?

There may be ways to automate somethings, but you're going to have to provide something to work with...home and mobile etc.

You need a plan or strategy if you want to reduce the manual effort, BUT we can't continue to guess at WHAT you're trying to do.

You should be aware that your query with Like E* will not use any indexing and will be relatively slow.

ok so what i am doing is searching the information that i have got. then im looking to see how many records meet the criteria. and then im taking the amount of records that match the criteria and putting it into an excel sheet. that is what im doing at the minute manually on all 33 postcodes and 12 different files. i want to know if there is a way that access can do that at once. you wouldn't need any more data as that is all im running my query on the postcode.

[June7]

Did you read post 9?

Why are there 12 tables? Are they identical in structure?

What do the 3 number fields in your example output represent?

Consider:

SELECT Left([PostCode], IIf(IsNumeric(Mid([PostCode],2,1)),1,2) AS PostGroup, Count(*) AS CountRecs FROM tablename GROUP BY Left([PostCode], IIf(IsNumeric(Mid([PostCode],2,1)),1,2);

[orange]

Further to June's questions, it is the "... my query..." in

i want to know if there is a way that access can do that at once. you wouldn't need any more data as that is all im running my query on the postcode.

.

So you have a whole bunch of postal codes that you want to query. But until we get some hint of WHAT you are try to find with the query, I have no idea what your issue is. How do you query postal codes of 6-8 chars in length and get values like OX 632 - without hinting or suggesting to the reader what this represents??