Updatequery in Access 2003

B

Bjørn C. Winkel

Hi group

First sorry for my english, it is not my mothertongue.

I have a dunning database in Access 2003 on Windows XP.
I need some kind of update query that copies f.ex. comment3 in old date to
comment1 in new date.
Example (sorry for the eventual taberrors):

Date Customer comment1 Comment2 Comment3
01.04.09 1 01.04 Dunn. 07.04 Dunn2 12.04
Dunn3
16.04.09 1 12.04 Dunn3
01.04.09 2 01.04 Called 08.04 Said that...
16.04.09 2 08.04 Said that...
and so on.

What I am trying to do is that when my colleague and I make filter on the
new date in the query we can, in Comment1, se what was last done.
It is not allways Comment3 that has to be copied to Comment1, sometimes
there are only text in Comment1 or Comment2, but it is allways the highest
nummbered field that has to be copied to the new Comment1-field.

I don't know how to do that so any help would be greately appreciated.

Thank you in advance.

Best regards
Bjørn C. Winkel
 
B

Beetle

If there can be more than one Comment per customer record,
then the comments should be in a separate table, related 1:m
with the Customer table. The Comments table would have a
CommentDate field with a default value of Now() which would
record the Date and Time the comment was entered.

Then you would use a subform to display the related comments
on your main form. That way you could easily see the whole history
of comments with the most recent one at the top or the bottom
depending on how you sort the records. Or, if you only want to see
the most recent comment, you could base the subform on a query
like;

Select CustomerID, Max(CommentDate) As Expr1, Comments
From tblComments
 
B

Bjørn C. Winkel

Hi Sean

Thanks for your input.

We do have a table with customers, names addresses and so on, and another
table with the open items grouped by age, total, not due, up to 30 days, 60
days, 120days, more than 120 and so on.
We also have a form where we input the customer no. in a combobox and there
can see the whole history in a subform from a single customer.

But we think it is easyer with a datasheet like query to take quick glance
and see what customers has to be dealt with. So therefore we thought it
would be easyer with the datasheet like list which has the columns date of
open items, Customer no, total, not due, 30days, 60 days ........, Comment1,
Comment2, Comment3.
That way we have a little history for each time we run the open items list
from SAP via Excel to Access. Acces is updated with new open item customers
on the 10. 20. and the last day of the month.

That is the reason we want the last comment to be copied to the new dates
Comment1-field. So when filtered to the new date we can se the last event.

I hope I have made myself understood?
 
B

Bjørn C. Winkel

MGFoster said:
Bjørn C. Winkel said:
Hi group

First sorry for my english, it is not my mothertongue.

I have a dunning database in Access 2003 on Windows XP.
I need some kind of update query that copies f.ex. comment3 in old date
to comment1 in new date.
Example (sorry for the eventual taberrors):

Date Customer comment1 Comment2
Comment3
01.04.09 1 01.04 Dunn. 07.04 Dunn2
12.04 Dunn3
16.04.09 1 12.04 Dunn3
01.04.09 2 01.04 Called 08.04 Said that...
16.04.09 2 08.04 Said that...
and so on.

What I am trying to do is that when my colleague and I make filter on the
new date in the query we can, in Comment1, se what was last done.
It is not allways Comment3 that has to be copied to Comment1, sometimes
there are only text in Comment1 or Comment2, but it is allways the
highest nummbered field that has to be copied to the new Comment1-field.

I don't know how to do that so any help would be greately appreciated.

Thank you in advance.

Best regards
Bjørn C. Winkel
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

<To Skip Lecture Go To Heading "Possible Solution," below>

<Lecture>

You'll probably get Normalization lectures about your table design. I
see you've responded to "Beetle" (Sean Bailey) w/ the reason you set up
your table the way you did. The issue is does Display of data over-ride
Storage of data? I would say No. The storage of the data is more
important than the display. You can manipulate the data in a Form to
fit the Display you desire.

The new Comments table would look like this:

Comments
customer_id Long Integer References Customers table
comment_date DateTime
comment Memo or Text(255)

