Query "N" previous records starting with this record

[WR]

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

 

[John Vinson]

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]

 

[WR]

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

 

[John Vinson]

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]