If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Table Sorting/Query Last Function
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? |
#2
|
|||
|
|||
Access Table Sorting/Query Last Function
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. -- Duane Hookom Microsoft Access MVP "mb" wrote: 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? |
#3
|
|||
|
|||
Access Table Sorting/Query Last Function
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" wrote in message ... 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? |
#4
|
|||
|
|||
Access Table Sorting/Query Last Function
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" wrote: 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" wrote in message ... 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? |
#5
|
|||
|
|||
Access Table Sorting/Query Last Function
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" wrote: 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" wrote: 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|