By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,859 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Sort customers by postcode and print reports .

P: n/a
Hi,
I am a very beginner in databases. I created a database table in Access 2003
and OOo 2.03 that includes name, address, postcode, phone numbers etc of our
customers. I would like to sort customers by areas of London, UK by the
first part of the postcode. The London postcodes are the form of E19 4PR,
NW5U 4RT. So I would like to print a report (customers arranged into Word
tables - normal tables, not database tables) for example which includes the
following zones of London: North East, East and South London and which are
represented by the following postcodes E(1-18), SE(1-29), SW(1-29).
Thanks,
Julian
Aug 3 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"Julian" <x@x.xwrote in message news:4j************@individual.net...
Hi,
I am a very beginner in databases. I created a database table in Access
2003
and OOo 2.03 that includes name, address, postcode, phone numbers etc of
our
customers. I would like to sort customers by areas of London, UK by the
first part of the postcode. The London postcodes are the form of E19 4PR,
NW5U 4RT. So I would like to print a report (customers arranged into Word
tables - normal tables, not database tables) for example which includes
the
following zones of London: North East, East and South London and which are
represented by the following postcodes E(1-18), SE(1-29), SW(1-29).
It is relatively simple to include a Calculated Field in your Query that
returns a "sort order" value for the postal codes as you describe. It is
not clear to me exactly the order you desire, so I won't suggest a
particular algorithm, but you'll use embedded IIFs.

It is also not clear to me what your goal is for "(customers arranged into
Word tables - normal tables, not database tables)." Unless you plan to
manually modify the output, there is not always an advantage in doing so. I
have not had exceptionally good luck with formatting being well-preserved
when I export Reports to "Word format" (actually RTF) and using COM
Automation to build Report output with Word can require a lot more work than
creating Access Reports.

If, however, you are going to create Access Reports, then you will use the
Report's Sorting and Grouping properties to define the sorting and grouping.

Larry Linson
Microsoft Access MVP

Aug 3 '06 #2

P: n/a
Your sorting is a issue of your report generator, or your query builder.

From your question, it sounds like you are not using ms-access at all..but
just opening the file for use...

the above being the case, you will need to ask your question in a oo
newsgroup. This sorting issue will be that of your
query builder, or report generator used...in your case oo

If you actually are building the queries and reports in ms-access, then
simply use the sorting and grouping option of the report...

If you are using a differnt product to build the reprots..then consult the
help/manuals for that product, or try their newsgroup..
--
Albert D. Kallal
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 3 '06 #3

P: n/a
It is relatively simple to include a Calculated Field in your Query that
returns a "sort order" value for the postal codes as you describe. It is
not clear to me exactly the order you desire, so I won't suggest a
particular algorithm, but you'll use embedded IIFs.
I would like to sort out customers by zones North, East etc, zones which are
defined by various postcodes. For example the North zone would be defined by
N(1-20), WD(1-24), NW(1-10). So, how do I make a query like: if the
customer's postcode starts with N(1-20), WD(1-24), NW(1-10), than sort them
into the North zone customers. Then create a report with these customers
that looks like a table (the one with rows and columns and borders) created
in Word, so I can print it. See what I mean at: ftp://87.112.95.111/
user: anonymous, no password.

It is also not clear to me what your goal is for "(customers arranged into
Word tables - normal tables, not database tables)." Unless you plan to
manually modify the output, there is not always an advantage in doing so.
I
have not had exceptionally good luck with formatting being well-preserved
when I export Reports to "Word format" (actually RTF) and using COM
Automation to build Report output with Word can require a lot more work
than
creating Access Reports.

If, however, you are going to create Access Reports, then you will use the
Report's Sorting and Grouping properties to define the sorting and
grouping.

See above.

Julian
Aug 4 '06 #4

P: n/a
I use Access. See my reply above.
Aug 4 '06 #5

P: n/a

"Julian" <x@x.xwrote in message news:4j************@individual.net...
>I use Access. See my reply above.
ok...just not clear what the reference and information about oo was all
about...it just serves to confuse the issue, or introduce extra complexities
here. (what does oo have to do this problem then????).

Anyway....

The first problem you need to tackle is that of stripping out the postal
codes. You don't mention if your files are large, or small.

If your data file is only 5000-8000 records, then you likely can process the
records in place. If you have more records, then I would introduce two new
fields into the database.

Region
This field would hold the region

eg: E19 4PR, we set region = E
NW5U 4RT. we set region = NW
etc. etc. etc.

I would then create a another field called RegionN
RegionN
This field would hold the number, such as

eg: E19 4PR, we set RegionN = 19
NW5U 4RT. we set RegionN = 5
etc. etc. etc.

Once we do the above, then we would be able to obtain decent performance.
the above type of string processing could be done for each query, but that
would make for difficult queries.

So, I would add the two fields, and then run a update query to pull/put in
the region values into that new region field in the table....

I would then run another update query to put in the regionNUM vales into
that

Once the above is done, then the query you ask for would be easier to
write....eg
E(1-18), SE(1-29), SW(1-29)

