Update Query Overwrite?

  • Thread starter Thread starter Brandon M
  • Start date Start date
B

Brandon M

I'm trying to get an Update Query to overwrite any records that already
exist. By default it appears to skip any records in which the key already
exists. Is there any way to change this?
 
Likely you have an incorrect criterion for a field in the query.

Post the SQL of the query and explain what the criterion is supposed to do.
 
This pulls together information from a few different queries and stores it
under the date entered into frmUpdate.txtSaveDate. Now if DateRec already
exists then it does not replace that line in the table. DateRec is the
primary key as there will only ever be one entry per day.

INSERT INTO tblBranchStats ( DateRec, CallTime_0715, CallTime_1523,
CallTime_2307 )
SELECT CDate(forms.frmUpdate.txtSaveDate) AS DateRec,
(DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime) AS
CallTime0715,
(DailyBranchStats1523.TotalTalkTime/DailyBranchStats1523.TotalOnTime) AS
CallTime1523,
(DailyBranchStats2307.TotalTalkTime/DailyBranchStats2307.TotalOnTime) AS
CallTime2307
FROM DailyBranchStats0715, DailyBranchStats1523, DailyBranchStats2307;
 
You have posted an "append" query's SQL statement, not an "update" query's
SQL statement. I'm confused.....you describe in previous posts that you want
to "update" already existing records, but this post's description and the
query's SQL statement will add new records to the table, not modify existing
ones.

Which do you want to do?

Also, your SQL is using three tables but they are not joined, thus you're
getting a cartesian "join" on the tables (you'll get a record for each
combination of records in all three tables). Is that what you want?

As I don't know what the fields are in the tables, I don't know if a
cartesian join will be a problem or not...there are appropriate uses for
them in some queries. But before I suggest anything else, please clarify for
me just exactly what you want this query to do.
 
OK - but an append query will not overwrite existing records' data...it only
will add new records. From your posts, I'm not clear as to which action you
really want to occur.

So which is it that you want to happen? Add new records? Overwrite existing
records? Or something else?
 
I want to overwrite existing records... which goes against the meaning of
append I guess. So I guess my question should be is there a way to do it?
 
Assuming that your posted SQL will properly provide the data you want (which
I admit is still a puzzle to me what it's going to give you), you need to
change it to an update query...something like this (not tested, obviously,
so test it on a copy of your data):

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec)
INNER JOIN DailyBranchStats2307 ON tblBranchStats.DateRec =
DailyBranchStats2307.DateRec
SET tblBranchStats.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
tblBranchStats.CallTime_0715 =
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
DailyBranchStats1523.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
DailyBranchStats2307.TotalTalkTime/DailyBranchStats2307.TotalOnTime

But this is just a guess at the moment based on my admittedly incomplete
understanding of what you have in your database forms and tables.
 
As you have already helped me out with two of the problems I've had I'll
give you a little background. I work in a call centre and all of the
information on the different operators and the calls they have had are all
stored in an access file. I am trying to pull the numbers and turn them into
statistics on a branch and an operator level. I used a slightly modified
version of what you wrote and had one (and hopefully the last) problem:

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec) INNER JOIN DailyBranchStats2307 ON
tblBranchStats.DateRec = DailyBranchStats2307.DateRec SET
tblBranchStats.DateRec = CDate(Forms!frmUpdate!txtSaveDate),
tblBranchStats.CallTime_0715 =
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
DailyBranchStats1523.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
DailyBranchStats2307.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats2307.TotalOnTime;

