Address books

[LOAMA]

I am designing my own address book using Access 2007. The form is fine and
getting a print preview on the size paper I am using is also working. The
problem I have is that the preview does not show the addresses in
alphabetical order. I have tried using "group" or "sort" and obviously
incorrectly because that still doesn't work. I have set up a field, "alpha"
which is a single letter for each person. (The first letter of the last
name.) I also would like a page break after each letter of the alphabet. I
need help in getting the addresses in alpha order and having a new page for
the start of a new letter.

 

[Duane Hookom]

You can set the sorting and grouping to a field and then select the first X
number of characters. You would then have to set another sorting and grouping
level to the full field so the records sort correctly within the primary
group/level.

 

[LOAMA]

I set the sorting and grouping two ways, once to the "Alpha" field, a single
letter and the second time to the field "Last name". Both didn't work. What
I didn't do is select the first X number of characters. Where do you do that
and what is the first X number of characters. Sorry if I sound dense.

 

[Duane Hookom]

I don't know which version of Access you are using so don't create the Alpha
field. Set the first level of sorting and grouping to:
=Left([LastNameField],1)
Display the group header/footer so you set properties to new page after a
group.

Set the second level of sorting and grouping to:
=[LastNameField] & [FirstNameField]

 

[LOAMA]

First I am using Access 2007.
Now I am more confused than ever. You said "Sorting and Grouping" together.
I can only do one at a time. If I used group and typed
"=Left([Last Name],1)" Last Name is my field name I was asked for the
parameter value.
If I used sort and wrote the same expression as above the preview was not
in Alphabetical order. And if I tried a second sort, with the first & last
name the same expression came up as my first.

If I tried to used the table to import my field names I got syntax errors.

Then when I went to properties to display new page there was only properties
for either header or footer and neither had a place for "new page". All I
found was display after "section".
I am so confused..... Can you help?

--
Thanks for your help in advance,
LOAMA

I don't know which version of Access you are using so don't create the Alpha
field. Set the first level of sorting and grouping to:
=Left([LastNameField],1)
Display the group header/footer so you set properties to new page after a
group.

Set the second level of sorting and grouping to:
=[LastNameField] & [FirstNameField]

--
Duane Hookom
Microsoft Access MVP

I set the sorting and grouping two ways, once to the "Alpha" field, a single
letter and the second time to the field "Last name". Both didn't work. What
I didn't do is select the first X number of characters. Where do you do that
and what is the first X number of characters. Sorry if I sound dense.

 

[Duane Hookom]

In 2007 you would first choose to add a group on (this will automatically
sort). Choose expression rather than a field and enter:
=Left([Your Field Name Here],1)
You can add a text box to this group header section with a control source of:
=Left([Your Field Name Here],1)

Then add a Sort By based on [Your Field Name Here].

You should now have a report that groups and sorts by the first letter of
your field and within the group the field should be sorted.

--
Duane Hookom
Microsoft Access MVP

First I am using Access 2007.
Now I am more confused than ever. You said "Sorting and Grouping" together.
I can only do one at a time. If I used group and typed
"=Left([Last Name],1)" Last Name is my field name I was asked for the
parameter value.
If I used sort and wrote the same expression as above the preview was not
in Alphabetical order. And if I tried a second sort, with the first & last
name the same expression came up as my first.

If I tried to used the table to import my field names I got syntax errors.

Then when I went to properties to display new page there was only properties
for either header or footer and neither had a place for "new page". All I
found was display after "section".
I am so confused..... Can you help?

--
Thanks for your help in advance,
LOAMA

I don't know which version of Access you are using so don't create the Alpha
field. Set the first level of sorting and grouping to:
=Left([LastNameField],1)
Display the group header/footer so you set properties to new page after a
group.

Set the second level of sorting and grouping to:
=[LastNameField] & [FirstNameField]

--
Duane Hookom
Microsoft Access MVP

I set the sorting and grouping two ways, once to the "Alpha" field, a single
letter and the second time to the field "Last name". Both didn't work. What
I didn't do is select the first X number of characters. Where do you do that
and what is the first X number of characters. Sorry if I sound dense.
--
Thanks for your help in advance,
LOAMA


:

You can set the sorting and grouping to a field and then select the first X
number of characters. You would then have to set another sorting and grouping
level to the full field so the records sort correctly within the primary
group/level.

--
Duane Hookom
Microsoft Access MVP


:

I am designing my own address book using Access 2007. The form is fine and
getting a print preview on the size paper I am using is also working. The
problem I have is that the preview does not show the addresses in
alphabetical order. I have tried using "group" or "sort" and obviously
incorrectly because that still doesn't work. I have set up a field, "alpha"
which is a single letter for each person. (The first letter of the last
name.) I also would like a page break after each letter of the alphabet. I
need help in getting the addresses in alpha order and having a new page for
the start of a new letter.

 

