Appending data to multiple Tables in Access 2003

[Kimg0123]

I would like to be able to append data from multiple tables into a single summary table using either an event procedure for the On Load or On Open properties on a form, but cannot seem to figure this out. Is it possible to do this using a macro or event procdedure in Access 2003?

I tried both but cannot seem to get either to work.

Please help...

Thanks!

[Scott Price]

Generally speaking you would use an Append query to do this. The general syntax is:

Insert Into [Destination Table]
Select [Select Criteria]
From [Source Table(s)]
Where [Where Criteria]

Note that this is just like a regular select query, with the exception of the Insert Into clause.

Welcome to the Scripts!

Regards,
Scott

[Kimg0123]

OK, I am not sure how to do this... I have 22 tables that are exactly the same except for one column, which indicates the AlphaCategory of the data being entered for that table. I need to append data from all of these tables into a summary table that will combine all data. I also need two fields combined (AlphaCategory and Unique ID) from each of the individual tables to create a File_ID for each record, and I have set the criteria for each table to not append the records if the "original file name" is null.

I created two append queries, the first to append the first 11 tables and the next to append the next 11 of the 22 tables. When I ran them, I get the following error: "Duplicate output destination 'alphacategory'" [the alphacategory is in each of the tables and identifies the data type for the group of records in that table]. I think that I would most likely get this error for each of the columns I am trying to append.

I'd have also created individual append queries for each of hte tables and would like to run an event procedure or a macro that would run them right after each other in sequence to get them appended. Is this a possible workaround or is there something else that I can try?

-------------------------------------------------------------------------------------------------------------------

[Scott Price]

Why 22 tables exactly the same?

The essential concept of a Relational Data Base Management System (of which Access is just one) is that you only store ONE piece of information in ONE place.

If you are locked into have the database structure non-normalised, you could explore using a Union query first to group the data, then run the Append query.

Have a look, though, at these tutorials: Database Normalisation and Table Structures and Basis SQL Syntax for Access queries

Regards,
Scott

[Kimg0123]

Well, I created this db on the fly and needed to ensure that the data types were managed separately... each of the separate data types needed to have a unique ID that is specific to the data type... The data types are alphacategories that go from A to V; each one represents a different type of stored data and we need to track the same information for each of hte data types, thus the need for the same data in each of the tables A through V except the alphacategory.

The goal is to have the alphacategories, A for instance, enumerate each record consecutively then have the same thing happen for each of the other alphacategories/data types. So, if we had 500 records for category A - they would be numbered sequentially like this A10001 to A10500, and 300 records in category B, they would be numbered in the same fashion, i.e. B10001 to B10300. Thus, each data type A-V will have its own group of unique alphanumerics assigned to it.

I din't come up with the idea, only the solution to data entry to accomplish this goal in naming convention for each of the data types. Do you have any other suggestions on how to do this?

---

Why 22 tables exactly the same?

The essential concept of a Relational Data Base Management System (of which Access is just one) is that you only store ONE piece of information in ONE place.

If you are locked into have the database structure non-normalised, you could explore using a Union query first to group the data, then run the Append query.

Have a look, though, at these tutorials: Database Normalisation and Table Structures and Basis SQL Syntax for Access queries

Regards,
Scott

[Scott Price]

How about posting for me the meta data of one of your tables, I'll be better able to give a suggestion after seeing this.

Here is an example of how to post table MetaData :
Table Name=tblStudent

      Field; Type; IndexInfo
      StudentID; AutoNumber; PK
      Family; String; FK
      Name; String
      University; String; FK
      Mark; Numeric
      LastAttendance; Date/Time

Regards,
Scott