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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Append A Record To The Same Table?, Access 2016    
 
   
Ron38
post Nov 28 2017, 10:58 AM
Post#1



Posts: 222
Joined: 19-August 10
From: New Hampshire


I am trying to copy a record back into the same table, so user can make minor changes to a record for a new instance, without having to enter everything.
An append query to the same table give me an Out of Stack space error. Primary key is an autonumber.
I can picture using VBA to read the record, storing it in an array, then making an Insert query to add it again, but there are a lot of fields!!!
Any other ideas?
Ron
Go to the top of the page
 
theDBguy
post Nov 28 2017, 11:01 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Ron,

Can you show us your code please? Thanks.

Appending to the same table should not be a problem...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Nov 28 2017, 11:15 AM
Post#3


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


"... there are a lot of fields"

That's always a red flag for a non-normalized table design. Properly normalized tables are "Narrow and Tall", spreadsheet tables are "Wide".

So, that's a caution to think through carefully.

That said, what you need is something like this.

SQL
INSERT INTO tblYourtableNameGoesHere (Field1, Field2)
SELECT tblYourtableNameGoesHere.Field1, tblYourtableNameGoesHere.Field2
FROM tblYourtableNameGoesHere WHERE tblYourtableNameGoesHere.PrimaryKeyField = lngPKFortheRecordYouWantToDuplicate;


Change my sample names to your actual name. Add all of the fields you want to duplicate. Make sure you filter on the one record you want to copy.

--------------------
Go to the top of the page
 
projecttoday
post Nov 28 2017, 10:13 PM
Post#4


UtterAccess VIP
Posts: 8,668
Joined: 10-February 04
From: South Charleston, WV


Usually this type of thing is done on the form where the user would ordinarily enter a new record. He/she clicks on a button and the fields are copied in for him.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Ron38
post Nov 29 2017, 08:36 AM
Post#5



Posts: 222
Joined: 19-August 10
From: New Hampshire


I was trying to avoid the INSERT query because there are so many fields. Lazy. I used to teach RDB: this table has a lot of fields because it needs to..
Guess I have to just do the work! And yes, this will be a button on the form.
Thanks to all!
Go to the top of the page
 
projecttoday
post Nov 29 2017, 09:00 AM
Post#6


UtterAccess VIP
Posts: 8,668
Joined: 10-February 04
From: South Charleston, WV


thumbup.gif

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 09:02 AM
Post#7


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


"this table has a lot of fields because it needs to..."

Okay, I'll bite, why does it need to have lots of fields?

--------------------
Go to the top of the page
 
Ron38
post Nov 29 2017, 09:15 AM
Post#8



Posts: 222
Joined: 19-August 10
From: New Hampshire


I tried a small sample doing just this, and I get Out of Stack Space. I think Access does not like a query appending to itself...
That's why I am thinking of defining an array for the record I want to copy, then doing the INSERT using array fields...
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 09:18 AM
Post#9


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


If you are trying to append to a QUERY, it's likely you're going to run into problems, especially if there are multiple tables involved in that query.

Please note that all of us talked about appending to a TABLE.

--------------------
Go to the top of the page
 
Ron38
post Nov 29 2017, 09:19 AM
Post#10



Posts: 222
Joined: 19-August 10
From: New Hampshire


Actual count is 24 fields. 3 Organization fields, 3 name fields, 5 address fields, 8 phone fields (including extensions), email, web site.
This is a Resource table listing all kinds of organizations that are available to assist veterans.
Go to the top of the page
 
Ron38
post Nov 29 2017, 09:22 AM
Post#11



Posts: 222
Joined: 19-August 10
From: New Hampshire


I am not appending TO a query. I am using an Append query (INSERT) to append one record from the table back into the table: should be OK because primary key is an autonumber and I do not include that field in the INSERT.
Go to the top of the page
 
projecttoday
post Nov 29 2017, 09:52 AM
Post#12


UtterAccess VIP
Posts: 8,668
Joined: 10-February 04
From: South Charleston, WV


When you said "the work" I thought you meant you were working on your form. I thought this was finished.

You did say you were going to use a button. Now you're working on an INSERT?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 10:09 AM
Post#13


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Okay, you have what are called "repeating groups", as one might have guessed.

The question is whether you end up with Nulls in any of those fields because some organizations have one address field while others have five? What do you do when you need to look at all addresses for each organization in a report?

And what happens to your table (forms, reports, queries) when an organization comes along with six or seven addresses, or four or five different "names"?

All in all, it bears a bit of further analysis to decide if this is really the most optimal plan.

In addition, you are truly going to have to account for ALL 24 fields (and possibly others as thing change over time), regardless of whether any given source record actually contains all 24 values, or only 22, or 16, or whatever. That's just one of the penalties imposed by a non-normalized design...

--------------------
Go to the top of the page
 
Ron38
post Nov 29 2017, 10:27 AM
Post#14



Posts: 222
Joined: 19-August 10
From: New Hampshire


I will have a command button labelled "Copy" on the form. In the OnClick event, I will read the current row, insert it into an array, then create an INSERT query in VBA and run he query. That's the plan.

Quotes are a PITA...
Go to the top of the page
 
River59
post Nov 29 2017, 10:46 AM
Post#15



Posts: 1,342
Joined: 7-April 10
From: Detroit, MI


I don't understand why the amount of fields lessons or increases the work for this query? Simply use the design grid to make an append query, then set the ID criteria to match what is selected on the form to copy. If you want, you can run this as a query (DoCmd.openquery 'The append query'), or you can go to the SQL view and paste that into your module. I have to assume that your table structure is what you need so this is a one-to-one match on the fields. If not, as Grover said, you need to rethink your structure.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
River59
post Nov 29 2017, 10:46 AM
Post#16



Posts: 1,342
Joined: 7-April 10
From: Detroit, MI


I don't understand why the amount of fields lessons or increases the work for this query? Simply use the design grid to make an append query, then set the ID criteria to match what is selected on the form to copy. If you want, you can run this as a query (DoCmd.openquery 'The append query'), or you can go to the SQL view and paste that into your module. I have to assume that your table structure is what you need so this is a one-to-one match on the fields. If not, as Grover said, you need to rethink your structure.

Sorry about the double post, my pc is 'doggin' today so I hit the Continue button twice ...

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 11:01 AM
Post#17


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Okay, let's explore this a bit deeper.

"...your table structure is what you need ...."

The problem here is that what qualifies as a properly normalized relational database is pretty well understood. It does NOT involve repeating groups such as 3 "names" and 3 "organizations". "Need" is not something I'd apply to this question directly. It DOES apply in a different way, of course, as in one NEEDS contact information, including one or more phone numbers, one or more email addresses, and so on. How those are structured, on the other hand, depends on understanding relational table design principles.

That said, of course, I am aware of no requirement which specifies any given table cannot be built following a non-normalized design, i.e. a "spreadsheet" style table like this one, with multiple sets of repeating groups. It's up to the developer.

There are trade-offs, of course; some things are just plain harder to do with a non-normalized design. Some things--primarily simple interface design--tend to be more easily done with a non-normalized design. However, regardless of which way one goes, the decision should be a conscious, informed choice. In other words, if someone decides to include multiple email addresses, or multiple phone numbers, in a table in order to make a data entry form simpler, that's their choice. But doing so without understanding how that impacts other aspects of the database is not so sanguine. The consequences of every choice follow those choices and must be acceptable.

So, the whole issue of appending 23 fields (obviously one doesn't include the primary key) in an INSERT query versus 4 or 7 or 18 fields is not pertinent, IMO, to this aspect of it. You are right, of course, that the absolute number of fields should NOT be more or less work except for the original creation of that query.

We've already invested more time talking about it than it would have taken to just create that query in the first place.

HTH,

George

--------------------
Go to the top of the page
 
projecttoday
post Nov 29 2017, 12:09 PM
Post#18


UtterAccess VIP
Posts: 8,668
Joined: 10-February 04
From: South Charleston, WV


How do you enter the records into the table which are the source for your copy?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Nov 29 2017, 12:15 PM
Post#19


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


My apologies. Let me clarify what I was thinking.

The fields specified are all needed if that's what it takes to adequately account for all instances of a phone number for each organization, for example. However, I was trying to make the point that having one field for each phone number in this table is not the same thing. In other words, in a normalized database, you'd have a table of organizations, a table of phone numbers and a junction table of phone numbers assigned to organizations (or, perhaps to individuals within an organization--this can get murky). So, organization A could have three RECORDS in the PhoneNumberOrganization junction table, one for each of them. Organization B might have only one record, and Organization C, with a huge staff, might have ten or more records. All are needed, but the design choice of exactly three phone number FIELDS per organization makes it harder to handle these different scenarios.

Again, I am sorry.

--------------------
Go to the top of the page
 
Ron38
post Nov 29 2017, 12:24 PM
Post#20



Posts: 222
Joined: 19-August 10
From: New Hampshire


A standard Access form...
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 12:11 PM