Adding Data to a blank "Template Table"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Day,
I am trying to add data to a blank template table that I created.
1. I am having some trouble converting the Excel file with the data (40,000
records) into an access table (presumably because of the column names). I
think I can work around this issue by renaming each and everyone of the
fields...then deleting the first row (which has the column names)...ughhh.
2. The main question: Once I have the excel data (which comes in
periodically) in a table; how can I add the data to a blank database table?
The append query and the update query both gave me the message "Updating 0
rows"
I have no way of creating a relationship (drawing a line between the two
tables in query mode) since the template (blank table) contains 0 records.
P.S. The reason I have this template table is because there are numerous
fields that I set up in design view with very specific criteria. I do not
want to have to redefine the specifications of each field everytime I import
a new set of data (the excel sheet) into the blank template table.
 
Good Day,
I am trying to add data to a blank template table that I created.
1. I am having some trouble converting the Excel file with the data (40,000
records) into an access table (presumably because of the column names). I
think I can work around this issue by renaming each and everyone of the
fields...then deleting the first row (which has the column names)...ughhh.
2. The main question: Once I have the excel data (which comes in
periodically) in a table; how can I add the data to a blank database table?
The append query and the update query both gave me the message "Updating 0
rows"
I have no way of creating a relationship (drawing a line between the two
tables in query mode) since the template (blank table) contains 0 records.
P.S. The reason I have this template table is because there are numerous
fields that I set up in design view with very specific criteria. I do not
want to have to redefine the specifications of each field everytime I import
a new set of data (the excel sheet) into the blank template table.
Click to expand...

Can you use File... Get External Data... Link to *link* to the spreadsheet? If
you can, you should be able to run an Append query from the linked spreadsheet
into your stored (empty) table. As you have seen, a join or an Update query
are inappropriate. What specific errors are you getting when you try to import
(due to the header row)?

A getaround if the header row is unmanagable would be to create a named range
excluding that row but including all the data, and link to that named range.

John W. Vinson [MVP]
 
Thanks for the quick response!

The error reads..."An error occurred trying to import file ... THe file was
not imported." I can work around it by entering the field names...or I will
try linking.

My main concern, however, is getting the data (once it is in a table) to
load into my template. The two have columns that are similar, but they are
not in the same order. Will this create a problem? How can I use an append
query to add the data into the blank table? Any help is GREATLY
APPRECIATED!!!

Thanks again!
 
I just thought of a possible solution, but I would need to know if it seems
viable from someone....

How about in my case, I just create an empty (Null) field in my data table
(the one that came from Excel), and I create a field of the same type (text)
in the template table. I then run an update query and creat a join between
the two tables with the blank fields....and choose the option to include ALL
records from the data table and only those where the template table are
equal.

Would that trick access into running my append the way I want?

Thanks again for a response, I am needing to complete this portion of the
project ASAP!

:)
 
My main concern, however, is getting the data (once it is in a table) to
load into my template. The two have columns that are similar, but they are
not in the same order. Will this create a problem? How can I use an append
query to add the data into the blank table? Any help is GREATLY
APPRECIATED!!!
Click to expand...

The fieldnames are COMPLETELY IRRELEVANT. The order of the fieldnames is
COMPLETELY IRRELEVANT.

You can very easily set up an append query to append from any selected field
in the source into any selected field in the target.

If you cannot link to the spreadsheet, perhaps you could post back with a
couple of lines of sample data (fake data if it's confidential).

John W. Vinson [MVP]
 
I have tried to use the append query about 20 different ways. Without some
sort of relationship between the source and target. It will not append any
data. It seems like such an easy task, but I can not get it to work. I keep
getting an error saying that 'x' number of records were not appended. The
only work around I can figure out is to go into the table view and then
change the query to make table. Then I can get the results i need.
Although, since it won't actually perform the query, I dont get the field
descriptions and names from the target table that I want. So, I have had to
go back in and re-type and re-select all of that. Why is appending data so
difficult and 'un-user-friendly"?

Thanks again for your help! I just need to know an easy way to append from a
source to a blank target?????????????????
 
I have tried to use the append query about 20 different ways. Without some
sort of relationship between the source and target. It will not append any
data. It seems like such an easy task, but I can not get it to work. I keep
getting an error saying that 'x' number of records were not appended. The
only work around I can figure out is to go into the table view and then
change the query to make table. Then I can get the results i need.
Although, since it won't actually perform the query, I dont get the field
descriptions and names from the target table that I want. So, I have had to
go back in and re-type and re-select all of that. Why is appending data so
difficult and 'un-user-friendly"?

Thanks again for your help! I just need to know an easy way to append from a
source to a blank target?????????????????
Click to expand...

Well, there should certainly NOT be a relationship; the append query must be
based on the soucre table, and only on the source table.

Could you post the SQL view of the MakeTable query, and of one of the append
queries which didn't work?

An Append query is *just as easy* as a MakeTable query... unless you are doing
something odd to make it more difficult, which certainly sounds to be the
case.

Just for the SQL syntax of a valid Append query, consider

INSERT INTO targettablename(fieldA, fieldB, fieldC)
SELECT thisfield, ThatField, SomeOtherField FROM sourcetable;

Note that the target table name is not involved in the SELECT clause, only the
INSERT INTO clause.

Another possible issue is that "x records were not appended" because you're
violating some sort of constraint on the table - a primary key that's getting
NULL or duplicate data, a required field that's not getting any data, a
foreign key field that's getting an invalid value, etc.

John W. Vinson [MVP]
 
I FIGURED IT OUT!

On the template (blank) table....I had one field set as the key. THat was
screwing things up to where the query would not run!
Thanks for your help though!!!
 
Back
Top