Compare records.

[shafiq_eng]

hi,

How can a previous record of certain field can be compared with next record of other field. For example record 10 of column 1 to record 11 of column 2.

plz help

 

[Andrew Fergus]

Hi, can you provide some more details of what it is you are trying to do? For example, are you trying to do this in a query, while using a form, in a macro or some other way? What are the field & table names and what are the field formats? If you could be a little more specific then someone here should be able to help.

In light of the lack of details and in answer to your question, this can be done on a form with a macro that copies a value from one record to the next that you can then use in your comparison.

Andrew.

 

[shafiq_eng]

hi

I am trying to compare in query.


shafiq

 

[Andrew Fergus]

Hi, I don't think this can be done in a query - although someone please correct me if I am wrong.
Andrew.

 

[shafiq_eng]

Hi

Is it possible in SQL query?

shafiq

 

[Andrew Fergus]

Hi shafiq

While looking for something else I found this article which might help you with your query.

HTH, Andrew.

 

[shafiq_eng]

Hi Andrew

Now I tell you my problem in a little bit of detail. I have following fields

Date BrickName FromMeter ToMeter
10/10/2003 A 0 2.0
10/10/2003 A 2.0 10
10/10/2003 B 10 12
10/10/2003 C 12 15
10/10/2003 A 15 18
10/10/2004 A 18 22

Now you can see that brick A is in two portions I want to make a report which can give me the result

Date BrickName FromMeter ToMeter
10/10/2004 A 0 10
10/10/2004 B 10 12
10/10/2004 C 12 15
10/10/2004 A 15 22

This is a small example.

shafiq

 

[mdmilner]

Maybe, but I think it doesn't work from a purely query technique.

Can you always assume that consecutive records with identical contents in the BrickName field are all related to each other? (whether there's 1, 2 or 10 - if they're consecutive, they're part of the same calculation)

One thing that is similar is this. You can join a table with itself by joining different fields within a given table. This as a simple example generated by the QBE wizard.

SELECT tblTask.Desc_task, tblTask_1.Desc_task, tblTask_1.desc_task2, tblTask_1.task_name, tblTask_1.task_seq
FROM tblTask INNER JOIN tblTask AS tblTask_1 ON tblTask.Desc_task = tblTask_1.desc_task2;

You can also join multiple fields together making it complex.

SELECT tblTask.Desc_task, tblTask_1.Desc_task, tblTask_1.desc_task2, tblTask_1.task_name, tblTask_1.task_seq
FROM tblTask INNER JOIN tblTask AS tblTask_1 ON (tblTask.task_seq = tblTask_1.task_seq) AND (tblTask.Desc_task = tblTask_1.desc_task2);

If you had a keyfield that identifies records in each 'group' you'd at least have an easy way to help the mdb control which records work together. This still doesn't fix the problem.

keyfld Date BrickName FromMeter ToMeter 
1  10/10/2003 A 0 2.0 
1  10/10/2003 A 2.0 10 
2  10/10/2003 B 10 12 
3  10/10/2003 C 12 15 
4  10/10/2003 A 15 18 
4  10/10/2004 A 18 22

If youc an always count on consecutive entries to be 'adjacent' - meaning. The first entry is always from/to a & b -- and the second entry is always from/to b & c -- and any third entry is always from/to c & d

You can try something like this: This creates a crosstab query based on the above original query that does the join. All it really does is present the MIN(minimum) and MAX (maximum) values in the given fields.

TRANSFORM Count(Query1.task_seq) AS CountOftask_seq
SELECT [tblTask].[Desc_task] & [task_seq] AS Expr1, Min(Query1.morenum) AS MinOfmorenum, Max(Query1.morenum2) AS MaxOfmorenum2
FROM Query1
GROUP BY [tblTask].[Desc_task] & [task_seq]
PIVOT Query1.task_seq;

Mike