Merge with dates in MS Access

Question

I am a pilot who flies several legs a day. The software I use to register flights spits out a csv file and lists each leg separately. I am importing a csv file into table 1 as ms. I would like to combine all flights from the same day into one entry on a new table. My problem is combining the route and adding 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 for this, but I have not programmed in 12 years and, unfortunately, did not manage to relearn. I don't think the code should be too complicated, it seems pretty simple. I just don't know how to do this. Hope someone can help me. Thanks in advance.

: MS Access 97 (, )/ - , / ​​ / 1/ 80 , /

Answer 1

, Date Time . Date Group By Total Row, Time Sum. , , To Totals Last.

, , ​​:

http://www.mvps.org/access/modules/mdl0004.htm

FROM , . Expression.

, FROM LAST TO.

, . ( , , ) ( ) , , .

Answer 2

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

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

ACE (Access 2007), Jet 3.51 (Access97) . SQL ( SQL- Access Database Engine) "Concatenate", (1NF), . - SQL-. .

1NF, , , . , "", "" DATETIME, , " ", . a CHECK, . !

Answer 4

. "Then's", (, ). , , . , csv , , . ( 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

:

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

:

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;

, "" openrecordset, , . , . , "To" openrecordset, , . , , , . .