Windows 7 32bit 
Access 2010 32bit
What I have is a business that bids about 400 jobs every year. A bid is done with a spreadsheet I have created that is a single page with about 10-12 columns dictating all the line items and charges etc. that it would take to accomplish a single job. We are awarded roughly 100 jobs per year. Each job is represented by a spreadsheet, and I need to get the data on each spreadsheet into an access database. This would be easy if each spreadsheet was static, but each spreadsheet changes as the job is actually worked, so I can't just append each Excel sheet into one large table and work with that. What I'm currently doing is making a table for every spreadsheet that reads the changes that occur in the spreadsheet and updates the database accordingly.
The one thing I have going for me here is that all the column names for each spreadsheet will always stay the same, it's only the rows of data that change. However, it gets increasingly difficult to manage queries that join and query 50-100+ tables at a time.
Can anybody either suggest a way to write a query that automatically combines a dynamic number of tables into one, or can you suggest a better way of designing my database? Is there some feature I'm not aware of that will let me handle/query so many different tables of data?
Reply With Quote
