Insert or Update aggregates from another table

[BulbFresh]

Hi,

I'm using Access 2007.
I have imported a table called 'January' and from this I need to update an existing table called 'TablesInfo' which needs to hold some details about the imported tables.

Fields needed are:
[NumRecords] total number of records in the imported table
[DateFrom] earliest date in the Arrival Time field of the imported table
[DateTo] latest date in the Arrival Time field of the imported table

I'm using VB from a command button at the moment to do this but I'm not sure what to use e.g. INSERT INTO or UPDATE or maybe I have to try a join?

Ok I have tested hard wiring in values and this works fine e.g.

DoCmd.RunSQL "INSERT INTO TablesInfo ([NumRecords], [DateFrom], [DateTo]) VALUES ('100', '01/01/2008', '31/01/2008');"
 

But i need to calculate them which brings up errors i.e.

DoCmd.RunSQL "INSERT INTO TablesInfo ([NumRecords], [DateFrom], [DateTo]) SELECT (COUNT (January.[Arrival Time]) AS Count, MIN (January.[Arrival Time]) AS Min, MAX (January.[Arrival Time]) AS Max) FROM January;"
 

Any help would be appreciated
Thanks,

Chris

[JKing]

Hi Chris,

I believe the error is coming from too many brackets in your select portion of the statement.

I made a mock-up of your scenario and the following code worked for me:

Private Sub Command4_Click()
 
Dim strSQL As String
 
strSQL = "INSERT INTO TableInfo ( NumRecords, DateFrom, DateTo ) "
strSQL = strSQL & "SELECT Count(January.[Arrival Time]) AS [CountOfArrival Time], Max(January.[Arrival Time]) AS [MaxOfArrival Time], Min(January.[Arrival Time]) AS [MinOfArrival Time]"
strSQL = strSQL & "FROM January;"
 
 
DoCmd.RunSQL strSQL
 
End Sub
 

Let me know how this works out for you.

Jking

[BulbFresh]

Hi Chris,

I believe the error is coming from too many brackets in your select portion of the statement.

I made a mock-up of your scenario and the following code worked for me:

Private Sub Command4_Click()
 
Dim strSQL As String
 
strSQL = "INSERT INTO TableInfo ( NumRecords, DateFrom, DateTo ) "
strSQL = strSQL & "SELECT Count(January.[Arrival Time]) AS [CountOfArrival Time], Max(January.[Arrival Time]) AS [MaxOfArrival Time], Min(January.[Arrival Time]) AS [MinOfArrival Time]"
strSQL = strSQL & "FROM January;"
 
 
DoCmd.RunSQL strSQL
 
End Sub
 

Let me know how this works out for you.

Jking

Thank you that worked well.

As an extension to this i would like to have another filed called [tablename] this will be captured from the user when tables are imported so 'January' would be a value under tablename with the corresponding count and dates info.

I can't seem to add a variable to the code you suggested. Furthermore i'd like the tablename to be the key.

something like (which returns syntax error)

strSQL = "INSERT INTO TableInfo ( tablename, NumRecords, DateFrom, DateTo ) "
strSQL = strSQL & variable & ", SELECT Count(January.[Arrival Time]) AS [CountOfArrival Time], Max(January.[Arrival Time]) AS [MaxOfArrival Time], Min(January.[Arrival Time]) AS [MinOfArrival Time]"
strSQL = strSQL & "FROM January;"

or would it be better to insert the table name variable then run an update query version of what you suggested. I tried this but i know its wrong

strSQL = "UPDATE TablesInfo SET ( NumRecords, DateFrom, DateTo ) = "
strSQL = strSQL & "SELECT Count(January.[Arrival Time]) AS [CountOfArrival Time], Max(January.[Arrival Time]) AS [MaxOfArrival Time], Min(January.[Arrival Time]) AS [MinOfArrival Time]"
strSQL = strSQL & "FROM January WHERE [TablesInfo.tableName] = '" & variable & "';"

Thanks,
Chris

[BulbFresh]

...and obviously the other places where the table name 'January' is would be replaced with the variable....

[NeoPa]

Please remember to use the [ CODE ] tags provided (See the # button when posting).