I believe you could have set up a form that would have been just as good
as your table design, just a little different. I'd have set up a main
form for the Customer info and a sub-form for the Comments. The
Comments could have been in a Datasheet form so that the final form
would look like this:

Customer Info
================= Datasheet view (link on Customer ID) ==============
Comment1 ......................................................
Comment2 ......................................................
Comment3 ......................................................

Actually, you could have as many Comments as would fit in a Comments
table. And, you could use the date as an identifier field (in place of
the Comment1,2,3). Then you could sort the date field in the sub-form
to display the comments in any order.

</Lecture>

Possible Solution:

You don't say if you want to overwrite the Comment1 or just add to it.
You also don't say if you want the copied column to be emptied after it
is copied to Comment1. Emptying the copied column would require a 2nd &
3rd query.

Overwrite Comment1:

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment1 = Nz(Comment3,Comment2)
WHERE [Date]=[Update which date?]
AND Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null"

Add to Comment1:

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment1 = Comment1 & Nz(Comment3,Comment2)
WHERE [Date]=[Update which date?]
AND Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null"

The expression [ Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null" ]
uses the Nz() function to determine if Comment3 has data, if not use
Comment2. If Comment2 doesn't have data, then return the phrase "Is
Null" which is compared to [ <> "Is Null" ] which will indicate if there
were any comments in Comment2 or Comment3. If this expression evaluates
to True there was something in either Comment3/Comment2. If there is
nothing in those columns the expression evaluates to False and the
record is skipped.

To Empty the copied column (you have to remember to run both of these
after you run one of the above queries):

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment2 = Null
WHERE [Date]=[Update which date?]
AND Comment1 = Comment2

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment3 = Null
WHERE [Date]=[Update which date?]
AND Comment1 = Comment3

<More Lecture>
See why it's better to have a Normalized table. All this would not have
been necessary - its not recommended to update columns with data from
columns in the same table. You could have just had a main form/sub-form
or a View (query in Access) that showed the Comments sorted by the Date.
</More Lecture>

BTW, your English is way better than my Danish. :)

HTH
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSekYMIechKqOuFEgEQKeOACfUSXClb1XGMUOjD09X4K8tQwc8/MAoPEI
sbSnvAceVF0G3LMWid4cGDGm
=oyOO
-----END PGP SIGNATURE-----
Click to expand...

Hi

Thanks for you advice.

I agree that for normalization reasons it would be better your way. I did
think of that, but then I couldn't figure out how to present the data in a
datasheet like way. You know like this: date, customer, total......,
Comment1, Comment2, and so on.

I will try it on monday when I get back to work. I am quite sure where to
put your code though?

Thanks for you compliment about my english :)

Best regards
Bjørn C. Winkel
 
B

Bjørn C. Winkel

MGFoster said:
Bjørn C. Winkel said:
MGFoster said:
Bjørn C. Winkel wrote:
Hi group

First sorry for my english, it is not my mothertongue.

I have a dunning database in Access 2003 on Windows XP.
I need some kind of update query that copies f.ex. comment3 in old date
to comment1 in new date.
Example (sorry for the eventual taberrors):

Date Customer comment1 Comment2 Comment3
01.04.09 1 01.04 Dunn. 07.04 Dunn2 12.04
Dunn3
16.04.09 1 12.04 Dunn3
01.04.09 2 01.04 Called 08.04 Said that...
16.04.09 2 08.04 Said that...
and so on.

What I am trying to do is that when my colleague and I make filter on
the new date in the query we can, in Comment1, se what was last done.
It is not allways Comment3 that has to be copied to Comment1, sometimes
there are only text in Comment1 or Comment2, but it is allways the
highest nummbered field that has to be copied to the new
Comment1-field.

I don't know how to do that so any help would be greately appreciated.

Thank you in advance.

Best regards
Bjørn C. Winkel
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

<To Skip Lecture Go To Heading "Possible Solution," below>

<Lecture>

