Query Problem Append or Update (1 Viewer)

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
The person who normally does the programming is sick and I am having two problems.

The table = Portfolio_Website_T = is placed on our website every Monday morning with New and refreshed data.
Do I run an Append Query or run a Update Query to refresh the data?
===

The Queries which I have created are not working in our Access 2003 database.
Could I please be advised as to what I have done wrong?

Thank you.
Crystal
 

Attachments

  • Append_Update_21=091.mdb
    384 KB · Views: 22

theDBguy

I’m here to help
Staff member
Local time
Today, 00:09
Joined
Oct 29, 2018
Messages
14,860
Hi Crystal,

None of your queries is working because your second table is empty. If the master db is update, and the web db is replaced with new data, then I would suggest simply using an APPEND query to populate a blank database with the data to replace the file on the web server.

To make the APPEND query work, remove the empty table from your query and only add the source table in it.
 

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
Thank you for your suggestion, but I can not find where the data is, could you please help me?
Crystal
 

Attachments

  • Append_Update_21=091=Updated.mdb
    412 KB · Views: 20

theDBguy

I’m here to help
Staff member
Local time
Today, 00:09
Joined
Oct 29, 2018
Messages
14,860
Thank you for your suggestion, but I can not find where the data is, could you please help me?
Crystal
Hi. Tell you what. How about you post three Excel files instead? Make sure the file names are the names of the tables in your db.
  1. Source Data from main db
  2. Expected Data for first web table
  3. Expected Data for second web table
 

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
Hi. Tell you what. How about you post three Excel files instead? Make sure the file names are the names of the tables in your db.
  1. Source Data from main db
  2. Expected Data for first web table
  3. Expected Data for second web table
Thank you for your note.
We did try using Excel files as you have suggested, but only by using a Database have we had success creating this type of Visual Web Page..
Could you please advise me what I am doing wrong with our Append and Update Queries?
Crystal
 

Attachments

  • Screen_Shot.pdf
    55.4 KB · Views: 24

theDBguy

I’m here to help
Staff member
Local time
Today, 00:09
Joined
Oct 29, 2018
Messages
14,860
Thank you for your note.
We did try using Excel files as you have suggested, but only by using a Database have we had success creating this type of Visual Web Page..
Could you please advise me what I am doing wrong with our Append and Update Queries?
Crystal
Hi. I asked for three files because I only see one table in the last file you posted, so I couldn't tell what you want to get out of it.

I can't tell you what you're doing wrong, because I have no idea the final outcome you're trying to achieve. That's why I asked for a sample source file and a sample of the outcome you want.

And lastly, I asked for Excel files because I wanted to remove any confusions about using Access at the moment. I was going to give you an Access file once I understood better what you were really trying to do.

As far as what you did wrong, I already told you that your update and append queries had an extra table in them. However, I didn't mean for you to delete the other table in your db - just in the query.
 

arnelgp

error reading drive A:
Local time
Today, 15:09
Joined
May 7, 2009
Messages
12,315
remember Investmentl_ID in table Investments01_tbl is Autonumber.
and you are Joining on this field.

you should make InvestmentI_ID in Investments01 as Number (long, not Autonumber).

this will Add/Update record to Investments01_tbl:
Code:
UPDATE Investments01_tbl
    RIGHT JOIN Portfolio_Website_T ON Investments01_tbl.Investmentl_ID = Portfolio_Website_T.Investmentl_ID
    SET Investments01_tbl.Investmentl_ID = Portfolio_Website_T.Investmentl_ID,
    Investments01_tbl.Symbol_Stock_Y = "Portfolio_Website_T",
    Investments01_tbl.Stock_Name = "Portfolio_Website_T",
    Investments01_tbl.Net_Share_Cost = "Portfolio_Website_T",
    Investments01_tbl.Sector = "Portfolio_Website_T",
    Investments01_tbl.[Sub-Industry] = "Portfolio_Website_T",
    Investments01_tbl.AnalystPrice_Target = "Portfolio_Website_T";
 
Last edited:

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
Thank you, Arnelgp for your code.
We have tried running your suggestion and keep receiving this error message.

== Carnot update "Investments01_Tbl.Investmentl_ID" field not updateable

Could you, please advise how to correct this message.
Happy Easter
Nicole
 

Attachments

  • Append_Update_21=092.mdb
    408 KB · Views: 17

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
Thank you theDBguy for your question.
We previously used Excel Spread sheets on our Web Site, but now we are working on something new, which takes its data from an MsAccess Table.

The table = Investments01_tbl = contains confidential and restricted information, which should not be accessed on the web site.

The table = Portfolio_Website_T = contains the information feeding our Web Pages, that are available to our general staff.

What we have tried doing is to Append = Portfolio_Website_T = table from the = Investments01_tbl = table and then the = Portfolio_Website_T = table is uploaded to the Web Site.

Happy Easter
Nicole
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:09
Joined
Oct 29, 2018
Messages
14,860
Thank you theDBguy for your question.
We previously used Excel Spread sheets on our Web Site, but now we are working on something new, which takes its data from an MsAccess Table.

The table = Investments01_tbl = contains confidential and restricted information, which should not be accessed on the web site.

The table = Portfolio_Website_T = contains the information feeding our Web Pages, that are available to our general staff.