[LOAMA]

At the bottom of the Report, where you enter "group Sosrt' I hit on Group
then expression and typed =Left([Last Name],1) and still the preview does
NOT show the addresses in alphabetical order. You also said I can add a test
box tothis groupo header section. I don'tunderstand that.
What I did notice was on the design view at the top of the report I now have
a bar under page header that says =Left([Last Name],1)
Should something be in the Property sheet of Report to show the sort and
page break on each letter?
I am still very confused.
--
Thanks for your help in advance,
LOAMA

In 2007 you would first choose to add a group on (this will automatically
sort). Choose expression rather than a field and enter:
=Left([Your Field Name Here],1)
You can add a text box to this group header section with a control source of:
=Left([Your Field Name Here],1)

Then add a Sort By based on [Your Field Name Here].

You should now have a report that groups and sorts by the first letter of
your field and within the group the field should be sorted.

--
Duane Hookom
Microsoft Access MVP

First I am using Access 2007.
Now I am more confused than ever. You said "Sorting and Grouping" together.
I can only do one at a time. If I used group and typed
"=Left([Last Name],1)" Last Name is my field name I was asked for the
parameter value.
If I used sort and wrote the same expression as above the preview was not
in Alphabetical order. And if I tried a second sort, with the first & last
name the same expression came up as my first.

If I tried to used the table to import my field names I got syntax errors.

Then when I went to properties to display new page there was only properties
for either header or footer and neither had a place for "new page". All I
found was display after "section".
I am so confused..... Can you help?

--
Thanks for your help in advance,
LOAMA

I don't know which version of Access you are using so don't create the Alpha
field. Set the first level of sorting and grouping to:
=Left([LastNameField],1)
Display the group header/footer so you set properties to new page after a
group.

Set the second level of sorting and grouping to:
=[LastNameField] & [FirstNameField]

--
Duane Hookom
Microsoft Access MVP


:

I set the sorting and grouping two ways, once to the "Alpha" field, a single
letter and the second time to the field "Last name". Both didn't work. What
I didn't do is select the first X number of characters. Where do you do that
and what is the first X number of characters. Sorry if I sound dense.
--
Thanks for your help in advance,
LOAMA


:

You can set the sorting and grouping to a field and then select the first X
number of characters. You would then have to set another sorting and grouping
level to the full field so the records sort correctly within the primary
group/level.

--
Duane Hookom
Microsoft Access MVP


:

I am designing my own address book using Access 2007. The form is fine and
getting a print preview on the size paper I am using is also working. The
problem I have is that the preview does not show the addresses in
alphabetical order. I have tried using "group" or "sort" and obviously
incorrectly because that still doesn't work. I have set up a field, "alpha"
which is a single letter for each person. (The first letter of the last
name.) I also would like a page break after each letter of the alphabet. I
need help in getting the addresses in alpha order and having a new page for
the start of a new letter.

 

[LOAMA]

I am typing the expression in the box "Expression Builder". Is that correct?
Still can't figure out "ADD A TEXT BOX" but would that make a differnce in
trying to get the alphabetical sort?
--
Thanks for your help in advance,
LOAMA

In 2007 you would first choose to add a group on (this will automatically
sort). Choose expression rather than a field and enter:
=Left([Your Field Name Here],1)
You can add a text box to this group header section with a control source of:
=Left([Your Field Name Here],1)

Then add a Sort By based on [Your Field Name Here].

You should now have a report that groups and sorts by the first letter of
your field and within the group the field should be sorted.

--
Duane Hookom
Microsoft Access MVP

First I am using Access 2007.
Now I am more confused than ever. You said "Sorting and Grouping" together.
I can only do one at a time. If I used group and typed
"=Left([Last Name],1)" Last Name is my field name I was asked for the
parameter value.
If I used sort and wrote the same expression as above the preview was not
in Alphabetical order. And if I tried a second sort, with the first & last
name the same expression came up as my first.

If I tried to used the table to import my field names I got syntax errors.

Then when I went to properties to display new page there was only properties
for either header or footer and neither had a place for "new page". All I
found was display after "section".
I am so confused..... Can you help?

--
Thanks for your help in advance,
LOAMA

I don't know which version of Access you are using so don't create the Alpha
field. Set the first level of sorting and grouping to:
=Left([LastNameField],1)
Display the group header/footer so you set properties to new page after a
group.

Set the second level of sorting and grouping to:
=[LastNameField] & [FirstNameField]

--
Duane Hookom
Microsoft Access MVP


:

