By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,992 Members | 934 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,992 IT Pros & Developers. It's quick & easy.

Combining Crosstab Queries

P: 36
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
Aug 10 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,357
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?
Aug 10 '07 #2

P: 36
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
Aug 10 '07 #3

Rabbit
Expert Mod 10K+
P: 12,357
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.
Aug 10 '07 #4

Scott Price
Expert 100+
P: 1,384
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
Aug 10 '07 #5

P: 36
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
Aug 14 '07 #6

Scott Price
Expert 100+
P: 1,384
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
Aug 15 '07 #7

P: 36
Great thanks, I'll try that.

Tiffany
Aug 15 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.