Update or Append query help

Update or Append query help

Post by bWNjbG91Z » Sat, 02 May 2009 03:26:33

I have several queries that build data and add to a SQL table based on a date
prompt (example 200904). This works fine and usally is run once a month for
the previous month. Data is then used in reports.

INSERT INTO dbo_loy_hist ( rebate_amt, Insert_dt, [Month], Cus_no,
All_sales, Hp_sales, Comp_sales, Rebate_1_onrun, Rebate_2_onrun,
Rebate_3_onrun, Rebate_4_onrun, OEM_sales, Comp_perc, HP_Perc, [qualifier],
Month_profit, Qualifier_perc, Customer, slspsn_no, slspsn_email, datakey )
SELECT IIf([data-2].[qualifier]="OEM",([oem sales]*([qualifier
percent]*0.01)),IIf([data-2].[qualifier]="HP",([hp sales]*([qualifier
percent]*0.01)))) AS rebate_amt, (Now()) AS [Insert date], [Data-2].YearMon,
[Data-2].Cus_no, [Data-2].[Month Total Sales], [Data-2].[HP Sales],
[Data-2].[Comp Sales], [Data-2].Rebate_1, [Data-2].Rebate_2,
[Data-2].Rebate_3, [Data-2].Rebate_4, [Data-2].[OEM Sales], [Data-2].[Comp
percent], [Data-2].[HP Percent], [Data-2].Qualifier, [Data-2].Month_profit,
[Data-2].[Qualifier Percent], [Data-2].Customer, [Data-2].slspsn_no,
[Data-2].email_addr, [data-2].[yearmon] & [data-2].[cus_no] AS datakey
FROM [Data-2] LEFT JOIN dbo_loy_hist ON ([Data-2].YearMon =
dbo_loy_hist.Month) AND ([Data-2].Cus_no = dbo_loy_hist.cus_no)
WHERE ((([Data-2].YearMon) Is Not Null));

My issue...
My user needs to refresh the data when new accounts are added to the
program. I've tried creating an update query but I get this error "must use
an updatable query". I've created a make table query "data3" and I can run
the update using the table just fine.

My question...
Can I call/trigger "data3" (make query) to run from the update query?
If not is there a better approach to solving this issue.


Update or Append query help

Post by KC-Mas » Sat, 02 May 2009 12:17:18


When you want to establish new records for new acounts you want an "append"
query that
will INSERT new records into the existing table reflecting new accounts.
When you want to change the data in existing records you will want an UPDATE
query where you can change a last name from "Smith" to "Jones". Lastly, if
you want to create a new table that does not now exist from existing data
you use a MAKE Table query wherein you CREATE a table from data. There are
other options but for your question I think this is it.

As to your question of can one query kick of another, the answer is "No".
But a small piece of VBA code can kick off both queries.




Update or Append query help

Post by bWNjbG91Z » Sun, 03 May 2009 00:08:09

Thanks for the quick reply.

Do you have samples of the VB code two run both queries? Can I run the code
from the query or do I need to a form?