When this query is run it gives me the error "Operation must use an
updateable query" (Error 3073). I've looked through the help and my best
guess is that the database is read-only (which I don't think is possible) or
it is trying to update the table with invalid information.
In the table tblBranchStats I have DateRec As Date/Time and the other values
as single so that I can handle the decimal numbers which will result from
the TalkTime/OnTime equation. I added DateRec to all of the queries in an
effort to solve the problem thinking that maybe there was a reason behind
the relationships you had put in yours. Which is simply the
frmUpdate.txtSaveDate value all over again.



Ken Snell said:
Assuming that your posted SQL will properly provide the data you want (which
I admit is still a puzzle to me what it's going to give you), you need to
change it to an update query...something like this (not tested, obviously,
so test it on a copy of your data):

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec)
INNER JOIN DailyBranchStats2307 ON tblBranchStats.DateRec =
DailyBranchStats2307.DateRec
SET tblBranchStats.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
tblBranchStats.CallTime_0715 =
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
DailyBranchStats1523.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
DailyBranchStats2307.TotalTalkTime/DailyBranchStats2307.TotalOnTime

But this is just a guess at the moment based on my admittedly incomplete
understanding of what you have in your database forms and tables.

--
Ken Snell
<MS ACCESS MVP>

Brandon M said:
I want to overwrite existing records... which goes against the meaning of
append I guess. So I guess my question should be is there a way to do it?


data...it
only action
you right
now! that
you uses
for is
the the
key
Click to expand...
Click to expand...
 
OK - time for me to admit that I was almost asleep when I posted my last
response...how else to explain how I missed the obvious error that you now
are getting re: nonupdatable query....(yet, now that I look back, the info
about the subqueries was in a different thread, and I just didn't connect
the dots).

I apologize....because your subqueries contain Sum functions in them, the
overall query becomes nonupdatable (the Sum functions are part of a "totals"
query, and such a query loses its uniqueness).

One way to get around this is to use the DSum function in a query to get a
sum of values from one field in a table....however, if you have a lot of
records that need to be summed/selected, this can be very slow. However, it
avoids the need to build a temporary table (see next option).

A second way is to rewrite the subqueries so that they append records into a
temporary table, and then use that temporary table in the main query instead
of the subqueries. This option avoids having Sum functions in the queries
because you put the results of those subqueries into a table and then the
values are just like any other table.

Here is an untested stab at how to use the first option:

UPDATE tblBranchStats AS T
SET T.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
T.CallTime_0715 = (DSum("TotalTalkTime", "DailyBranchStats0715", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats071", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#")),
T.CallTime_1523 =(DSum("TotalTalkTime", "DailyBranchStats1523", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats1523", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#")),
T.CallTime_2307 = (DSum("TotalTalkTime", "DailyBranchStats2307", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats2307", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#"));



--
Ken Snell
<MS ACCESS MVP>


Brandon M said:
As you have already helped me out with two of the problems I've had I'll
give you a little background. I work in a call centre and all of the
information on the different operators and the calls they have had are all
stored in an access file. I am trying to pull the numbers and turn them into
statistics on a branch and an operator level. I used a slightly modified
version of what you wrote and had one (and hopefully the last) problem:

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec) INNER JOIN DailyBranchStats2307 ON
tblBranchStats.DateRec = DailyBranchStats2307.DateRec SET
tblBranchStats.DateRec = CDate(Forms!frmUpdate!txtSaveDate),
tblBranchStats.CallTime_0715 =
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
DailyBranchStats1523.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
DailyBranchStats2307.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats2307.TotalOnTime;

