Query which Value Returns Previous Record

[Johnny Mac]

I'm very new to access so please forgive me. I have a query that points to a
table and returns a response based on a certain value. I was wondering if
anyone could show me what to type in the query design view that if the value
(example John Smith) is found report back not only that specific record but
the previous record located above.

Any help would be much appreciated.
A thousand thanks
Johnny Mac

 

[Tom Wickerath]

Hi Johnny,

A mental picture that you should have for records in a table, within a JET
("Access") database is like fish in an aquarium. So, locating "the previous
record above" presents some challenges, unless you have a field, such as an
autonumber or a data/time value, that can be used to order the records.

To get effective help, you should provide the names of all tables and fields
involved, the data types for the fields, some sample data, and the result
that you are looking to achieve. You should also copy the SQL (Structured
Query Language) statement for your query, and paste it into a reply. In
Access 2003 and all prior versions, you can open a query in design view, and
then click on View | SQL View, to see the SQL statement. Select the entire
statement, press <Ctrl><C> to copy it, and then paste it <Ctrl><V> into a
reply.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Johnny Mac]

Sorry about not including additional info. Sort of new to this stuff.

I do not use auto number in my tables however, if it would make the query
easier I will gladly let access assign a Auto Number to the far left column.

The table is called Audits and the focus is Field 2 of the Table
The Field 2 Value is Employee Named "Tom" and the Data type is text

The Query is call Audit Query and I am pulling from Table Audits and my
query is searching for every instanace of Tom in Feild 2 and Jim in Feild 4.
If there is a match I need to show the previous record as well as this
record, not sure how to show previous record.

Thanks
John

 

[Tom Wickerath]

Hi John,

Are the names of your fields "Field 2", "Field 4", etc.? If so, it sounds
like you imported this data from Excel, but did not include the option for
the column heading. Or, is the second field named "Employee"? So far, we have
this:

Audits (table)
Field 2 Text (or possibly Employee)
Field 4 Text

Sample Data
Field 2 Field 4
"Tom" "Jim"
"Tom" "Some other name"
"Tom" Null ? (ie. no entry at all)

Really need more sample data, along with the result that you are expecting.
Is there a date/time field in the record?

Do these text fields have the default setting of Allow Zero Length = Yes? Is
the Required property for these fields set to Yes or No? Do the fields "Field
2" (or Employee) and Field 4 describe the same type of data (employees) or
does one field describe employees and the other field something else, like
manager? If the latter, ie. both fields describe employees, then you likely
have an improper database design, commonly referred to as an "Access
spreadsheet".


Audit Query
SQL = ?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John W. Vinson]

The Query is call Audit Query and I am pulling from Table Audits and my
query is searching for every instanace of Tom in Feild 2 and Jim in Feild 4.
If there is a match I need to show the previous record as well as this
record, not sure how to show previous record.

The point that Tom was making is that *there IS no previous record*.

Relational tables are not spreadsheets. They have no defined order. There is
no record number, and there is no (implied or explicit) usable order of
records. They're like fish in an aquarium, or marbles in a bag.

If you want to define an order for the records, you must - no option! -
include some field or fields in your table which define that order, and use
that field to locate the "previous record".

 

[Tom Wickerath]

If the latter, ie. both fields describe employees, ....

should have read:

"If the former, ie. both fields describe employees, ...."


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Johnny Mac]

Sir,

This is not an import of a excel or any file. This is a New Table called
Audit.

Audit Table:

Heading for Table is
ID Feild 1 Feild 2 Feild 3 Feild 4
Betty Tom Judy Robert
Susan Danny Brandi Jerry
Steve Tom James Jim

Audit Query:
ID Feild 1 Feild 2 Feild 3 Feild 4
Steve Tom James Jim

Query Design View

Criteria: Tom Jim

Thanks,
John

 

[Tom Wickerath]

Hi John,

You really *should* assign some proper names to your fields, instead of just
"Field 1", "Field 2", etc. The name should describe the attribute, should not
include spaces, special characters, or reserved words:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

If this was not imported from Excel, then you likely got such a result by
using the built-in denormalization wizard in Access (aka Create a table by
entering data). Open the table in design view and assign descriptive names to
each field. Add an Autonumber data type while you are at it.

Query Design View

Criteria: Tom Jim

While I can deduce the equivalent SQL statement from the information you
provided, it would be best if you could bring up the SQL view for your query,
copy the SQL statement, and paste it into a reply.

You still haven't shown me the result that you desire. I can only guess that
you want to see these two rows, based on the order that you typed them into
this newsgroup reply, when you specify criteria of "Tom" for Field 2 and
"Jim" for Field 4:

Susan Danny Brandi Jerry
Steve Tom James Jim

But, you haven't clearly stated that this is your desired result....
What values are shown in the ID field, and is this an Autonumber data type?

Have you read John Vinson's reply yet?

Try this sample, using the data presented in the KB article. Once you get it
working, try applying the same logic to your data:

Referring to a Field in the Previous Record or Next Record
http://support.microsoft.com/kb/210504

Note: Disregard the "ACC2000" in the title. This article applies to all
versions of Access.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[frederic cayet]

Sir,

This is not an import of a excel or any file. This is a New Table called
Audit.

Audit Table:

Heading for Table is
ID Feild 1 Feild 2 Feild 3 Feild 4
Betty Tom Judy Robert
Susan Danny Brandi Jerry
Steve Tom James Jim

Audit Query:
ID Feild 1 Feild 2 Feild 3 Feild 4
Steve Tom James Jim

Query Design View

Criteria: Tom Jim

Thanks,
John