Maintenance/activity log duplicate date total last max unique dist

[Guest]

Here's the situation: I am creating a maintenance/activity log for the a lab
where a user will be able to enter a part name and date installed among other
fields (the rest are irrelevant to this question). I want to create a query
where the user can type in a date and the configuration of the lab on that
date will be displayed. This is done by limiting the date field with a
criteria of "<=[enter a date:]".

I've tried two methods to accomplish this. The first I tried was the "last"
function under totals. This works assuming all the database entries are made
in chronological order i.e. it is not idiot proof, which it needs to be. It
fails if someone makes an old entry after a newer one was entered. For
example, if the first person enters for part name Laptop-1A and a date
installed of July 21, 2005 and then right after that, someone else comes
along and enters Laptop-1A installed on July 20, 2005. Using the last
function and choosing a date of July 22 to see the configuration of the lab,
the entry with Laptop-1A installed on July 20, 2005 is displayed, which was
physically entered into the database last, but is the wrong entry that I want
to see.

Using the 'max' function doesn't work either because I want to search a
range of dates. If the maximum date for all of the Laptop-1A entries are not
within the user chosen range of dates (<=[enter a date:]), all of the
Laptop-1A entries are discarded.

My thinking now is thus: Is there any way to use the 'Min' function and
have Access return the opposite results? This would work because the minimum
date is always going to be within the range of dates a user enters, and if I
could get it to return the opposite of Min, something like Not Min, this
would work perfectly. One would think that the opposite of min would just be
Max, but because of the range of dates issue, this is not the case. BTW,
TopValues did not work as I want to return each part an instance of once,
including the ones which have duplicate entries.

I have a bad feeling I am headed straight for some SQL code writing and I am
hoping to avoid that complexity. I have never written SQL before but I am
familiar with C++, BASIC and HTML.

Thanks for the help!!

 

[MGFoster]

Here's the situation: I am creating a maintenance/activity log for the a lab
where a user will be able to enter a part name and date installed among other
fields (the rest are irrelevant to this question). I want to create a query
where the user can type in a date and the configuration of the lab on that
date will be displayed. This is done by limiting the date field with a
criteria of "<=[enter a date:]".

I've tried two methods to accomplish this. The first I tried was the "last"
function under totals. This works assuming all the database entries are made
in chronological order i.e. it is not idiot proof, which it needs to be. It
fails if someone makes an old entry after a newer one was entered. For
example, if the first person enters for part name Laptop-1A and a date
installed of July 21, 2005 and then right after that, someone else comes
along and enters Laptop-1A installed on July 20, 2005. Using the last
function and choosing a date of July 22 to see the configuration of the lab,
the entry with Laptop-1A installed on July 20, 2005 is displayed, which was
physically entered into the database last, but is the wrong entry that I want
to see.

Using the 'max' function doesn't work either because I want to search a
range of dates. If the maximum date for all of the Laptop-1A entries are not
within the user chosen range of dates (<=[enter a date:]), all of the
Laptop-1A entries are discarded.

My thinking now is thus: Is there any way to use the 'Min' function and
have Access return the opposite results? This would work because the minimum
date is always going to be within the range of dates a user enters, and if I
could get it to return the opposite of Min, something like Not Min, this
would work perfectly. One would think that the opposite of min would just be
Max, but because of the range of dates issue, this is not the case. BTW,
TopValues did not work as I want to return each part an instance of once,
including the ones which have duplicate entries.

I have a bad feeling I am headed straight for some SQL code writing and I am
hoping to avoid that complexity. I have never written SQL before but I am
familiar with C++, BASIC and HTML.

Could you show the structure of your table(s), some sample data and an
example of the results you want? It would help, enormously.

RDBMS by definition do not have a "first" and "last" entry - the data is
stored in random order, which means you have to use the dates to
determine the retrieval order. Using Max() on your dates seems to be
the solution to your problem, but your problem description is not very
clear, hence my request for table structure & sample/example data.

If you can write C++ code you can learn SQL. ;-)