CODEWITHSUNDEEP
ms-accessms-access-2010
ColdTeck
ColdTeck

Reputation: 143

Access 2010 Database Fixup

I have been working on a database for a while and now I have come to a standstill, I think that I am going to have a problem if I do not build a advanced Inner Join to create a big table, as I have data of the same type in different tables, in my great brilliance I have created a table for every month of every site I work on, instead of creating a column for the month and another for the site (which I realize was a bad move on my part being an Access Noob) Is there a way that I can make a new table combining all the old tables in the database and two columns that identifies which table it came from as I can see I am heading in the wrong direction?

Then When I have this table and want to do reporting on it, is there a way to do multiple filters on it to pull the data out like it was from each individual tables. Since at my current rate I built a vba function to inner join each site for each month for each year then performed multiple queries on each inner join then sub reports on each query, so I have like 500 tables 40 inner joins then 2 queries for each inner join then a report for each query and trying to combine all the sub reports into a main report is becoming a nightmare. Realized this when I was trying to create a chart based on all the sub reports. It seems like a lot of work but with a few days of vba you can create a lot of inner joins and such.

Upvotes: 1

Views: 67

Answers (1)

HansUp
HansUp

Reputation: 97101

After you create the new master table, execute a series of append queries to load the data from each of the site/month tables.

INSERT INTO tblMaster (site_id, mnth, etc)
SELECT 1 AS site_id, 1 AS mnth, etc
FROM tblSite1_Jan;


INSERT INTO tblMaster (site_id, mnth, etc)
SELECT 1 AS site_id, 2 AS mnth, etc
FROM tblSite1_Feb;

... and so forth.

That will take a while for 500-some source tables. If you're motivated, you could build a VBA procedure to create and execute those 500 INSERT statements. Decide whether you can build a usable VBA procedure quicker than you can manually create and execute those statements.

Upvotes: 2

Related Questions