Combining Crosstab Queries

[tcveltma]

Hi again,

Ok, so I have about 15 crosstab queries. Each crosstab query is an employee's name. Within the query are the weeks as the column heading, the work order numbers as the row heading, and the hours as a value. Each query comes from a different employee table (1 table per employee). Is there any way I can combine all the queries into 1 query/report?
So far it seems to me that the only way I'll have a chance at making every employee name, week, work order number, and hours fit into one query would be to have a giant table of that information and work from there. Could someone please let me know if there is an easier way to show that information?

Thanks,
Tiffany

[Rabbit]

Hi again,

Ok, so I have about 15 crosstab queries. Each crosstab query is an employee's name. Within the query are the weeks as the column heading, the work order numbers as the row heading, and the hours as a value. Each query comes from a different employee table (1 table per employee). Is there any way I can combine all the queries into 1 query/report?
So far it seems to me that the only way I'll have a chance at making every employee name, week, work order number, and hours fit into one query would be to have a giant table of that information and work from there. Could someone please let me know if there is an easier way to show that information?

Thanks,
Tiffany

I assumed all the information was already in one giant table. Is this not the case? How are the tables set up then?

[tcveltma]

Each time an employee fills out the form, their information goes to their own table. So each employee has their own table. I'm finding more and more that one combined table is the more effective option so I will probably end up doing that....making my previous dilema a useless question. So I guess I don't really need any more help yet. Thanks for reading though :)

Tiffany

[Rabbit]

Each time an employee fills out the form, their information goes to their own table. So each employee has their own table. I'm finding more and more that one combined table is the more effective option so I will probably end up doing that....making my previous dilema a useless question. So I guess I don't really need any more help yet. Thanks for reading though :)

Tiffany

Good luck. You can make a large query using a union query. Don't forget to include a field to specify to whom the record belongs.

[Scott Price]

Each time an employee fills out the form, their information goes to their own table. So each employee has their own table. I'm finding more and more that one combined table is the more effective option so I will probably end up doing that....making my previous dilema a useless question. So I guess I don't really need any more help yet. Thanks for reading though :)

Tiffany

You might benefit more from reading this tutorial first:

Database Normalisation Tutorial

I.e. instead of one giant table, you would have an employees table, then a work orders table, etc... They link better through queries that way...

Regards,
Scott

[tcveltma]

instead of one giant table, you would have an employees table, then a work orders table, etc... They link better through queries that way...

Thanks for the advice. I have a few of the tables you suggested. Unfortunately I don't know enough about relationships to do anything with them....a task for another day I suppose.

Thanks,
Tiffany

[Scott Price]

Thanks for the advice. I have a few of the tables you suggested. Unfortunately I don't know enough about relationships to do anything with them....a task for another day I suppose.

Thanks,
Tiffany

For that other day :-)

Relationships aren't really all that difficult to understand! Once you grasp the essential fact of Relational Databases that you should only store one unique piece of information in one place, it follows logically that you need to 'relate' or connect each piece of information that you have stored.

Relationships then are the connections between pieces of data. They exist in three forms: Many to Many, One to Many and One to One. The most common type of relationship is One to Many. It means that One piece of information (i.e. SalesmanID) can be related/connected to Many orders. From this example you can easily understand that Many to Many and One to One relationships refer to tables/subjects that store data in their own particular format.

All data stored in a database have these types of relationships inherently. All you are doing by designing your database correctly is reflecting the relations/connections that already exist!

As I think I mentioned in a post to the Database Normalization tutorial , the best/easiest to understand book that I know of is Database Design for Mere Mortals, 2nd edition by Mike Hernandez. I'm not trying to be his advertising manager, but I would strongly urge you to read the book! It's well worthwhile for anyone who works with databases.

Regards,
Scott

[tcveltma]

Great thanks, I'll try that.

Tiffany