How to add records from one table to another

[rickscript]

I am trying to get my old databases from Symantecs Q&A (yes, it's a dinosaur, but a very simple to use dinosaur that hasn't caused near the consternation of Access XP) into Access 2002. I've managed to get the Q&A to .csv text in notepad and imported it into Access tables. Problem is that I could only get the data from my old DOS machine in floppy-disk size pieces so I have 3 separate tables, all with the same fields and different records. I would like to combine three tables into one table to consolidate the data. Do I use the append query to do this? I've made a few attempts at it but haven't made it over the hump yet. I've read most of the threads in this forum and my issue seems like a walk in the park for the people here. This should not be difficult to do but it is turning into a "saga' forme. Any help would certainly be appreciated.

 

[BNPMike]

So you have three input tables all the same 'shape'? Create a select query from the first and turn it into a Make Table Query, and run it. That gets your first set in. Adjust the table design according to your needs. For the second input table, create a select query and turn it into an Append and select the table you made above. Similarly for the third one.

Then start again by designing your target tables properly and append the data from your first stab.

 

[rickscript]

BNPMike
Thanks for the reply...I know the forum is not a place to be looking for a course in how to use a program so I won't ask for that kind of help. I understand most of the rest of the office suite but Access is another story. This seems to be about the most Un-intuitive program I have ever used. I have a CD tutorial on Access (lynda.com) but I am afraid it covers some pretty basic things and does not do a very good job with queries (which seems to be the hardest part of this program for me to learn). Your description sounds a lot like the description in Access Help for the append query. I tried a lot of different combinations but it's just not happening- usually I get this error message saying

Microsoft Access can't append all the records in the append query...
set 0 field(s) to Null due to a type conversion failure, and it didn't add 4098 record(s) to the table due to key violations, 0 record)s) due to lock violations, and 0 records due to validation rule violations. Do you want to run the query action anyway? For an explanation of the causes of the violations click help.

clicking help brings up the entire help manual and not any specific area with specific explanations. Not exactly what I would call helpful. I really wanted to learn this program but I am beginning to think it's just not worth it. Is there a better way to understand what is going wrong here?

 

[PHV]

it didn't add 4098 record(s) to the table due to key violations
Check the indexed fields in the destination table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

As you say, people don't have intuition that covers relational databases. You need to read a manual. Microsoft gave you a terrific one when I was a boy. They don't now so you have to buy one.

Access is more-or-less as sophisticated as the biggest and most expensive database systems in the world. This has its benefits later but it does make it more complicated when you first start using it.

You're obviously making progress. Somewhere you must have added a key or index to your table. Did you use a wizard?

 

[HenryAnthony]

Rickscript,

Look at the design view of the table you are appending to. Click your cursor in each field. Look below at Field Properties>General>Indexed. If any fields say "Yes (No Duplicates)" under Indexed, and you try to append a duplicate value, the record will not be appended due to key violations as revealed to you in the message box. Make another selection under Indexed and try to append.

Don't give up the ship just yet. If I can get Access to work for me, I think most anyone can. Takes a bit of study though. And, yes, Access help is not much. It was better in Access 98.

Best regards,

Henry

 

[rickscript]

To PHV- your tip about the indexed files was a good one and started me in the right direction-Thanks

To BNPMike, Thanks for your help- yes I did use a wizard and it added the keyframe (oops, that's Flash, another complex program I'm trying to learn now) being an auto-generated and unique number in the first column and which has turned out to be the source of much of my travails (and I don't mean "happy travails"). Also, thanks for having a bit of understanding for a newcomer's (to Access, not to computers) frustrations with a program which is likely overkill for most home/personal users.

To HenryAnthony (with the name formatted in the standard Excel naming convention)- You also get an assist on the play. You sound like someone who has gone through the bizarre learning curve that I must now go through; with a few helping hands along the way, I will be one of the turtles that DOES make it to the sea.

I used your suggestions, tried many combinations of steps, erred, used my backup copies over and over and finally got the tables data combined, cleaned up and usable. I even feel like I kinda understand it somehat. Along the way, I had the appended files sometimes get added twice, a few times not at all and once, in a very VERY bizarre occurrence, my database, a matter of single digit megabytes was taking a LONG time to "render". When it finally finished and I checked the size of the combined file it was over 2 GIGA-BYTES! Y-i-k-e-s! I don't know what I did to make that happen. The only problem I have with all this now is that in order to get this to work right, I had to monkey with that first column-the auto-generated key field. So now I have lost that and I don't know how (or even if) to get it back-possibly delete the values that are left there and re-generate them? Any ideas? Are they really all that necessary?

One more question for anyone- one of my fields is a date field but only a month and year which I would like to express as "1/93" for example. How can I do this? It seems to like the normal mm/dd/yy format but not the simple month/year.

 

[PHV]

If the date field is a DateTime field then you only have to play with Format.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HenryAnthony]

Glad to hear you are making some progress. If you appended and deleted many records with an index, Access somehow saves the indexes and they bloat the database (not sure how to explain this better). On the main menu bar, click on Tools>Database Utilities>Compact and Repair Database. This should reduce your file size. And, pick up a good book on Access. If you can learn Flash, you can learn Access. I began learning Access at the same time as Director. They are in many ways similar.

 

[rickscript]

HenryAnthony-
Bloat the database is an understatement, it was like a database genetic experiment gone wrong. I have a pair of books on the office suite (one is for 1997 and the other 2000) but neither does an adequate job explaining Access. So if you could recommend one I'd sure appreciate it. FWIW, I have documented a step-by-step explanation of how I converted (successfully now) my old Symantec Q&A database to Access. I did it for myself, so I wouldn't forget it if I need it again, but also to help anyone else that may need to do something similar, I had found some people advertising their services to do the same process and they were charging a minimum of $120.00 per database document (more for ones with large numbers of records). Since this is not something that can't be done by ordinary individuals for themselves, I'd like to donate this somewhere but I don't know how or where to do it. If anyone here knows a way or if there is a place at this forum to put this, please let me know. It may be that there are people with data that they would like to convert but are unwilling 9or unable) to afford that amount of money.

 

[HenryAnthony]

I have "Using Microsoft Access 97" from Que. I think it is pretty good. Also have the manual from version 2.0 which is excellent at explaining relational databases in my opinion. As far as your conversion process, you could write a faq. Just click on the faq link and scroll down.