Update or Append query help

[mccloud]

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.

Thanks

 

[KC-Mass]

Hi

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.

Regards

Kevin

 

[mccloud]

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?

Hi

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.

Regards

Kevin

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.

Thanks