(Region = "E" and (RegionN between 1 and 18)
or
(Region = "SE" and (RegionN between 1 and 29)
OR
(Region = "SW" and (RegionN between 1 and 29)

you can well see how much more easy our problem becomes when we split out
the post codes as above....

It is not clear if this data file of yours is being constantly updated.
further, are the post codes ALWAYS consistent in how they are entered (if
they are not..then trying to do processing on data that is not in your
example format is not going to work).

Anyway, the above is the general idea here. It is not known how large your
files are, but adding those two extra fields, and processing out the data
into those two fields would make this problem DRAMATICALLY easier. And, this
approach would also ensure good performance when building a query.

to do this type of data processing you are going to need some software
skills here. Lets take a crack at this...

The first routine we need is one that grabs the region value up to the first
number

The 2nd routine we need is one that grabs the region "number"

Here is the 3 routines needed. You can paste them into a standard code
module..and save it...

Public Function regionchar(s As Variant) As Variant

If IsNull(s) Then Exit Function

If s Like "[A-Z][A-Z]*" = True Then

regionchar = Left(s, 2)

Else

If s Like "[A-Z]*" = True Then
regionchar = Left(s, 1)
End If
End If

End Function
Public Function regioncharN(s As Variant) As Variant

If IsNull(s) Then Exit Function

If s Like "[A-Z][A-Z]*" = True Then

regioncharN = OnlyNumbers(Mid(s, 3))

Else

If s Like "[A-Z]*" = True Then
regioncharN = OnlyNumbers(Mid(s, 1))
End If
End If
End Function

Public Function OnlyNumbers(s As Variant) As String

Dim i As Integer
Dim mych As String

OnlyNumbers = ""

If IsNull(s) = False Then

i = 1
For i = 1 To Len(s)
mych = Mid$(s, i, 1)
If InStr("0123456789", mych) 0 Then
OnlyNumbers = OnlyNumbers & mych
Else
Exit For
End If
Next i
End If
End Function

Now, it is just a simple matter to run two udpates on the file to set our
two new fields we created

Region (text)
RegionNum (number - integer)

update tblCustomers set Region = RegionChar([PostalCode]

Run the above in the query bulder...
and, also

update tblCustomers set RegionNum = RegionCharN([PostalCode]

Once we done the above, we can now build a query based on our conditions
quite easy.

Send this query results to a report, or even select, and cut/paste into your
word document....

and, you might find the formating in word works better if you export the
query to excel first...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 4 '06 #6

P: n/a
I am having to interpret your partial example, because you didn't provide a
_definition_ of how zone and postal code are related. It's entirely possible
that my interpretation of your partial example was too simplistic and that
you may need to use a function to determine the zone.

But using the following SQL I put together (entirely in the Query Builder)
and some test data I concocted, with the following RecordSource for a Report
(with relatively little reformatting from the wizard's "tabular" report to
resize and reposition the text boxes, and to change their border from
transparent to solid), I created a report selecting specific people by
postcode that almost identically matches the template you posted.

And it was done entirely in Access.

Here's the SQL I used:

SELECT tblNameAndPostCodeBritish.FullName, tblNameAndPostCodeBritish.Phone,
tblNameAndPostCodeBritish.Address, tblNameAndPostCodeBritish.PostCode,
tblNameAndPostCodeBritish.Obs FROM tblNameAndPostCodeBritish WHERE
(((Left([PostCode],2))="NW" Or (Left([PostCode],2))="WD")) OR
(((Left([PostCode],1))="N"));

You'll note I used "FullName" rather than "Name" because Name is an Access
reserved word and can lead to confusion.

Larry Linson
Microsoft Access MVP
"Julian" <x@x.xwrote in message news:4j************@individual.net...
>It is relatively simple to include a Calculated Field in your Query that
returns a "sort order" value for the postal codes as you describe. It is
not clear to me exactly the order you desire, so I won't suggest a
particular algorithm, but you'll use embedded IIFs.

I would like to sort out customers by zones North, East etc, zones which
are
defined by various postcodes. For example the North zone would be defined
by
N(1-20), WD(1-24), NW(1-10). So, how do I make a query like: if the
customer's postcode starts with N(1-20), WD(1-24), NW(1-10), than sort
them
into the North zone customers. Then create a report with these customers
that looks like a table (the one with rows and columns and borders)
created
in Word, so I can print it. See what I mean at: ftp://87.112.95.111/
user: anonymous, no password.

>It is also not clear to me what your goal is for "(customers arranged
into
Word tables - normal tables, not database tables)." Unless you plan to
manually modify the output, there is not always an advantage in doing so.
I
>have not had exceptionally good luck with formatting being well-preserved
when I export Reports to "Word format" (actually RTF) and using COM
Automation to build Report output with Word can require a lot more work
than
>creating Access Reports.

If, however, you are going to create Access Reports, then you will use
the
Report's Sorting and Grouping properties to define the sorting and
grouping.

See above.

Julian


Aug 4 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.