You'll probably get Normalization lectures about your table design. I
see you've responded to "Beetle" (Sean Bailey) w/ the reason you set up
your table the way you did. The issue is does Display of data over-ride
Storage of data? I would say No. The storage of the data is more
important than the display. You can manipulate the data in a Form to
fit the Display you desire.

The new Comments table would look like this:

Comments
customer_id Long Integer References Customers table
comment_date DateTime
comment Memo or Text(255)

I believe you could have set up a form that would have been just as good
as your table design, just a little different. I'd have set up a main
form for the Customer info and a sub-form for the Comments. The
Comments could have been in a Datasheet form so that the final form
would look like this:

Customer Info
================= Datasheet view (link on Customer ID) ==============
Comment1 ......................................................
Comment2 ......................................................
Comment3 ......................................................

Actually, you could have as many Comments as would fit in a Comments
table. And, you could use the date as an identifier field (in place of
the Comment1,2,3). Then you could sort the date field in the sub-form
to display the comments in any order.

</Lecture>

Possible Solution:

You don't say if you want to overwrite the Comment1 or just add to it.
You also don't say if you want the copied column to be emptied after it
is copied to Comment1. Emptying the copied column would require a 2nd &
3rd query.

Overwrite Comment1:

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment1 = Nz(Comment3,Comment2)
WHERE [Date]=[Update which date?]
AND Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null"

Add to Comment1:

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment1 = Comment1 & Nz(Comment3,Comment2)
WHERE [Date]=[Update which date?]
AND Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null"

The expression [ Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null" ]
uses the Nz() function to determine if Comment3 has data, if not use
Comment2. If Comment2 doesn't have data, then return the phrase "Is
Null" which is compared to [ <> "Is Null" ] which will indicate if there
were any comments in Comment2 or Comment3. If this expression evaluates
to True there was something in either Comment3/Comment2. If there is
nothing in those columns the expression evaluates to False and the
record is skipped.

To Empty the copied column (you have to remember to run both of these
after you run one of the above queries):

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment2 = Null
WHERE [Date]=[Update which date?]
AND Comment1 = Comment2

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment3 = Null
WHERE [Date]=[Update which date?]
AND Comment1 = Comment3

<More Lecture>
See why it's better to have a Normalized table. All this would not have
been necessary - its not recommended to update columns with data from
columns in the same table. You could have just had a main form/sub-form
or a View (query in Access) that showed the Comments sorted by the Date.
</More Lecture>

BTW, your English is way better than my Danish. :)

HTH
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSekYMIechKqOuFEgEQKeOACfUSXClb1XGMUOjD09X4K8tQwc8/MAoPEI
sbSnvAceVF0G3LMWid4cGDGm
=oyOO
-----END PGP SIGNATURE-----
Click to expand...

Hi

Thanks for you advice.

I agree that for normalization reasons it would be better your way. I did
think of that, but then I couldn't figure out how to present the data in
a datasheet like way. You know like this: date, customer, total......,
Comment1, Comment2, and so on.

I will try it on monday when I get back to work. I am quite sure where to
put your code though?

Thanks for you compliment about my english :)

Best regards
Bjørn C. Winkel
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The SQL code goes in a Query's SQL View: from the menu bar click View >
SQL View.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSerotYechKqOuFEgEQJWUQCgyc56SiBcBJ/s2w1EscrXfFqhBjkAoLZT
dU7TocdoG4wRfxa9pj4yOrF3
=H4dE
-----END PGP SIGNATURE-----
Click to expand...

Hi again

Thanks clearing that up for me.

Now I have looked at SQLcode below. If I am not mistaken i moves Comment3 or
Comment2 to the Comment1-field on one specific date? (Maybe I misunderstod
something, but I can only see one date in the code?)

What I wanted was that the comment in Comment3 or 2 field on records with
f.ex. date 01.04.09 was copied to Comment1-field on records with f.ex. date
16.04.09

In short: the last comment on previous date copied to Comment1-field in new
date.

Best regards
Bjørn C. Winkel
 
B

Bjørn C. Winkel

