Need some help manipulating database columns

[flightsimguy]

I'm working on a database in MS Access 2007 for a flight simulator, and I need to convert rows to columns, It's difficult to explain, so let me show what my problem is.



The data I have to start with looks like this:

<pre>
The data I have to start with looks like this:

Waypoint Lat            Lon    previous/next  minimim-alt   airwayName
00MKK   22.528056   -156.170961    BITTA          12         R464
00MKK   22.528056   -156.170961    CKH99          12         R464
03SML   25.61       30.635278      57SML          195        L321
03SML   25.61       30.635278      AST             85        W8
03SML   25.61       30.635278      KHG             85        W8
03SML   25.61       30.635278      KUNAK          195        L321

I need the data to look like this:

Waypoint  Lat      Lon        AirwayName Previous Next    AirwayName Previous Next 
03SML    25.61     30.635278  L321       57SML    KUNAK   W8         AST      KHG
00MKK    22.52805  -156.1709  R464       BITTA    CKH99   blank      blank    blank
</pre>

For every airway the same waypoint has, I need a new column with the previous and next fields next to it. Each waypoint may have several airways associated with it(usually not more than 10). The order in which the previous and next entries appear is not especially important. From what I've gathered, if this is even possible, this kind of operation can be done using multiple crosstab queries. Any help is appreciated. Thank you.

[June7]

Denormalizing data is not easy and yours looks especially tricky.

VBA code (a LOT of VBA code) might accomplish this. You say it doesn't matter if 57SML, AST, BITTA are Previous and CKH99, KHG, KUNAK are Next or vice versa?

I used Allen Browne's code at http://allenbrowne.com/func-concat.html to get the previous/next pairs for each waypoint and airwayname combination concatenated into one field. I called the function in a query like:
PN: ConcatRelated("[Previous/Next]","Table1","Waypoint='" & [Waypoint] & "' AND airwayName='" & [airwayName] & "'","[airwayName], [Waypoint], [Previous/next]","/")

That is Query1. The full SQL statement is:
SELECT Waypoint, airwayName, ConcatRelated("[Previous/Next]","Table1","Waypoint='" & [Waypoint] & "' AND airwayName='" & [airwayName] & "'","[airwayName], [Waypoint], [Previous/next]","/") AS PN, [Minimum-alt], Lat, Lon
FROM Table1
GROUP BY Waypoint, airwayName, ConcatRelated("[Previous/Next]","Table1","Waypoint='" & [Waypoint] & "' AND airwayName='" & [airwayName] & "'","[airwayName], [Waypoint], [Previous/next]","/"), [Minimum-alt], Lat, Lon
ORDER BY Waypoint, airwayName;

Then I used this query to split the pairs into two columns.
SELECT Waypoint, airwayName, Left([PN],InStr([PN],"/")-1) AS Previous, Mid([PN],InStr([PN],"/")+1) AS [Next], [Minimum-alt], Lat, Lon
FROM Query1;

That gets one previous/next column pair. Getting the records for same waypoint displayed horizontally on one row will take more code. Review this thread http://forums.aspfree.com/microsoft-...ry-322123.html

[flightsimguy]

Thanks for the speedy reply, I'll try that.
BTW, "The Great Land" do you mean Alaska? that's where I am.

[June7]

There is no other! Good luck with the code.

[flightsimguy]

Well, I got the previous/next separated into two columns. I'm a little unsure about the last step you gave in the link, I'll look into it to see what I can make of it.

[June7]

Can you explain why the data needs to be in this structure? Are you sure this is necessary for the program?

[flightsimguy]

That's just the way the software reads the database (It wasn't my idea ). Anyway, thanks a lot for your help. With what I have now, I think I can code it the rest of the way using a script.

[flightsimguy]

OK, I've gotten the data into the form I want it. What I did was import the data into Excel, then I used a its delete duplicates option to remove all duplicates in the waypoint field. Then I pasted the original data below the remaining rows, then ran the delete duplicates function again, this time deleting the duplicate primary ID number (Which Access gave it). Then I took the leftover of the pasted data, and put it into a new table. I ended up having to create 35 tables to get all the different airways listed with their associated data, so I just tested it in access with two. I imported both tables into Access, set up a relationship between the two waypoint fields of both tables. Then I ran a full outer join query, cleaned up the remaining columns, and presto! denormalized database!! When I'm working in excel, I need to order the waypoint column alphabetically, then secondarily order the airway column alphabetically to keep them in groups.