When this query is run it gives me the error "Operation must use an
updateable query" (Error 3073). I've looked through the help and my best
guess is that the database is read-only (which I don't think is possible) or
it is trying to update the table with invalid information.
In the table tblBranchStats I have DateRec As Date/Time and the other values
as single so that I can handle the decimal numbers which will result from
the TalkTime/OnTime equation. I added DateRec to all of the queries in an
effort to solve the problem thinking that maybe there was a reason behind
the relationships you had put in yours. Which is simply the
frmUpdate.txtSaveDate value all over again.



Ken Snell said:
Assuming that your posted SQL will properly provide the data you want (which
I admit is still a puzzle to me what it's going to give you), you need to
change it to an update query...something like this (not tested, obviously,
so test it on a copy of your data):

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec)
INNER JOIN DailyBranchStats2307 ON tblBranchStats.DateRec =
DailyBranchStats2307.DateRec
SET tblBranchStats.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
tblBranchStats.CallTime_0715 =
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
DailyBranchStats1523.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
DailyBranchStats2307.TotalTalkTime/DailyBranchStats2307.TotalOnTime

But this is just a guess at the moment based on my admittedly incomplete
understanding of what you have in your database forms and tables.

--
Ken Snell
<MS ACCESS MVP>

Brandon M said:
I want to overwrite existing records... which goes against the meaning of
append I guess. So I guess my question should be is there a way to do it?


OK - but an append query will not overwrite existing records' data...it
only
will add new records. From your posts, I'm not clear as to which action
you
really want to occur.

So which is it that you want to happen? Add new records? Overwrite
existing
records? Or something else?

--
Ken Snell
<MS ACCESS MVP>

I'm sorry... yes I did mean an append query too much in my head right
now!


You have posted an "append" query's SQL statement, not an "update"
query's
SQL statement. I'm confused.....you describe in previous posts that
you
want
to "update" already existing records, but this post's
Click to expand...
description
and
the
query's SQL statement will add new records to the table, not modify
existing
ones.

Which do you want to do?

Also, your SQL is using three tables but they are not joined, thus
you're
getting a cartesian "join" on the tables (you'll get a record
Click to expand...
for
each
combination of records in all three tables). Is that what you want?

As I don't know what the fields are in the tables, I don't know
Click to expand...
Click to expand...
if
a
Click to expand...
DateRec
is
Click to expand...
Click to expand...
 
Thank-you again for some amazing advice. I ended up using the second method
for one of my issues and the first method for another which did not have
near as many records.


Ken Snell said:
OK - time for me to admit that I was almost asleep when I posted my last
response...how else to explain how I missed the obvious error that you now
are getting re: nonupdatable query....(yet, now that I look back, the info
about the subqueries was in a different thread, and I just didn't connect
the dots).

I apologize....because your subqueries contain Sum functions in them, the
overall query becomes nonupdatable (the Sum functions are part of a "totals"
query, and such a query loses its uniqueness).

One way to get around this is to use the DSum function in a query to get a
sum of values from one field in a table....however, if you have a lot of
records that need to be summed/selected, this can be very slow. However, it
avoids the need to build a temporary table (see next option).

A second way is to rewrite the subqueries so that they append records into a
temporary table, and then use that temporary table in the main query instead
of the subqueries. This option avoids having Sum functions in the queries
because you put the results of those subqueries into a table and then the
values are just like any other table.

Here is an untested stab at how to use the first option:

UPDATE tblBranchStats AS T
SET T.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
T.CallTime_0715 = (DSum("TotalTalkTime", "DailyBranchStats0715", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats071", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#")),
T.CallTime_1523 =(DSum("TotalTalkTime", "DailyBranchStats1523", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats1523", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#")),
T.CallTime_2307 = (DSum("TotalTalkTime", "DailyBranchStats2307", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats2307", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#"));



--
Ken Snell
<MS ACCESS MVP>


Brandon M said:
As you have already helped me out with two of the problems I've had I'll
give you a little background. I work in a call centre and all of the
information on the different operators and the calls they have had are all
stored in an access file. I am trying to pull the numbers and turn them into
statistics on a branch and an operator level. I used a slightly modified
version of what you wrote and had one (and hopefully the last) problem:

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec) INNER JOIN DailyBranchStats2307 ON
tblBranchStats.DateRec = DailyBranchStats2307.DateRec SET
tblBranchStats.DateRec = CDate(Forms!frmUpdate!txtSaveDate),
tblBranchStats.CallTime_0715 =
Click to expand...
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
Click to expand...
DailyBranchStats1523.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
Click to expand...
DailyBranchStats2307.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats2307.TotalOnTime;

