467,879 Members | 1,301 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

Insert or Update aggregates from another table

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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO TablesInfo ([NumRecords], [DateFrom], [DateTo]) VALUES ('100', '01/01/2008', '31/01/2008');"
  2.  
But i need to calculate them which brings up errors i.e.
Expand|Select|Wrap|Line Numbers
  1. 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;"
  2.  
Any help would be appreciated
Thanks,

Chris
Feb 25 '08 #1
  • viewed: 2010
Share:
4 Replies
JKing
Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click()
  2.  
  3. Dim strSQL As String
  4.  
  5. strSQL = "INSERT INTO TableInfo ( NumRecords, DateFrom, DateTo ) "
  6. strSQL = strSQL & "SELECT Count(January.[Arrival Time]) AS [CountOfArrival Time], Max(January.[Arrival Time]) AS [MaxOfArrival Time], Min(January.[Arrival Time]) AS [MinOfArrival Time]"
  7. strSQL = strSQL & "FROM January;"
  8.  
  9.  
  10. DoCmd.RunSQL strSQL
  11.  
  12. End Sub
  13.  
Let me know how this works out for you.

Jking
Feb 26 '08 #2
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command4_Click()
  2.  
  3. Dim strSQL As String
  4.  
  5. strSQL = "INSERT INTO TableInfo ( NumRecords, DateFrom, DateTo ) "
  6. strSQL = strSQL & "SELECT Count(January.[Arrival Time]) AS [CountOfArrival Time], Max(January.[Arrival Time]) AS [MaxOfArrival Time], Min(January.[Arrival Time]) AS [MinOfArrival Time]"
  7. strSQL = strSQL & "FROM January;"
  8.  
  9.  
  10. DoCmd.RunSQL strSQL
  11.  
  12. End Sub
  13.  
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)
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO TableInfo ( tablename, NumRecords, DateFrom, DateTo ) "
  2. 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]"
  3. 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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE TablesInfo SET ( NumRecords, DateFrom, DateTo ) = "
  2. strSQL = strSQL & "SELECT Count(January.[Arrival Time]) AS [CountOfArrival Time], Max(January.[Arrival Time]) AS [MaxOfArrival Time], Min(January.[Arrival Time]) AS [MinOfArrival Time]"
  3. strSQL = strSQL & "FROM January WHERE [TablesInfo.tableName] = '" & variable & "';"
Thanks,
Chris
Feb 26 '08 #3
...and obviously the other places where the table name 'January' is would be replaced with the variable....
Feb 26 '08 #4
NeoPa
Expert Mod 16PB
Please remember to use the [ CODE ] tags provided (See the # button when posting).
Feb 26 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
16 posts views Thread by robert | last post: by
4 posts views Thread by =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.