What we have tried doing is to Append = Portfolio_Website_T = table from the = Investments01_tbl = table and then the = Portfolio_Website_T = table is uploaded to the Web Site.

Happy Easter
Nicole
Hi Nicole. I'm sorry, but you're still not giving me enough information to fully understand the goal you're trying to accomplish. Please remember, we are not familiar with your business or your database, so we'll need specific instructions to be able to help you.

Okay, so, let's try to use the file you already provided, since you can't/won't give me what I asked. There is a table in your file called Investments01_tbl that has a lot of fields in it. You also have a table called Portfolio_Website_T with just a handful of fields in it. Assuming Investmens01 is the source of the main data, and Portfolio_Website is the table you want to populate and post on the website, and also assuming there is a one-to-one correspondence between the field names in both tables, then you could try the following APPEND query.

SQL:
INSERT INTO Portfolio_Website_T ( 
  Sergey, Investmentl_ID, Symbol_Stock_Y, Stock_Name, 
  Exchange, [Currency], Current_Price, AnalystPrice_Target, 
  Sector, [Sub-Industry], AverageShareCost, [TotalCost_$], 
  Net_Share_Cost, Net_Share_Quantity, PortfolioCode, Target_Date, Target )
SELECT Investments01_tbl.Sergey, Investments01_tbl.Investmentl_ID, 
  Investments01_tbl.Symbol_Stock_Y, Investments01_tbl.Stock_Name, 
  Investments01_tbl.Exchange, Investments01_tbl.Currency, 
  Investments01_tbl.Current_Price, Investments01_tbl.AnalystPrice_Target, 
  Investments01_tbl.Sector, Investments01_tbl.[Sub-Industry], 
  Investments01_tbl.AverageShareCost, Investments01_tbl.[TotalCost_$], 
  Investments01_tbl.Net_Share_Cost, Investments01_tbl.Net_Share_Quantity, 
  Investments01_tbl.PortfolioCode, Investments01_tbl.Target_Date, Investments01_tbl.Target
FROM Investments01_tbl;
Hope that helps...
 

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
T.A.N.K. Y.O.U. theDBguy.

I am sorry that I misunderstood your question and your Append Query is P.E.R.F.E.C.T.

Nicole

 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:09
Joined
Oct 29, 2018
Messages
14,860
T.A.N.K. Y.O.U. theDBguy.

I am sorry that I misunderstood your question and your Append Query is P.E.R.F.E.C.T.

Nicole

Hi. Glad to hear we finally figured it out. Good luck with your project.
 

arnelgp

error reading drive A:
Local time
Today, 15:09
Joined
May 7, 2009
Messages
12,315
your Original query is that you are Appending to Investments01_tb?
 

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
Thank you theDBguy for your help in creating our Append Query which is P.E.R.F.E.C.T.

I have modified your Query for our different Criteria’s.
We now have two new tables and two new append Queries
Portfolio_Website_T_Purchased // Append_2_Portfolio_Website_TQ_Purchased
Portfolio_Website_T_Watch // Append_2_Portfolio_Website_TQ_Watch

The data in the source table is CHANGING weekly and I am manually deleting the Appended table’s contents.

Can you please suggest a way of deleting the contents of Portfolio_Website_T_Purchased and Portfolio_Website_T_Watch before we run the Append Queries?

Thank you.
Crystal
 

Attachments

  • Works=Append=Update_21=093.mdb
    432 KB · Views: 19

theDBguy

I’m here to help
Staff member
Local time
Today, 00:09
Joined
Oct 29, 2018
Messages
14,860
Thank you theDBguy for your help in creating our Append Query which is P.E.R.F.E.C.T.

I have modified your Query for our different Criteria’s.
We now have two new tables and two new append Queries
Portfolio_Website_T_Purchased // Append_2_Portfolio_Website_TQ_Purchased
Portfolio_Website_T_Watch // Append_2_Portfolio_Website_TQ_Watch

The data in the source table is CHANGING weekly and I am manually deleting the Appended table’s contents.

Can you please suggest a way of deleting the contents of Portfolio_Website_T_Purchased and Portfolio_Website_T_Watch before we run the Append Queries?

Thank you.
Crystal
Good morning. See if this helps...
 

Attachments

  • Works=Append=Update_21=093.zip
    30.8 KB · Views: 22

arnelgp

error reading drive A:
Local time
Today, 15:09
Joined
May 7, 2009
Messages
12,315
firstly the 2 queries can be made Append & Update.
Append record if it does not exists yet.
Update if it exists.

so there is no need to Delete the record everytime there is an update.
see the 2 queries with "arnelgp" as prefix.

Note also that you cannot Update an Autonumber field.
therefore, i make the two "Investmentl_ID" fields of Portfolio_Website_T_* tables Long Integer.
(also, everytime you delete records, the value of this changes).
 

Attachments

  • Works=Append=Update_21=093.mdb
    420 KB · Views: 17

access2010

Registered User.
Local time
Today, 00:09
Joined
Dec 26, 2009
Messages
706
Thank you both for your assistance.
We have had SUCCESS with the suggestions that you both have made.

T.H.A.N.K. Y.O.U.
Nicole
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:09
Joined
Oct 29, 2018
Messages
14,860
Thank you both for your assistance.
We have had SUCCESS with the suggestions that you both have made.

T.H.A.N.K. Y.O.U.
Nicole
Hi Nicole. @arnelgp and I were happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom