Merge like dates in MS Access

Question

I am a pilot who flies multiple legs in a day. The software I use to log flights spits out a csv file and lists every leg separately. I import the csv file into table 1 in ms access. I would like to merge all flights from the same day into one record on a new table. My problem is combining the route and adding the time.

Table 1

   Date       Plane     From     To     Time
2009-10-13    111WS     CHO      LGA    120
2009-10-13    111WS     LGA      ITH    100
2009-10-13    111WS     ITH      LGA     90
2009-10-13    111WS     LGA      BOS    110

Table 2

   Date       Plane          Route            Time
2009-10-13    111WS    CHO-LGA-ITH-LGA-BOS     420

I would like to use VBA code to do this, but I haven't done any programming in 12 years and unfortunately don't have the time to relearn. I don't think the code has to be too elaborate, it seems pretty straightforward. I just don't know how to do it. I hope someone can help me out. Thanks in advance.

Note: I am using MS Access 97 (hope that's not a problem)/ The date field is a string, not a date/ The time is in minutes, and can stay that way/ There normally will not be more than 80 records in table 1/ There can be anywhere from one to eight flights in one day/

Answer 1

Create a Totals query, bring in your table, and include the Date and Time as columns. The Date Column should be set to Group By in the Total Row, and the Time should be set to Sum. You will also need another column to get the final entry in the route, so put the To column in the grid also, and set the Totals row for that column to Last.

To get the remainder of the route, you will need to use a combining function like this one:

Return a concatenated list of sub-record values
http://www.mvps.org/access/modules/mdl0004.htm

This will combine the FROM column into a single value, which you can include as another column in the output. Set the Total row for this column to Expression.

To get the complete route, combine the concatenated FROM columm with the LAST TO column.

Note that you don't need to build the entire query at once. Build each of the three pieces (total time, concatenated route, ending destination) individually (in its own query), and make sure each piece works individually, before combining them into a single query.

Answer 2

Add module

Public Function ConcatField(FieldName As String, TableName As String, Where As String, Optional Delimeter = "-", Optional OrderBy = "") As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT " & FieldName & " FROM " & TableName & " WHERE " & Where & IIf(OrderBy > "", " ORDER BY " & OrderBy, ""))
    ConcatField = DLookup("From", "RTE", Where)
    While Not rs.EOF
        ConcatField = ConcatField + IIf(ConcatField = "", "", Delimeter) + rs.Fields(0)
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
End Function

and run query

Worked on mine

SELECT rte.Date, rte.Plane, ConcatField("to","rte","Date='" & [Date] & "' AND Plane='" & [Plane] & "'") AS Expr1, Sum(rte.Time) AS SumOfTime
FROM rte
GROUP BY rte.Date, rte.Plane, ConcatField("to","rte","Date='" & [Date] & "' AND Plane='" & [Plane] & "'");
enter code here

Answer 3

Unlike ACE (Access 2007), the Jet 3.51 engine (Access97) doesn't have multivalue types. SQL the language (including the Access Database Engine's own proprietary SQL) does not have a 'Concatenate' function because it would be a violation of first normal form (1NF) which requires scalar types. So this isn't something for a SQL query. Sounds to me more like a candidate for a report.

Speaking of 1NF, considering it is possible to fly to the same destination twice in one day, your table lacks a relational key. It sounds like you need to replace you single 'date' column that is typed as 'text' with a pair of DATETIME values representing a period, with the required 'sequenced primary key' e.g. a CHECK constraint to prevent overlapping periods. Temporal databases are definitely non-trivial!

Answer 4

Thanks for all your responses. I used "THEn's" answer, but I had to change a few things (hope that's not a problem). I only needed the flights grouped by date, so I took out the grouping by plane, and just logged the first plane on the first leg of that day. Also I just found out that my software exports the csv file in reverse order, so I changed the module a little to account for this. So this is what the imported data looks like (I start and end in CHO):

  Date       Plane     From     To     Time
2009-10-14    111WS     LGA      CHO    120
2009-10-14    111WS     BOS      LGA    110
2009-10-13    111WS     LGA      BOS    110
2009-10-13    111WS     ITH      LGA     90
2009-10-13    111WS     LGA      ITH    100
2009-10-13    111WS     CHO      LGA    120

This is the Module:

Public Function ConcatField(FieldName As String, TableName As String, Where As String, Optional Delimeter = "-") As String

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT " & FieldName & " FROM " & TableName & " WHERE " & Where)
rs.MoveLast
While Not rs.BOF
    ConcatField = ConcatField + IIf(ConcatField = "", "", Delimeter) + rs.Fields(0)
    rs.MovePrevious
Wend
ConcatField = ConcatField + "-" + DLookup("To", "rte", Where)
rs.Close
Set rs = Nothing
End Function

This is the query:

SELECT rte.Date, First(rte,plane), ConcatField("From","rte","Date='" & [Date] & "'") AS Expr1, Sum(rte.time) AS [Total Time]
FROM rte
GROUP BY rte.Date;

This causes a problem because I'm using a field called "From" in the openrecordset line, I tried renaming the field to something else and it worked perfectly. However I was hoping to keep the field names the way they are. It worked when I was using the field name "To" in the openrecordset line, but then I was running into a problem with the data being in reverse order. So I was looking for any suggestions, but I would like to keep the field names the same, and I would like to keep the table in reverse order if possible. Thanks again guys.