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
|
|||
|
|||
Query "N" previous records starting with this record
I have a table with 2 primary keys that are in TEXT format. Date is Primary
Key 1 in this format (e.g. "2005.08.30") and Time is Primary Key 2 in this format (e.g. "00:00", "06:15", "21:00"). I want to build a query that says give me the past 15 records (or what ever number I choose) starting with this record "2005.08.21 00:00" (or what ever record I choose). How do I do this? WR |
#2
|
|||
|
|||
On Tue, 30 Aug 2005 15:27:01 -0700, (donotspam)"
om wrote: I have a table with 2 primary keys that are in TEXT format. Nitpick: you have ONE primary key which consists of two fields. Date is Primary Key 1 in this format (e.g. "2005.08.30") and Time is Primary Key 2 in this format (e.g. "00:00", "06:15", "21:00"). Any reason not to use a single Date/Time field??? I want to build a query that says give me the past 15 records (or what ever number I choose) starting with this record "2005.08.21 00:00" (or what ever record I choose). How do I do this? A Top Values query should work: SELECT TOP 15 [field], [field], [field], ... FROM YourTable ORDER BY CDate([Date] & " " & [Time]) DESC; Note that both Date and Time are reserved words and are NOT good choices for fieldnames. Access *will* confuse them with the builtin Date() and Time() functions. John W. Vinson[MVP] |
#3
|
|||
|
|||
"John Vinson" wrote: On Tue, 30 Aug 2005 15:27:01 -0700, (donotspam)" om wrote: I have a table with 2 primary keys that are in TEXT format. Nitpick: you have ONE primary key which consists of two fields. Date is Primary Key 1 in this format (e.g. "2005.08.30") and Time is Primary Key 2 in this format (e.g. "00:00", "06:15", "21:00"). Any reason not to use a single Date/Time field??? I want to build a query that says give me the past 15 records (or what ever number I choose) starting with this record "2005.08.21 00:00" (or what ever record I choose). How do I do this? A Top Values query should work: SELECT TOP 15 [field], [field], [field], ... FROM YourTable ORDER BY CDate([Date] & " " & [Time]) DESC; Note that both Date and Time are reserved words and are NOT good choices for fieldnames. Access *will* confuse them with the builtin Date() and Time() functions. John W. Vinson[MVP] Hi John, both time and date fields are designated as primary keys. You can have multiple dates but the combination of date and time is unique. The data is in two separate fields when I receive it in csv format. Other than the fact that I do not know how to convert it to one field, there is no other reason for it to be two fields. Fortunately, the real field names are not actually "Date" and "Time", they are "Year_Month_Day" and "Hours_Minutes". Thanks for your response. I will try your suggestion and let you know how it works out. With respect to my comments on one vs. two fields, any thoughts? WR |
#4
|
|||
|
|||
On Wed, 7 Sep 2005 10:41:04 -0700, (donotspam)"
wrote: Hi John, both time and date fields are designated as primary keys. You can have multiple dates but the combination of date and time is unique. The data is in two separate fields when I receive it in csv format. Other than the fact that I do not know how to convert it to one field, there is no other reason for it to be two fields. Fortunately, the real field names are not actually "Date" and "Time", they are "Year_Month_Day" and "Hours_Minutes". Thanks for your response. I will try your suggestion and let you know how it works out. With respect to my comments on one vs. two fields, any thoughts? Use an Append query based on your (linked) text file; if you have one primary key date/time field, you can append to it a calculated field: CDate([Year_month_day]) + CDate([hours_minutes]) Since a Date/Time is just a number, a count of days and fractions of a day (times), you can numerically add a pure-date field (a whole number) to a pure-time field (a fraction less than 1) to get a date/time. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Query is not locating all records | Susan L | Running & Setting Up Queries | 3 | December 23rd, 2004 08:41 PM |
Attn Sprinks- Not duplicate insert records | babs | Using Forms | 1 | December 13th, 2004 06:25 PM |
Unmatched Query Mess | Natalia | Running & Setting Up Queries | 8 | October 28th, 2004 02:36 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |