UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Append Query Using Tables Autonumber Id Field, Access 2016    
 
   
blueman2
post Jun 2 2020, 09:27 AM
Post#1



Posts: 261
Joined: 15-November 14



I have a new unpopulated table with a field to hold a foreign ID key from a parent key autonumber field in another existing table. The first thing I want to do is populate the new table with all the numbers in the parent key field (actually, I've criteria -ed out the records I don't want in a query but that shouldn't be an issue). I'm aware of the difficulties in populating a parent key autonumber field with an append query, but that's not what I'm trying to do. I assume there is a similar problem going the other way because I am getting violation errors every time I try to append and the violations list all the common errors in an append query. So is there a workaround to get this done?

Thanks
Go to the top of the page
 
Doug Steele
post Jun 2 2020, 09:32 AM
Post#2


UtterAccess VIP
Posts: 22,306
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What the SQL of the query you're trying to run? How are you attempting to run the query? What error(s) are you getting?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
RJD
post Jun 2 2020, 09:38 AM
Post#3


UtterAccess VIP
Posts: 10,669
Joined: 25-October 10
From: Gulf South USA


Hi: Hmmm ... did you make the Parent key field (foreign key) in the new table an Autonumber field? If so, change that to a number field (say, Long). You cannot insert a value into an Autonumber field. But then, we have very limited information about what you are doing. As Doug asked, please post the SQL of the append query that is not working. Also, please post the design of the new table. More information needed.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Jun 2 2020, 10:01 AM
Post#4


UtterAccess VIP
Posts: 12,483
Joined: 10-February 04
From: South Charleston, WV


Why do you need to perform this data copy?

--------------------
Robert Crouser
Go to the top of the page
 
blueman2
post Jun 2 2020, 10:01 AM
Post#5



Posts: 261
Joined: 15-November 14



Thanks

Here's the SQL


CODE
INSERT INTO tblWebProductInfo ( ProdID )
SELECT tblProducts.ProdID
FROM tblProducts
WHERE (((tblProducts.ProdID)<>65 And (tblProducts.ProdID)<>67 And (tblProducts.ProdID)<>68) AND ((tblProducts.Discontinued)=False))
ORDER BY tblProducts.ProdCode;


QUOTE
How are you attempting to run the query?

? It's an append query. I am hitting the run button after viewing the correct results in data sheet view.

QUOTE
What error(s) are you getting?

Attached File  Image_9.jpg ( 30.36K )Number of downloads: 4

Go to the top of the page
 
blueman2
post Jun 2 2020, 10:05 AM
Post#6



Posts: 261
Joined: 15-November 14



QUOTE
Hi: Hmmm ... did you make the Parent key field (foreign key) in the new table an Autonumber field?


No, that wouldn't work. It's just a number field as are all foreign key fields in all my tables
This post has been edited by blueman2: Jun 2 2020, 10:15 AM
Go to the top of the page
 
RJD
post Jun 2 2020, 10:33 AM
Post#7


UtterAccess VIP
Posts: 10,669
Joined: 25-October 10
From: Gulf South USA


Hi: I tested your SQL in a demo db, and it seems to work fine - provided both table fields (source and target) are numeric. See the attached. See if you can spot a difference between my demo and your actual tables.

I also included another approach to the SQL, using Not In instead of the three And <> components. Works the same.

HTH
Joe
Attached File(s)
Attached File  AppendQuery.zip ( 18.87K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
blueman2
post Jun 2 2020, 10:40 AM
Post#8



Posts: 261
Joined: 15-November 14



QUOTE
Hi: I tested your SQL in a demo db, and it seems to work fine - provided both table fields (source and target) are numeric. See the attached. See if you can spot a difference between my demo and your actual tables.



Thanks for the 'Not In' tip. I'll use that but yes there is a difference. As I mentioned in my opening:
QUOTE
from a parent key autonumber field in another existing table.

I am taking the data from a parent key autonumber field, that's what 'ProdID' is. I believe in your example, you have it as a number field.
This post has been edited by blueman2: Jun 2 2020, 10:42 AM
Go to the top of the page
 
Doug Steele
post Jun 2 2020, 10:49 AM
Post#9


UtterAccess VIP
Posts: 22,306
Joined: 8-January 07
From: St. Catharines, ON (Canada)


The error message explicitly states that 56 records weren't added due to "validation rule violations". That implies to me that one or more of the fields in tblWebProductInfo is declared as mandatory, and you're not providing values. (Another possibility is that you've got foreign key fields in tblWebProductInfo with non-optional relationships to other tables)

I'm really confused about what you're trying to do. Why would you try to insert just ProdID into the table? Why not insert all of the fields of interest?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
RJD
post Jun 2 2020, 11:03 AM
Post#10


UtterAccess VIP
Posts: 10,669
Joined: 25-October 10
From: Gulf South USA


QUOTE
I am taking the data from a parent key autonumber field, that's what 'ProdID' is. I believe in your example, you have it as a number field.

Yes, but an Autonumber field is a Number field, just that the value is created automatically. But I think Doug hit the problem - look at other fields and see if they are required or linked in the relationships window.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
blueman2
post Jun 2 2020, 11:06 AM
Post#11



Posts: 261
Joined: 15-November 14



QUOTE
I'm really confused about what you're trying to do. Why would you try to insert just ProdID into the table? Why not insert all of the fields of interest?


There are no other fields of interest needed for this new table. Everything else will be entered manually based on the value in this field.


QUOTE
The error message explicitly states that 56 records weren't added due to "validation rule violations". That implies to me that one or more of the fields in tblWebProductInfo is declared as mandatory, and you're not providing values. (Another possibility is that you've got foreign key fields in tblWebProductInfo with non-optional relationships to other tables)


Well one thing I probably should have mentioned but didn't was that the backend is a sharepoint table. Here you can see there is nothing required and nothing indexed for this field. I've been using the back end for years now with no problems, so unless there's something specific to this, I don't know why there would be now.


Attached File  Image_11.jpg ( 119.67K )Number of downloads: 3


However, now I'm seeing something that I recall may have presented a problem a while back for something else and I think it has to do with the field size? Does that make sense?







Go to the top of the page
 
Doug Steele
post Jun 2 2020, 11:31 AM
Post#12


UtterAccess VIP
Posts: 22,306
Joined: 8-January 07
From: St. Catharines, ON (Canada)


QUOTE
That implies to me that one or more of the fields in tblWebProductInfo is declared as mandatory, and you're not providing values. (Another possibility is that you've got foreign key fields in tblWebProductInfo with non-optional relationships to other tables)

Since you are supplying a value for ProdID, where or not it's mandatory is irrelevant. Are any of the other fields in the table required?

QUOTE
There are no other fields of interest needed for this new table. Everything else will be entered manually based on the value in this field.

It's seldom a good idea to partially populate data. Why not build a form that allows them to populate tblWebProductInfo based on which rows in in tblProducts haven't been dealt with yet, rather than populating tblWebProductInfo and doing further manipulations on it?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
blueman2
post Jun 2 2020, 12:34 PM
Post#13



Posts: 261
Joined: 15-November 14



QUOTE
It's seldom a good idea to partially populate data. Why not build a form that allows them to populate tblWebProductInfo based on which rows in in tblProducts haven't been dealt with yet, rather than populating tblWebProductInfo and doing further manipulations on it?


Because not everyone gathering data for these records will be working through the DB. I want them to have Prod ID as a reference so that they can see that the rest of the data inputted for a row will relate to that particular Prod ID. I did not think it would be difficult to simply populate this field with an append query.

I'm going to check on your other point in a little while. There is of course an ID field for that table which would be WebProductsInfoID which is set to Autonumber but that is the Parent Key Field for that table.

Do you not think that the 'double' field size could be part of the problem?

I'm also now thinking that I could use ProductCode, which is a text value, instead of ProdID, but I usually like to stick to the ID values and then just use a combo box in the forms to bring up the text code?


I looked and there no other required fields. Most of the others are long text fields and there aren't that many. Of course sharepoint decides to add many other fields to the table which I have no use for in access but that is the case with all the tables.
This post has been edited by blueman2: Jun 2 2020, 12:43 PM
Go to the top of the page
 
blueman2
post Jun 2 2020, 01:46 PM
Post#14



Posts: 261
Joined: 15-November 14



We'll I went back to sharepoint to look directly and low and behold there was a field that required data. As I mentioned, sharpoint always adds these extraneous fields. It's done that with all the tables I've created but this is the first time I've come across this setting in one of those fields. I usually just ignore them but won't any more.

Attached File  Image_15.jpg ( 90.34K )Number of downloads: 0


Once I changed that, I was able to run the append query without a problem the way I wanted to. Thanks for your help
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    3rd August 2020 - 03:40 AM