Thanks for help so far

--

Mvh

Hanne og Bjørn


MGFoster said:
Bjørn C. Winkel said:
MGFoster said:
Bjørn C. Winkel wrote:
"MGFoster" <[email protected]> skrev i en meddelelse
Bjørn C. Winkel wrote:
Hi group

First sorry for my english, it is not my mothertongue.

I have a dunning database in Access 2003 on Windows XP.
I need some kind of update query that copies f.ex. comment3 in old
date to comment1 in new date.
Example (sorry for the eventual taberrors):

Date Customer comment1 Comment2 Comment3
01.04.09 1 01.04 Dunn. 07.04 Dunn2 12.04
Dunn3
16.04.09 1 12.04 Dunn3
01.04.09 2 01.04 Called 08.04 Said that...
16.04.09 2 08.04 Said that...
and so on.

What I am trying to do is that when my colleague and I make filter on
the new date in the query we can, in Comment1, se what was last done.
It is not allways Comment3 that has to be copied to Comment1,
sometimes there are only text in Comment1 or Comment2, but it is
allways the highest nummbered field that has to be copied to the new
Comment1-field.

I don't know how to do that so any help would be greately
appreciated.

Thank you in advance.

Best regards
Bjørn C. Winkel
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

<To Skip Lecture Go To Heading "Possible Solution," below>

<Lecture>

You'll probably get Normalization lectures about your table design. I
see you've responded to "Beetle" (Sean Bailey) w/ the reason you set
up
your table the way you did. The issue is does Display of data
over-ride
Storage of data? I would say No. The storage of the data is more
important than the display. You can manipulate the data in a Form to
fit the Display you desire.

The new Comments table would look like this:

Comments
customer_id Long Integer References Customers table
comment_date DateTime
comment Memo or Text(255)

I believe you could have set up a form that would have been just as
good
as your table design, just a little different. I'd have set up a main
form for the Customer info and a sub-form for the Comments. The
Comments could have been in a Datasheet form so that the final form
would look like this:

Customer Info
================= Datasheet view (link on Customer ID) ==============
Comment1 ......................................................
Comment2 ......................................................
Comment3 ......................................................

Actually, you could have as many Comments as would fit in a Comments
table. And, you could use the date as an identifier field (in place
of
the Comment1,2,3). Then you could sort the date field in the sub-form
to display the comments in any order.

</Lecture>

Possible Solution:

You don't say if you want to overwrite the Comment1 or just add to it.
You also don't say if you want the copied column to be emptied after
it
is copied to Comment1. Emptying the copied column would require a 2nd
&
3rd query.

Overwrite Comment1:

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment1 = Nz(Comment3,Comment2)
WHERE [Date]=[Update which date?]
AND Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null"

Add to Comment1:

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment1 = Comment1 & Nz(Comment3,Comment2)
WHERE [Date]=[Update which date?]
AND Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null"

The expression [ Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null" ]
uses the Nz() function to determine if Comment3 has data, if not use
Comment2. If Comment2 doesn't have data, then return the phrase "Is
Null" which is compared to [ <> "Is Null" ] which will indicate if
there
were any comments in Comment2 or Comment3. If this expression
evaluates
to True there was something in either Comment3/Comment2. If there is
nothing in those columns the expression evaluates to False and the
record is skipped.

To Empty the copied column (you have to remember to run both of these
after you run one of the above queries):

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment2 = Null
WHERE [Date]=[Update which date?]
AND Comment1 = Comment2

PARAMETERS [Update which date?] Date;
UPDATE Comments
SET Comment3 = Null
WHERE [Date]=[Update which date?]
AND Comment1 = Comment3

<More Lecture>
See why it's better to have a Normalized table. All this would not
have
been necessary - its not recommended to update columns with data from
columns in the same table. You could have just had a main
form/sub-form
or a View (query in Access) that showed the Comments sorted by the
Date.
</More Lecture>

BTW, your English is way better than my Danish. :)

