Look at next record

[wxkeep]

I have database that runs a query across several tables. That query's results are then sorted by department, office, employee, date, receipt order. The problem is - there is no unique or primary key anywhere in this data. The departments are numbered 1-25, each department has an office numbered 1-12, each office has up to 10 employees (numbered 1-10), and of course, there are entries every day of the year, and for each employee's sales on a given date, their receipts are numbered 1-whatever. Each receipt has a type code on it, for the type of product or service offered. A sample might look like this:

DEPT Office Employee Date Reciept Code
1 2 1 6/5/06 56 102
1 1 1 6/5/06 108 102
4 1 1 6/5/06 109 108
4 1 1 6/5/06 110 200

What I want to be able to do is run a new query on these results. Going through each record in the list and selecting that record as a result in my new query IF the code for this particular record is 108 AND the code for the VERY NEXT record in the query is 200. So, with the above data, it would return record #3.

Any ideas how I can achieve this as simply as possible?

 

[JPMontreal]

Hi,
1. Create a table to receive the data with an ID column (autoNumber).
2. Delete the data of this Temp table to make sure that the table is empty.
3. Insert the result of your query into this table.
4 Make on the Temp table a query like this:

select A.*, B.Code

from Temp as A inner join Temp as B

on A.ID = (B.ID-1)

where A.Code = 108 and B.Code = 200

order by A.ID


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[JPMontreal]

Sorry, I thought SQL server, if you are in ACCESS then your query should look like:

SELECT A.*, B.Code
FROM Temp AS A, Temp AS B
WHERE (((A.ID)=(.[ID]-1)) AND ((A.Code)=108) AND ((B.Code)=200));






Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[wxkeep]

But then, how do I automate that temp table? I like being able to just double click the query and get my results. And I really don't want to build a whole bevy of forms and such just to handle this one function....

 

[JPMontreal]

First make the Temp table something you will have to do only once then
just make a macro that will run your three queries:
1. Delete what is in the table
2. Your query to append into the Temp table
3. The last query to display the result.

You just run this macro.
Regards.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com