John,
That is so nearly there, well so far your SQL has succesfully put them all
into the correct order, but I could only see the various record, Id, etc
numbers on the query result, so I added the text fields like this:
SELECT A.Run_No, B.Run_No, A.Point_ID, B.Point_ID, A.OrderSeq, B.OrderSeq,
A.Run_point_Venue,
A.Run_point_Address,
B.Run_point_Venue,
B.Run_point_Address
FROM tbl_Points as A INNER JOIN tbl_Points as B
ON A.Run_No + 1 = B.Run_No
AND A.OrderSeq - 9 = B.OrderSeq
WHICH gives me this:
A.Point_ID A.Run_point_Venue A.Run_point_Address A.OrderSeq A.Run_No B.Run_No B.Point_ID B.OrderSeq B.Run_point_Venue B.Run_point_Address
11 De Vere Hotel 1 Aldwych 10 1 2 19 1 Hilton Hotel Tower Bridge Savoy Court
10 London Shelton Hotel Chancel Street 11 1 2 20 2 Bruebaker Hotel 10 John
Adam Street
12 Costello Palace Hotel Lavington Street 12 1 2 21 3 Parkside Hotel
N4 Cheney Road
BUT what I need is for the B.Run_Point_Venue and B.Run_point_Address to
merge with the A.Run_Point_Venue and A.Run_point_Address respectively, with
the B.Run_Point_Venue going before the A.Run_Point_Venue.
So that I end up with the fields and records like this:
Run_No Point_ID OrderSeq Run_point_Venue Run_point_Address
2 19 1 Hilton Hotel Tower Bridge Savoy Court
2 20 2 Bruebaker Hotel 10 John Adam Street
2 21 3 Parkside Hotel N4 Cheney Road
2 11 10 De Vere Hotel 1 Aldwych
2 10 11 London Shelton Hotel Chancel Street
2 12 12 Costello Palace Hotel Lavington Street
:
To get the last nine to match one for one to the first nine of the next
group, you would need something like the following.
SELECT A.Run_No, B.Run_No
, A.Point_ID, B.Point_ID
, A.OrderSeq, B.OrderSeq
FROM RunTable as A INNER JOIN RunTable as B
ON A.Run_No + 1 = B.Run_No
AND A.OrderSeq - 9 = B.OrderSeq
By the way, Point_ID cannot be the primary key since by definition no
two records can have the same value for the primary key field.
If I understood correctly, that should return
1,2,377,378,10,1
1,2,377,378,11,2
1,2,377,378,12,3
....
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
efandango wrote:
John, (after some sage advice from John W. Vinson, I have changed the table
somewhat and was wondering...
Can you help with this?...
I want to find a similar solution thing to the previous problem, but the key
difference here is that the 18 sets of fields are not seperated into 18
seperate fields, but have been normalized/consolidated into 1 field for Venue
and 1 for Address, but each set is held 'in order' by a [OrderSeq] field that
has a maximum of 18 per set of addresses.
The similar solution being I want to take the last 9 records (of each
OrderSeq) and marry them up with the first 9 records of the next set of 18.
For example:
Run_No 1 (last 9)
Run_No 2 (first 9)
and so on...
My table: (tbl_Points) looks like this:
Run_No (Master Field)
Point_ID (Primary Key)
OrderSeq (Number) orders the address list sequence)
Run_point_Venue (Text
Run_point_Address (Text)
From a user's point of view, the records are broken down into sets of 18,
denoted by a master field, Run_No. They are essentially ordered by the
[OrderSeq] field.
Sample records go like this:
Run_No Point_ID OrderSeq Run_point_Venue Run_point_Address
1
1 377 2 De Vere Hotel 1 Aldwych
1 377 3 London Shelton Hotel Chancel Street
1 377 4 Costello Palace Hotel Lavington Street
1 377 ...18 Hilton Hotel Tower Bridge Savoy Court
2 378 1 Bruebaker Hotel 10 John Adam Street
2 378 2 Parkside Hotel N4 Cheney Road
2 378 3 Elena Hotel Leicester Square
2 378 ...18 Hilton Tower Bridge Hotel Savoy Place
3 379 1 Celstion Hotel 18 Northumberland Avenue
3 379 2 Pembury Hotel Drury Lane
3 379 3 Eric Hotel Lennox Road
3 379 ...18 Holiday Inn Express SE1 Seven Sisters Road
:
No, I won't get notified if you post additional messages to this thread. I
usually keep an eye out, but I have been known to miss a followup.
Which solution did you use? You are always going to be missing half of the
fields in either the first record or the last record.
If you would rather the last record is unmatched then you need to modify the
two queries - whichever one you are using
SELECT A.Run_Point_List_ID
, A.Run_Point_Venue_A
, A.Run_Point_Venue_B
, B.Run_Point_Venue_C
, B.Run_Point_Venue_D
FROM Run_Point as A INNER JOIN Run_Point as B
On A.Run_Point_List_ID = B.Run_Point_List_ID - 1 <<<<<<< minus 1 instead
of plus 1
Max and Less than instead of Min and greater than in the subquery.
SELECT A.Run_Point_List_ID
, A.Run_Point_Venue_A
, A.Run_Point_Venue_B
, B.Run_Point_Venue_C
, B.Run_Point_Venue_D
FROM Run_Point as A , Run_Point as B
WHERE B.Run_Point_List_ID =
(SELECT Max(Tmp.Run_Point_List_ID)
FROM Run_Point as Tmp
WHERE Tmp.Run_Point_List_ID < A.Run_Point_List_ID)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
John,
That is great, but not quite working the way I figured.
The 2nd half of the 1st segment is missing, as in an 'odd man out' kind of
way, where the very first record's 2nd half is (naturally) displaced by
the
next records' 1st half. I'm just trying to take in all the permutations,
as
there are even more fields that I have added, and i'm trying to get my
head
round a screen full of data rows now.
I didn't show the additonal fields in my first post as I didn't want to
make
extra work for someone such as yourself. They follow the same pattern as
the
original fields, just that they go from A-H (first half) I-P (2nd half) of
each record. in all a total of 16 fields laid out spreadsheet style. So,
once
I really get my head around what's what, can I come back to you on this?
(will you get email notification from my response?)
:
SELECT A.Run_Point_List_ID
, A.Run_Point_Venue_A
, A.Run_Point_Venue_B
, B.Run_Point_Venue_C
, B.Run_Point_Venue_D
FROM Run_Point as A INNER JOIN Run_Point as B
On A.Run_Point_List_ID = B.Run_Point_List_ID +1
That will work as long as Run_Point_List_ID is sequential with NO gaps.
If you have gaps then life gets more complex as you will have to
calculate
the next higher number. That Might look something like the following.
SELECT A.Run_Point_List_ID
, A.Run_Point_Venue_A
, A.Run_Point_Venue_B
, B.Run_Point_Venue_C
, B.Run_Point_Venue_D
FROM Run_Point as A , Run_Point as B
WHERE B.Run_Point_List_ID =
(SELECT Min(Tmp.Run_Point_List_ID)
FROM Run_Point as Tmp
WHERE Tmp.Run_Point_List_ID > A.Run_Point_List_ID)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
I have a table:
Run_point_List_ID 1 (Autonumber)
Run_point_Venue_A
Run_point_Venue_B
Run_point_Venue_C
Run_point_Venue_D
How can I get the query to return the first two fields from the 1st
record
and the second two fields from the 2nd record, and so on... like the
example
below?
From Record 1 From Record 1 From Record 2 From Record 2
From Record 2 From Record 2 From Record 3 From Record 3
From Record 3 From Record 3 From Record 4 From Record 4