When this query is run it gives me the error "Operation must use an
updateable query" (Error 3073). I've looked through the help and my best
guess is that the database is read-only (which I don't think is
Click to expand...
possible)
or
it is trying to update the table with invalid information.
In the table tblBranchStats I have DateRec As Date/Time and the other values
as single so that I can handle the decimal numbers which will result from
the TalkTime/OnTime equation. I added DateRec to all of the queries in an
effort to solve the problem thinking that maybe there was a reason behind
the relationships you had put in yours. Which is simply the
frmUpdate.txtSaveDate value all over again.



Ken Snell said:
Assuming that your posted SQL will properly provide the data you want (which
I admit is still a puzzle to me what it's going to give you), you need to
change it to an update query...something like this (not tested, obviously,
so test it on a copy of your data):

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec)
INNER JOIN DailyBranchStats2307 ON tblBranchStats.DateRec =
DailyBranchStats2307.DateRec
SET tblBranchStats.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
tblBranchStats.CallTime_0715 =
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
DailyBranchStats1523.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
DailyBranchStats2307.TotalTalkTime/DailyBranchStats2307.TotalOnTime

But this is just a guess at the moment based on my admittedly incomplete
understanding of what you have in your database forms and tables.

--
Ken Snell
<MS ACCESS MVP>

I want to overwrite existing records... which goes against the
Click to expand...
meaning
of
append I guess. So I guess my question should be is there a way to
Click to expand...
do
it?
OK - but an append query will not overwrite existing records' data...it
only
will add new records. From your posts, I'm not clear as to which action
you
really want to occur.

So which is it that you want to happen? Add new records? Overwrite
existing
records? Or something else?

--
Ken Snell
<MS ACCESS MVP>

I'm sorry... yes I did mean an append query too much in my head right
now!


You have posted an "append" query's SQL statement, not an "update"
query's
SQL statement. I'm confused.....you describe in previous posts that
you
want
to "update" already existing records, but this post's description
and
the
query's SQL statement will add new records to the table, not modify
existing
ones.

Which do you want to do?

Also, your SQL is using three tables but they are not joined, thus
you're
getting a cartesian "join" on the tables (you'll get a record for
each
combination of records in all three tables). Is that what you
Click to expand...
want?
As I don't know what the fields are in the tables, I don't
Click to expand...
Click to expand...
know
if appropriate
uses DateRec which
the
Click to expand...
Click to expand...
 
You're welcome. Glad you found the advice useful!
--
Ken Snell
<MS ACCESS MVP>

Brandon M said:
Thank-you again for some amazing advice. I ended up using the second method
for one of my issues and the first method for another which did not have
near as many records.


Ken Snell said:
OK - time for me to admit that I was almost asleep when I posted my last
response...how else to explain how I missed the obvious error that you now
are getting re: nonupdatable query....(yet, now that I look back, the info
about the subqueries was in a different thread, and I just didn't connect
the dots).

I apologize....because your subqueries contain Sum functions in them, the
overall query becomes nonupdatable (the Sum functions are part of a "totals"
query, and such a query loses its uniqueness).

One way to get around this is to use the DSum function in a query to get a
sum of values from one field in a table....however, if you have a lot of
records that need to be summed/selected, this can be very slow.
Click to expand...
However,
it
avoids the need to build a temporary table (see next option).

A second way is to rewrite the subqueries so that they append records
Click to expand...
into
a
temporary table, and then use that temporary table in the main query instead
of the subqueries. This option avoids having Sum functions in the queries
because you put the results of those subqueries into a table and then the
values are just like any other table.

Here is an untested stab at how to use the first option:

UPDATE tblBranchStats AS T
SET T.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
T.CallTime_0715 = (DSum("TotalTalkTime", "DailyBranchStats0715", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats071", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#")),
T.CallTime_1523 =(DSum("TotalTalkTime", "DailyBranchStats1523", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats1523", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#")),
T.CallTime_2307 = (DSum("TotalTalkTime", "DailyBranchStats2307", "DateRec=#"
& Format(T.DateRec, "mm/dd/yyyy") & "#") /
DSum("TotalOnTime", "DailyBranchStats2307", "DateRec=#" & Format(T.DateRec,
"mm/dd/yyyy") & "#"));



