SQL Statement

  • Thread starter Thread starter ispfemme
  • Start date Start date
I

ispfemme

I posted earlier and fear I may have worded things wrong.

I also tested each individual query to ensure that it was down to these two.
I have a strong feeling that the problem is in the second query I had posted:


Update

UPDATE [Full Details] LEFT JOIN [Full Details w Comments] ON ([Full Details].
[Line #] = [Full Details w Comments].[Line #]) AND ([Full Details].[P/O
Number] = [Full Details w Comments].[P/O Number]) SET [Full Details w
Comments].[Remaining-Qty] = [Full Details].[Remaining-Qty], [Full Details w
Comments].[Ven Date] = [Full Details].[Ven Date], [Full Details w Comments].
[Due Date] = [Full Details].[Due Date];


How to I simply force the table with comments to allow new information to
append but have old information that is no longer in the [full details] table
to be nulled. The comments table just needs to be updated but I'm still
having trouble with this.

Thank you so much for getting through this
 
If you are trying to both update and append, I usually rely on two queries
to do this.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I did use two separate queries, but the appending one is the only one that
seems to be working. How can I get the old information off of the new table?

Jeff said:
If you are trying to both update and append, I usually rely on two queries
to do this.

Regards

Jeff Boyce
Microsoft Office/Access MVP
I posted earlier and fear I may have worded things wrong.
Click to expand...
[quoted text clipped - 22 lines]
Thank you so much for getting through this
Click to expand...
Click to expand...
 
I'm not sure what you mean. Where did "new table" come from?

If I do an update query, joining on the matching (?key) fields, and use the
"Update To" "cell" to indicate the new value, Access does this (NOTE: this
is via the design view window).

Regards

Jeff Boyce
Microsoft Office/Access MVP

ispfemme via AccessMonster.com said:
I did use two separate queries, but the appending one is the only one that
seems to be working. How can I get the old information off of the new
table?

Jeff said:
If you are trying to both update and append, I usually rely on two queries
to do this.

Regards

Jeff Boyce
Microsoft Office/Access MVP
I posted earlier and fear I may have worded things wrong.
Click to expand...
[quoted text clipped - 22 lines]
Thank you so much for getting through this
Click to expand...
Click to expand...
Click to expand...
 
Alright, so there is one table [Full Details] that gets automatically update
with my macro. Then it is used in a new make-table query in order to create
the [Full Details w Comments] table. This is the same as the [Full Details]
table except that it does include an additional field that allows for
comments to be made for that particular row.

My next query is supposed to clear out the information from the [Full Details
w Comments] table that does not match what is included in the [Full Details]
table. Unfortunately, it is still not doing this so that is where I'm stuck.
I have matched up all the fields and made the second query as an update to
query... still appending, not clearing off the old data.

Jeff said:
I'm not sure what you mean. Where did "new table" come from?

If I do an update query, joining on the matching (?key) fields, and use the
"Update To" "cell" to indicate the new value, Access does this (NOTE: this
is via the design view window).

Regards

Jeff Boyce
Microsoft Office/Access MVP
I did use two separate queries, but the appending one is the only one that
seems to be working. How can I get the old information off of the new
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
Why?

If you have one table without comments, and another table with comments,
everything else being equal, why have two tables?

I'm not asking out of curiosity, but because there may be another way to
satisfy the business need you've selected this approach to solve.

Regards

Jeff Boyce
Microsoft Office/Access MVP

ispfemme via AccessMonster.com said:
Alright, so there is one table [Full Details] that gets automatically
update
with my macro. Then it is used in a new make-table query in order to
create
the [Full Details w Comments] table. This is the same as the [Full
Details]
table except that it does include an additional field that allows for
comments to be made for that particular row.

My next query is supposed to clear out the information from the [Full
Details
w Comments] table that does not match what is included in the [Full
Details]
table. Unfortunately, it is still not doing this so that is where I'm
stuck.
I have matched up all the fields and made the second query as an update to
query... still appending, not clearing off the old data.

Jeff said:
I'm not sure what you mean. Where did "new table" come from?

If I do an update query, joining on the matching (?key) fields, and use
the
"Update To" "cell" to indicate the new value, Access does this (NOTE: this
is via the design view window).

Regards

Jeff Boyce
Microsoft Office/Access MVP
I did use two separate queries, but the appending one is the only one
that
seems to be working. How can I get the old information off of the new
Click to expand...
[quoted text clipped - 13 lines]
Thank you so much for getting through this
Click to expand...
Click to expand...
Click to expand...
 
Back
Top