Access Table Sorting/Query Last Function

  • Thread starter Thread starter mb
  • Start date Start date
M

mb

Hi,
I've been designing a database that basically works off two tables. From
the first table, I have a query that pulls information and populates the
second table. I've done this so that I can pull the last entered [Status]
for a particular [Category], [Initiative], and [Task], and use that as the
updated [Status] for all records in the second table with the same
[Category], [Initiative], and [Task]. In the first table, I have an
autonumber set for each entry as the primary key, and I've removed all
sorting and filtering from the tables. For some reason, however, entries
that are completed today and entered into the first table using a form in the
database have started from the top of the table and are inserted thereafter
instead of continuing from the bottom of all the entries in the table from
previous days. I was under the impression that if you remove the filter and
sort, the natural sort order of the table would insert new data to the bottom
of the table, which would work perfectly with the Last Function that I set up
in the query that pulls the [Status] information from the table. Since the
table is doing this weird sorting, I can't get the Last Function to work
properly; it keeps pulling the last of yesterday's Status. I've only had
this database up for a day, so I don't know how it will behave tomorrow, but
I need it to not behave like this. Can anyone help me figure this out please?
 
I would never rely on "Last" since records in a table are not ordered. You
can order records in a query. Even then, I never use Last. I expect there is
a solution to your issue without using Last. You probably need to find the
Max() of some date field.
 
What you mean by LAST and what Access means by LAST may not be the same.
And if you are working directly in the tables, you (and Access) will be
working much harder than you need to.

First, if you Table1 includes a field that holds the Date/Time the record
was added, you can use that field to get the Max(YourDateTimeField) instead
of the Last(...) in your totals query.

Tables in Access are unordered "buckets o' data" -- use queries to impose
order on the output from those tables.

You don't describe how you've set up your form. Are you viewing single
records or a datasheet?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The table is set up as a datasheet. Since reading the suggestions, I've
included a field with Now() as the default value to capture the time of entry
for each addition, and have set up the query to pull from the max date as you
suggested. I've put in a number of test entries to ensure it works, and will
check again tomorrow to make sure it isn't doing the same thing. I'll keep
you posted with the results. Thanks for your help. :)

Jeff Boyce said:
What you mean by LAST and what Access means by LAST may not be the same.
And if you are working directly in the tables, you (and Access) will be
working much harder than you need to.

First, if you Table1 includes a field that holds the Date/Time the record
was added, you can use that field to get the Max(YourDateTimeField) instead
of the Last(...) in your totals query.

Tables in Access are unordered "buckets o' data" -- use queries to impose
order on the output from those tables.

You don't describe how you've set up your form. Are you viewing single
records or a datasheet?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


mb said:
Hi,
I've been designing a database that basically works off two tables. From
the first table, I have a query that pulls information and populates the
second table. I've done this so that I can pull the last entered [Status]
for a particular [Category], [Initiative], and [Task], and use that as the
updated [Status] for all records in the second table with the same
[Category], [Initiative], and [Task]. In the first table, I have an
autonumber set for each entry as the primary key, and I've removed all
sorting and filtering from the tables. For some reason, however, entries
that are completed today and entered into the first table using a form in
the
database have started from the top of the table and are inserted
thereafter
instead of continuing from the bottom of all the entries in the table from
previous days. I was under the impression that if you remove the filter
and
sort, the natural sort order of the table would insert new data to the
bottom
of the table, which would work perfectly with the Last Function that I set
up
in the query that pulls the [Status] information from the table. Since
the
table is doing this weird sorting, I can't get the Last Function to work
properly; it keeps pulling the last of yesterday's Status. I've only had
this database up for a day, so I don't know how it will behave tomorrow,
but
I need it to not behave like this. Can anyone help me figure this out
please?
Click to expand...
Click to expand...
 
Assigning the date field works. Sorry for the multiple posts. I'm new to
the community and wasn't sure if people frequent the same areas, so I wanted
to make sure I covered my bases. Thanks for your help.

mb said:
The table is set up as a datasheet. Since reading the suggestions, I've
included a field with Now() as the default value to capture the time of entry
for each addition, and have set up the query to pull from the max date as you
suggested. I've put in a number of test entries to ensure it works, and will
check again tomorrow to make sure it isn't doing the same thing. I'll keep
you posted with the results. Thanks for your help. :)

Jeff Boyce said:
What you mean by LAST and what Access means by LAST may not be the same.
And if you are working directly in the tables, you (and Access) will be
working much harder than you need to.

First, if you Table1 includes a field that holds the Date/Time the record
was added, you can use that field to get the Max(YourDateTimeField) instead
of the Last(...) in your totals query.

Tables in Access are unordered "buckets o' data" -- use queries to impose
order on the output from those tables.

You don't describe how you've set up your form. Are you viewing single
records or a datasheet?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


mb said:
Hi,
I've been designing a database that basically works off two tables. From
the first table, I have a query that pulls information and populates the
second table. I've done this so that I can pull the last entered [Status]
for a particular [Category], [Initiative], and [Task], and use that as the
updated [Status] for all records in the second table with the same
[Category], [Initiative], and [Task]. In the first table, I have an
autonumber set for each entry as the primary key, and I've removed all
sorting and filtering from the tables. For some reason, however, entries
that are completed today and entered into the first table using a form in
the
database have started from the top of the table and are inserted
thereafter
instead of continuing from the bottom of all the entries in the table from
previous days. I was under the impression that if you remove the filter
and
sort, the natural sort order of the table would insert new data to the
bottom
of the table, which would work perfectly with the Last Function that I set
up
in the query that pulls the [Status] information from the table. Since
the
table is doing this weird sorting, I can't get the Last Function to work
properly; it keeps pulling the last of yesterday's Status. I've only had
this database up for a day, so I don't know how it will behave tomorrow,
but
I need it to not behave like this. Can anyone help me figure this out
please?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top