I set the sorting and grouping two ways, once to the "Alpha" field, a single
letter and the second time to the field "Last name". Both didn't work. What
I didn't do is select the first X number of characters. Where do you do that
and what is the first X number of characters. Sorry if I sound dense.
--
Thanks for your help in advance,
LOAMA


:

You can set the sorting and grouping to a field and then select the first X
number of characters. You would then have to set another sorting and grouping
level to the full field so the records sort correctly within the primary
group/level.

--
Duane Hookom
Microsoft Access MVP


:

I am designing my own address book using Access 2007. The form is fine and
getting a print preview on the size paper I am using is also working. The
problem I have is that the preview does not show the addresses in
alphabetical order. I have tried using "group" or "sort" and obviously
incorrectly because that still doesn't work. I have set up a field, "alpha"
which is a single letter for each person. (The first letter of the last
name.) I also would like a page break after each letter of the alphabet. I
need help in getting the addresses in alpha order and having a new page for
the start of a new letter.

 

[LOAMA]

Sorry to be so dense..
I just noticed something else in Preview report.
I have entered only 18 names so far which wuld show me about 5 pages not
separating pages in alphabetical order. In the "Preview" it shows 576 pages.
Some balnk and the list repeating over. Another thing I do not understand.
I hope you can solve my frustration/dilemna.
--
Thanks for your help in advance,
LOAMA

I am typing the expression in the box "Expression Builder". Is that correct?
Still can't figure out "ADD A TEXT BOX" but would that make a differnce in
trying to get the alphabetical sort?
--
Thanks for your help in advance,
LOAMA

In 2007 you would first choose to add a group on (this will automatically
sort). Choose expression rather than a field and enter:
=Left([Your Field Name Here],1)
You can add a text box to this group header section with a control source of:
=Left([Your Field Name Here],1)

Then add a Sort By based on [Your Field Name Here].

You should now have a report that groups and sorts by the first letter of
your field and within the group the field should be sorted.

--
Duane Hookom
Microsoft Access MVP

First I am using Access 2007.
Now I am more confused than ever. You said "Sorting and Grouping" together.
I can only do one at a time. If I used group and typed
"=Left([Last Name],1)" Last Name is my field name I was asked for the
parameter value.
If I used sort and wrote the same expression as above the preview was not
in Alphabetical order. And if I tried a second sort, with the first & last
name the same expression came up as my first.

If I tried to used the table to import my field names I got syntax errors.

Then when I went to properties to display new page there was only properties
for either header or footer and neither had a place for "new page". All I
found was display after "section".
I am so confused..... Can you help?

--
Thanks for your help in advance,
LOAMA


:

I don't know which version of Access you are using so don't create the Alpha
field. Set the first level of sorting and grouping to:
=Left([LastNameField],1)
Display the group header/footer so you set properties to new page after a
group.

Set the second level of sorting and grouping to:
=[LastNameField] & [FirstNameField]

--
Duane Hookom
Microsoft Access MVP


:

I set the sorting and grouping two ways, once to the "Alpha" field, a single
letter and the second time to the field "Last name". Both didn't work. What
I didn't do is select the first X number of characters. Where do you do that
and what is the first X number of characters. Sorry if I sound dense.
--
Thanks for your help in advance,
LOAMA


:

You can set the sorting and grouping to a field and then select the first X
number of characters. You would then have to set another sorting and grouping
level to the full field so the records sort correctly within the primary
group/level.

--
Duane Hookom
Microsoft Access MVP


:

I am designing my own address book using Access 2007. The form is fine and
getting a print preview on the size paper I am using is also working. The
problem I have is that the preview does not show the addresses in
alphabetical order. I have tried using "group" or "sort" and obviously
incorrectly because that still doesn't work. I have set up a field, "alpha"
which is a single letter for each person. (The first letter of the last
name.) I also would like a page break after each letter of the alphabet. I
need help in getting the addresses in alpha order and having a new page for
the start of a new letter.

 

[Duane Hookom]

You really seem to be struggling with this. I would work only in design view
since that is where I am most comfortable with the display.

When you add a Grouping level, this creates a group section in your report
design. The horizontal bar you referred to is the visual divider between
report sections. You might have these for
-Report Header
-Page Header
-Group Headers
-Detail
-Group Footers
-Page Footer
-Report Footer
You can add controls to each of these sections by clicking a control and
then clicking in the section where you want the control.

If you are getting too many pages, then either you have too many records in
your report's record source or your sections (see above) are much taller than
they need to be.

If you can find your report properties, it might be good to copy the Record
Source sql view and paste it into a reply to the news group.
--
Duane Hookom
Microsoft Access MVP

