Consolidating many tables same Fields

  • Thread starter Thread starter M.A.Halim
  • Start date Start date
M

M.A.Halim

Dear All
Im creating a new database mdb. And In order to collect data for this
database I created an excel sheet and sent it to 22 locations in 22 cities.
then on daily basis each location will fill up the data and email to me. then
I have to combine all those 22 files in one main table. since the excel
sheets will be filled up daily I expect a very big database by mid of the
year. Now all the 22 locations will fill up the same format.
And If I linked these 22 files in one database I will have the same fields
are repeated 22 times in 22 tables in the same Database. Otherwise I will
have to copy them and paste in the Access table. because if I used the data
imported "By Getting External Data" to this main table it might over write
the data of one location to another. How can I avoid this and what is the
best Idea to follow in such database.
Thanks
 
Dear All
Im creating a new database mdb. And In order to collect data for this
database I created an excel sheet and sent it to 22 locations in 22 cities.
then on daily basis each location will fill up the data and email to me. then
I have to combine all those 22 files in one main table. since the excel
sheets will be filled up daily I expect a very big database by mid of the
year. Now all the 22 locations will fill up the same format.
And If I linked these 22 files in one database I will have the same fields
are repeated 22 times in 22 tables in the same Database. Otherwise I will
have to copy them and paste in the Access table. because if I used the data
imported "By Getting External Data" to this main table it might over write
the data of one location to another. How can I avoid this and what is the
best Idea to follow in such database.
Thanks
Click to expand...

I'd suggest creating one table with all the fields in the spreadsheets, plus
one additional field indicating the location. You can then use File... Get
External Data... Link to *link to* (not import!) the spreadsheets, daily; and
run 22 Append queries (using VBA code to loop through them, natch). You would
include the location as a text literal calculated field in the append query.
E.g.

INSERT INTO Master (Location, Thisfield, Thatfield, Theotherfield, ...)
SELECT "Poughkeepsie", Field1, Field2, Field3, ...
FROM xlsPoughkeepsie;

where xlsPoughkeepsie is the name of the linked spreadsheet from that town.

John W. Vinson [MVP]
 
Back
Top