HTH
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSekYMIechKqOuFEgEQKeOACfUSXClb1XGMUOjD09X4K8tQwc8/MAoPEI
sbSnvAceVF0G3LMWid4cGDGm
=oyOO
-----END PGP SIGNATURE-----
Hi

Thanks for you advice.

I agree that for normalization reasons it would be better your way. I
did think of that, but then I couldn't figure out how to present the
data in a datasheet like way. You know like this: date, customer,
total......, Comment1, Comment2, and so on.

I will try it on monday when I get back to work. I am quite sure where
to put your code though?

Thanks for you compliment about my english :)

Best regards
Bjørn C. Winkel
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The SQL code goes in a Query's SQL View: from the menu bar click View >
SQL View.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSerotYechKqOuFEgEQJWUQCgyc56SiBcBJ/s2w1EscrXfFqhBjkAoLZT
dU7TocdoG4wRfxa9pj4yOrF3
=H4dE
-----END PGP SIGNATURE-----
Click to expand...

Hi again

Thanks clearing that up for me.

Now I have looked at SQLcode below. If I am not mistaken i moves Comment3
or Comment2 to the Comment1-field on one specific date? (Maybe I
misunderstod something, but I can only see one date in the code?)

What I wanted was that the comment in Comment3 or 2 field on records with
f.ex. date 01.04.09 was copied to Comment1-field on records with f.ex.
date 16.04.09

In short: the last comment on previous date copied to Comment1-field in
new date.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OK, that makes the query more complicated. Do you mean set the next
chronological record to the previous chronological Comment(2/3)? If so,
then:

********* BACK UP DATA BEFORE TRYING THESE ******************

PARAMETERS [Beginning Date?] Date, [Ending Date?] Date;
UPDATE Comments
SET Comment1 = Nz(Comment3,Comment2)
WHERE [Date]= (SELECT MIN(C2.[Date]) FROM Comments AS C2
WHERE C2.[Date] > Comments.[Date]
AND [Date] BETWEEN [Beginning Date?]
And [Ending Date?])
AND [Date] BETWEEN [Beginning Date?] And [Ending Date?]
AND Nz(Comment3, Nz(Comment2,"Is Null")) <> "Is Null"

The above query asks the user for the date range they want to update.
The correlated sub-query (that's the SELECT in the main query's WHERE
clause) finds the record with the next chronological date that is also
in the user-defined date range. That is the record(s) that will be
updated.

Then the "clearing out" queries would be (these are pretty complicated -
I'm not exactly sure they'll work. Be sure to **BACK UP** your data
before running these UPDATEs):

PARAMETERS [Beginning Date?] Date, [Ending Date?] Date;
UPDATE Comments
SET Comment2 = Null
WHERE [Date] BETWEEN [Beginning Date?] And [Ending Date?]
AND Comment2 = (SELECT C2.Comment2 FROM Comments As C2
WHERE C2.[Date] =
(SELECT MAX(C3.[Date]) FROM Comments AS C3
WHERE C3.[Date] < Comments.[Date]
AND C3.[Date] BETWEEN [Beginning Date?]
And [Ending Date?]))
AND Comment2 IS NOT NULL


PARAMETERS [Beginning Date?] Date, [Ending Date?] Date;
UPDATE Comments
SET Comment3 = Null
WHERE [Date]=[Update which date?]
WHERE [Date] BETWEEN [Beginning Date?] And [Ending Date?]
AND Comment3 = (SELECT C2.Comment3 FROM Comments As C2
WHERE C2.[Date] =
(SELECT MAX(C3.[Date]) FROM Comments AS C3
WHERE C3.[Date] < Comments.[Date]
AND C3.[Date] BETWEEN [Beginning Date?]
And [Ending Date?]))
AND Comment3 IS NOT NULL

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSezW3YechKqOuFEgEQJxXwCeOgohnBWohQWBb9Kk4SHVS5JxnQUAn2vq
aN9JQX7vB9YDxLfzJU2m5dyf
=xzfY
-----END PGP SIGNATURE-----
Click to expand...
 
Top