--
Ken Snell
<MS ACCESS MVP>


Brandon M said:
As you have already helped me out with two of the problems I've had I'll
give you a little background. I work in a call centre and all of the
information on the different operators and the calls they have had are all
stored in an access file. I am trying to pull the numbers and turn
Click to expand...
them
into
statistics on a branch and an operator level. I used a slightly modified
version of what you wrote and had one (and hopefully the last) problem:

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec) INNER JOIN DailyBranchStats2307 ON
tblBranchStats.DateRec = DailyBranchStats2307.DateRec SET
tblBranchStats.DateRec = CDate(Forms!frmUpdate!txtSaveDate),
tblBranchStats.CallTime_0715 =
Click to expand...
Click to expand...
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyBDailyBranchStats1523.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyBDailyBranchStats2307.TotalTalkTime/DailyBranchStats0715.TotalTalkTime/DailyB
ranchStats0715.TotalTalkTime/DailyBranchStats2307.TotalOnTime;

When this query is run it gives me the error "Operation must use an
updateable query" (Error 3073). I've looked through the help and my best
guess is that the database is read-only (which I don't think is
Click to expand...
possible)
or
it is trying to update the table with invalid information.
In the table tblBranchStats I have DateRec As Date/Time and the other values
as single so that I can handle the decimal numbers which will result from
the TalkTime/OnTime equation. I added DateRec to all of the queries in an
effort to solve the problem thinking that maybe there was a reason behind
the relationships you had put in yours. Which is simply the
frmUpdate.txtSaveDate value all over again.



Assuming that your posted SQL will properly provide the data you want
(which
I admit is still a puzzle to me what it's going to give you), you
Click to expand...
need
to
change it to an update query...something like this (not tested, obviously,
so test it on a copy of your data):

UPDATE ((tblBranchStats INNER JOIN DailyBranchStats0715 ON
tblBranchStats.DateRec = DailyBranchStats0715.DateRec) INNER JOIN
DailyBranchStats1523 ON tblBranchStats.DateRec =
DailyBranchStats1523.DateRec)
INNER JOIN DailyBranchStats2307 ON tblBranchStats.DateRec =
DailyBranchStats2307.DateRec
SET tblBranchStats.DateRec = CDate([Forms].[frmUpdate].[txtSaveDate]),
tblBranchStats.CallTime_0715 =
DailyBranchStats0715.TotalTalkTime/DailyBranchStats0715.TotalOnTime,
tblBranchStats.CallTime_1523 =
DailyBranchStats1523.TotalTalkTime/DailyBranchStats1523.TotalOnTime,
tblBranchStats.CallTime_2307 =
DailyBranchStats2307.TotalTalkTime/DailyBranchStats2307.TotalOnTime

But this is just a guess at the moment based on my admittedly incomplete
understanding of what you have in your database forms and tables.

--
Ken Snell
<MS ACCESS MVP>

I want to overwrite existing records... which goes against the meaning
of
append I guess. So I guess my question should be is there a way to do
it?


OK - but an append query will not overwrite existing records'
data...it
only
will add new records. From your posts, I'm not clear as to which
action
you
really want to occur.

So which is it that you want to happen? Add new records? Overwrite
existing
records? Or something else?

--
Ken Snell
<MS ACCESS MVP>

I'm sorry... yes I did mean an append query too much in my head
right
now!


You have posted an "append" query's SQL statement, not an "update"
query's
SQL statement. I'm confused.....you describe in previous posts
that
you
want
to "update" already existing records, but this post's description
and
the
query's SQL statement will add new records to the table, not
modify
existing
ones.

Which do you want to do?

Also, your SQL is using three tables but they are not
Click to expand...
joined,
thus
you're
getting a cartesian "join" on the tables (you'll get a
Click to expand...
record
for
each
combination of records in all three tables). Is that what
Click to expand...
Click to expand...
you
know queries
and
Click to expand...
Click to expand...
 
Back
Top