Access query to return a specific record and the next one

[wb8wbn]

I have an Access table that has telephone call data. I have a query to return a record, but I also need the next record in the table. Ie: result record and result record +1.

How can I make a query to do this ??

Thanks in advance...

 

[Golom]

Things like "Previous record" or "next record" have meaning only in the context of some specific Order By clause because relational databases don't guarantee any particular ordering of records without an Order By clause.
The general strategy is:

Select TOP 2 f1, f2, f3, etc.

From tbl

Where f1 >= 'Some Value'

Order By f1

Here we are assuming that the field "f1" completely determines the ordering of the returned records.

 

[wb8wbn]

The records are imported into the table from another application and are in the correct sequential order. If I change the order, I loose the following record which tells the second half of the information.

 

[Golom]

OK. Just use

Select TOP 2 f1, f2, f3, etc.

From tbl

Where f1 >= 'Some Value'

with the caution that, without "Order By" there's no guarantee that records will be returned in the order in which they were entered.

 

[BNPMike]

What Golom is saying is you are using a relational database system on something that is not a relation. You risk getting into trouble. Better to make the data relational, then you have the full power and safety of Access.

Presumably the row-plus-next-row share some common attribute. Add this attribute/identifier to the data so SQL works properly.

 

[willir]

wb8wbn

Mike is right, as is Golom.

Why do you need to record + the next record? Your objective may help us better answer your question. Is this a "level break" issue, or is the first and second record a specific format that you have to cope with? (For example: home and work phone numbers for the same contact?)

Richard

 

[wb8wbn]

The call data is written in sequential format, the common data varies based on the type of call, if it was internal, external, a transfer, etc. The tie could be the trunk group and line number in one set, and in another the original extension. There is also a small variance in the data contained in some fields.

When I import the data, access adds a sequential id number to each record.

 

[wb8wbn]

I forgot to specify the reason for record and record +1.

For some types of transfered calls the first record contains the data for the calling number, the second record contains the data for the called number. You need both records to properly process the call information.

 

[willir]

Okay, it sounds like you may or may not have to use the second record.

I have an Access table that has telephone call data

So the data is already in Access, and you want a query to retrieve the desired record(s). Is this data imported as raw data or is it entered into Access via a data entry screen?

For some types of transfered calls the first record contains the data for the calling number, the second record contains the data for the called number

So now it appears you have a one-to-many (1:M) relationship where you may or may not have a second record.

Typically, in a 1:M relationship there will be a foreign key. Say Call #123456 for the master table, the "primary key" on the Call master table will be 123456. The corresponding Call detail table would a different primary key for each record, but would share the same Call # 123456 as a "foreign key", thus linking the two tables together.

Now, it is still hard to invision a proper solution since we still do not know the data structure of your table or tables.

...But from what I see, it seems that you import data into a raw table, and now you are trying to retrieve the data from the raw table.

Perhaps one approach would be to "process" the raw data table. Sequentially read the table, and update "normalized" tables with the data. In this case, you would use a "level break" within the program to determine if the two (or more records) are related, or not related. This requires VBA coding and if not done, the creation of new (normailized) tables.

Another approach would be to use a query to retrieve the related records BUT to do this we need to know how you know if the subsequent record is related to the first record. If you can easily tell us how the 1st and 2nd records are related, then this approach is do-able.

A third approach would be a combination approach -- create a function that finds the first record, then using the "level break" determines if the subsequent record is related to the first. The function would then return the requried information. This requires VBA coding.

Richard