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 

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" wrote:

> 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 

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" wrote:

> 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" wrote:
> 
> > 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 

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/
__________________________________________

"Johnny Mac" wrote:

> 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

On Wed, 30 Dec 2009 12:09:01 -0800, Johnny Mac
<JohnnyMac@discussions.microsoft.com> wrote:

>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".
-- 

             John W. Vinson [MVP]

> 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/
__________________________________________

"Tom Wickerath" wrote:

> 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/
> __________________________________________
> 
> "Johnny Mac" wrote:
> 
> > 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

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" wrote:

> 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/
> __________________________________________
> 
> "Johnny Mac" wrote:
> 
> > 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

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/
__________________________________________

"Johnny Mac" wrote:

> 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

"Johnny Mac" <JohnnyMac@discussions.microsoft.com> a écrit dans le message 
de news:3761A94B-9023-407E-956C-8A30A20DA27E@microsoft.com...
> 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" wrote:
>
>> 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/
>> __________________________________________
>>
>> "Johnny Mac" wrote:
>>
>> > 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 

John:

I endorse everything Tom and John have said in their excellent responses.
However, we area still having to second guess a lot of things, and I think it
would help us help you if you were to forget about the database itself for a
moment and explain just what is the real world scenario being modelled here,
and exactly what you want to achieve in terms of the real world entities
involved.  A relational database is essentially a model of a part of the real
world, and the important thing is to get that model right.  Once we
understand what you want to do in real world terms, then we'll be in a
position to advise you on how to do it in database terms.

Ken Sheridan
Stafford, England

Johnny Mac wrote:
>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
>
>> Hi John,
>> 
>[quoted text clipped - 47 lines]
>> > Thanks
>> > John

-- 
Message posted via http://www.accessmonster.com