Sorry to be so dense..
I just noticed something else in Preview report.
I have entered only 18 names so far which wuld show me about 5 pages not
separating pages in alphabetical order. In the "Preview" it shows 576 pages.
Some balnk and the list repeating over. Another thing I do not understand.
I hope you can solve my frustration/dilemna.
--
Thanks for your help in advance,
LOAMA

I am typing the expression in the box "Expression Builder". Is that correct?
Still can't figure out "ADD A TEXT BOX" but would that make a differnce in
trying to get the alphabetical sort?
--
Thanks for your help in advance,
LOAMA

In 2007 you would first choose to add a group on (this will automatically
sort). Choose expression rather than a field and enter:
=Left([Your Field Name Here],1)
You can add a text box to this group header section with a control source of:
=Left([Your Field Name Here],1)

Then add a Sort By based on [Your Field Name Here].

You should now have a report that groups and sorts by the first letter of
your field and within the group the field should be sorted.

--
Duane Hookom
Microsoft Access MVP


:

First I am using Access 2007.
Now I am more confused than ever. You said "Sorting and Grouping" together.
I can only do one at a time. If I used group and typed
"=Left([Last Name],1)" Last Name is my field name I was asked for the
parameter value.
If I used sort and wrote the same expression as above the preview was not
in Alphabetical order. And if I tried a second sort, with the first & last
name the same expression came up as my first.

If I tried to used the table to import my field names I got syntax errors.

Then when I went to properties to display new page there was only properties
for either header or footer and neither had a place for "new page". All I
found was display after "section".
I am so confused..... Can you help?

--
Thanks for your help in advance,
LOAMA


:

I don't know which version of Access you are using so don't create the Alpha
field. Set the first level of sorting and grouping to:
=Left([LastNameField],1)
Display the group header/footer so you set properties to new page after a
group.

Set the second level of sorting and grouping to:
=[LastNameField] & [FirstNameField]

--
Duane Hookom
Microsoft Access MVP


:

I set the sorting and grouping two ways, once to the "Alpha" field, a single
letter and the second time to the field "Last name". Both didn't work. What
I didn't do is select the first X number of characters. Where do you do that
and what is the first X number of characters. Sorry if I sound dense.
--
Thanks for your help in advance,
LOAMA


:

You can set the sorting and grouping to a field and then select the first X
number of characters. You would then have to set another sorting and grouping
level to the full field so the records sort correctly within the primary
group/level.

--
Duane Hookom
Microsoft Access MVP


:

I am designing my own address book using Access 2007. The form is fine and
getting a print preview on the size paper I am using is also working. The
problem I have is that the preview does not show the addresses in
alphabetical order. I have tried using "group" or "sort" and obviously
incorrectly because that still doesn't work. I have set up a field, "alpha"
which is a single letter for each person. (The first letter of the last
name.) I also would like a page break after each letter of the alphabet. I
need help in getting the addresses in alpha order and having a new page for
the start of a new letter.

 

[Chuck]

I have entered only 18 names so far which wuld show me about 5 pages not
separating pages in alphabetical order. In the "Preview" it shows 576 pages.
Some balnk and the list repeating over.

Somewhere in your database you have a many to many relationship. Not good.

Duane, from LOAMA's posts I think he/she needs some very basic help. Once
there is a query delivering the correct data than LOAMA can clean it up using
your post.

In your main table include: LastName, FirstName, Address, and ZIP. Possible
include EmailAddress, and Phone. Do not include City and State. In a
secondary table include: ZIP, City, and State. Do not make duplicate ZIPs.
Make ZIP be a primary key, Indexed Yes (no duplicates). Make ZIP be a text
field in both tables. It is OK if some zip codes are 7 numbers and others are
11 numbers.

The first seven numbers in the zip code determines the state and city of the
post office that handles the mail to that address, If used, the last four
numbers determine what mail route or PO Box or anything else the post office is
using to help get the mail where it belongs. A city with more than one post
office will have a different zip code for each. Small towns may share one post
office. In this case the last four numbers will determine which town the mail
goes to and possible which delivery rout within the town.

Open the Relationships window. Add the main table and the secondary table.
Left click on zip and holding the mouse key down draw a line over to the ZIP in
the main table. In the Relationships Message box click on: Enforce Referential
Integrity. The line joining to two tables should now show the number 1 on the
end near the secondary table and an infinity sign (like the number 8 laying on
its side) on the end near the main table.

Make a new query in design mode. In the show table dialog box, double click on
main table and double click on the secondary table. You should see the two
tables just like you saw in the relationships window including the line joining
the two ZIPs. Select the necessary fields including the ZIP field from the
main table and the City and State fields from the secondary table.

Open the query and you should see only your starter 18 names and addresses.

Use this query as the record source for your report.

Chuck
--