reporting results of a query

[Ryan Young]

I have a report that I am generating based on a query. My problem is that
the query results are shown in the order they were entered into the table
(which is the way I want them), but on the report, the lines are random. I
would like to know how to force Access to put the results of this particular
query in the report in the order they were entered. I am just using a
filter in the report to display the information I want.

Any suggestions?

 

[fredg]

I have a report that I am generating based on a query. My problem is that
the query results are shown in the order they were entered into the table
(which is the way I want them), but on the report, the lines are random. I
would like to know how to force Access to put the results of this particular
query in the report in the order they were entered. I am just using a
filter in the report to display the information I want.

Any suggestions?

Any sort order in a table or query is irrelevant to a sort order in
the report.

If you wish to have them sorted in the order they were entered, you
should have an [EnteredTime] (a DateTime datatype) field in the table.
When the record is entered populate this field using =Now() as it's
default property value.
Then use this field in the report to sort on.
Use the report's sorting and Grouping dialog.
In Design View, click on View + Sorting and Grouping.
Enter [EnteredTime] in the Field/Expression line.

 

[Larry Linson]

I have a report that I am generating
based on a query. My problem is that
the query results are shown in the order
they were entered into the table (which
is the way I want them), but on the
report, the lines are random. I would
like to know how to force Access to
put the results of this particular query
in the report in the order they were
entered. I am just using a filter in the
report to display the information I want.

Access follows the relational rule that records are, by definition,
unordered. Because Access must display them in _some_ order, you may see
them in the order they were entered, particularly if you are using an
AutoNumber field as a Primary Key. To ensure some particular order, the
record must contain a field that you can use to define it.

In queries, to ensure order, you must specify the fields on which to sort
(or hope that it will be retrieved in Primary Key order, if that is useful).

In reports, the Report's Sorting and Grouping properties determine the
order. Any ordering in the underlying Query is immaterial because it is
ignored. In Design View of the Report, right-click the upperleftmost little
square, and choose "Sorting and Grouping".

And, if you are thinking, "if I DON'T specify sorting and grouping, why
doesn't the report use the order in which I retrieved the data in my Query,"
rest assured that you are not the first to think that, and that Microsoft
has heard that many times. It's possible that may be changed in some future
release, but how they choose what to implement in particular releases is a
mystery to those of us "on the outside" ("on the outside" includes MVPS, who
are not Microsoft employees).

Larry Linson
Microsoft Access MVPs

 

[Ryan Young]

Thanks for the help guys. Now I understand that I cannot add an AutoNumber
column to an existing table. Is there a way that I can set up something
similar to an AutoNumber without having to redo the table? Maybe even a
reverence to the ID cell (which will be in sequential order like I want)?
Also was wondering if there is a way to create a Lookup or something similar
in a date cell that will bring up a calendar from which to pick a date?

Thanks again