2

Using a hybrid Access 2010 / SQL Server 2012 platform - (a solution in either application will work)

I have a table created with a Select Into ... statement.

Is there any way to have this table dynamically update itself (using the query that created it) whenever its data is accessed?

Specifically, we want to keep a list of customers with only one order (non-repeat customers). I have created a table that contains the ID of those customers (WHERE COUNT(orderID) = 1) using Select Into, but if one of those customers makes a new order, or a new customer who makes one order is created, then I want that data removed/added to the table dynamically.

So, is this possible, or do I have to remember to update the table myself whenever I use it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mcalex
  • 6,628
  • 5
  • 50
  • 80
  • 1
    I would use a MSSQL [trigger](http://msdn.microsoft.com/en-us/magazine/cc164047.aspx) to update/insert/remove the data as necessary when the original table changes – CodingIntrigue Sep 19 '13 at 07:56
  • 1
    Instead of using a table, why don't you create a view with the SELECT * FROM table WHERE COUNT(ORDERID) = 1 – Steve Ford Sep 19 '13 at 07:58
  • Can I create a trigger in Access or is that solution for Sql Server? Ditto for views? – mcalex Sep 19 '13 at 08:24
  • 2
    You can do that in Sql Server. I'd go for the VIEW option. Also you can just make a query in Access, to work with. – Wietze314 Sep 19 '13 at 08:35

1 Answers1

1

I have a table created with a Select Into ... statement. [...] Is there any way to have this table dynamically update itself (using the query that created it) whenever its data is accessed?

What you've described is a SQL VIEW, also called a "(saved) SELECT Query" in Access. A View is a virtual table that dynamically retrieves its information from other tables (or views) each time it is accessed. The view does not save its results between calls, so each time you reference it you get the most current data. See if you can use a VIEW (in SQL Server) or a saved SELECT Query (in Access) in place of the temporary table you are currently creating.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418