Querying to yield similar data across several tables

[rcazador]

Hi,
I'm working on transitioning some of my departments files from MS Excel to MS Access. The data I'm working with is a large customer list. And daily/weekly payments/refund details. I've several of these daily/weekly worksheets, which contain the customer id and the amount refunded as well as the nature of the refund(cause) among other details. After importing/copying the sheets into an Access DB as tables I need to perform the following queries.

1. Find all the refunds for a particular cause during a particular period(within a date range)
2. Find all the refunds made to a particular customer id
In both cases all the cases the data must be drawn from all the tables containing the customer id in question.

If it were all in a single table getting these results from queries would be easy. But can these be accomplished when each refund is a separate table. Before you ask I'm only trying to keep the tables separate since my colleagues are quite specific on that detail. As a novice I can only imagine the query design view adding all the tables and 'choosing' the columns concerning the cause of refund from each table(there are more than a 100 of these)
and running a query.


Suggestions?


Thank you.

[NTC]

moving in data from excel into Access is quite common so in that regard you are in the mainstream. But as you are finding out - the work sheet logic does not translate into separate database tables. This is not an Access issue - it is a general conceptual issue on the difference between spread sheets and relational databases.

Sometimes you must consolidate the data into a single table where ever the data is spread across separate sheets. Sometimes one must break work sheets into separate tables. It is all about fundamentally understanding your data.

[orange]

Here is a tutorial that will help you learn the concepts of database table design. You'll have to work through it, and there are steps along the way with solution.
Good luck.

[rcazador]

moving in data from excel into Access is quite common so in that regard you are in the mainstream. But as you are finding out - the work sheet logic does not translate into separate database tables. This is not an Access issue - it is a general conceptual issue on the difference between spread sheets and relational databases.

Sometimes you must consolidate the data into a single table where ever the data is spread across separate sheets. Sometimes one must break work sheets into separate tables. It is all about fundamentally understanding your data.

Here is a tutorial that will help you learn the concepts of database table design. You'll have to work through it, and there are steps along the way with solution.
Good luck.

Thanks for the info guys. I've only dabbled in Access, this time I'll start reading up on it properly.