How to handle several tables with similar content?

aner1755

Registered User.
Local time
Today, 18:55
Joined
Jan 23, 2009
Messages
43
Hi all,

I’m new to Access and trying to learn from books.
I got a question regarding how to handle imported tables. The situation is like this, and I'll try to be as precise as I can:
Every month two different Excel sheets should be imported to Access. One lists all employees time in their projects, the other is the corresponding budget for that month. This will give me a lot of tables containing the same kind of information as the months fly by…
In the end I‘d like to compare the employees to the budget, but the time interval should be arbitrary. Hence, information should be gathered from different tables.
How to handle this situation?
I’ve got two solutions in mind, but either way I encounter problems:
1)[FONT=&quot] [/FONT]Work with Queries. The problem here is that I don’t know how to make a query that collects information from two or more tables (with similar kind of content) and show the result in a satisfying way. What I need is a recordset with the records from each table (of similar kind, i.e. tables regarding time or budget) listed one per row.
At this point I’ am able to ask a multi-table query, but the records from each different table are then listed at the same row. Not very handy…

So, how to query the several tables?

2)[FONT=&quot] [/FONT]Merge tables of the same kind, to reduce the amount of similar tables and then just query one table with times and one with budgets.
But how to merge them?
I’m more than a novice regarding programming in other languages than VB, but I can handle the basics though…
Would of course be very grateful if some could help me along with this.
Cheers!
 
If your tables have similar structures then you should investigate using UNION Queries. Try looking in Access help, searching this forum and googling
 
Another alternative is to always import the data to the same exact tables as intermediary locations. Then have a second APPEND query that adds the data to the permanent tables with a "DateOfImport" field added in the query via the NOW() function. One table, many records, qualified by date of import. (Actually, TWO tables, because you would do this once for employees and once for budget.)

Then, all is in a single table and the queries become trivial.
 
:-)
Thanks guys! Exactly the answers I was looking for. I'll try to manage from here.
 
you need to consider the atomic level of information you need to manage

lets say you have a time sheet system

when you capture your spreadsheet, does this show time spent per month (or per week or even day) on a job, or cumulative time - thats the atomic level you need to manage. Ideally your database should contain for each job, the time spent by each employee, for each month (or week or day) separately, so this would affect the way you capture the new information. on the other hand if (less likely) you only need the cumulative figure in your database, then you will process the data in a different way.
 
Thanks for your point of view! :-)
For my purposes I think I've got my strucure of the database under control. Now its only the thecnical part remaining... and luckely its processing thanks to this forum. You are a helpful bunch of people :-D
 

Users who are viewing this thread

Back
Top Bottom