If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combine crosstab with other query info
I have a database with many tables. One of them tracks employee ID
(linked back to the main table), a requirement ID (linked to a table defining the requirements), a date, and a text field defining the way the requirement was met (linked back to a table defining the ways to meet the requirements). A crosstab query works great to have columns for the requirements, a row for each employee and the detail is when they met the requirement. However, I need to combine this in with another query so I can make a pretty report that shows information from the main table (age, sex, employee number, etc), as well as the info from the crosstab, all on the same line, to meet Ministry requirements. Right now, I've only got a couple of "dummy" records in the requirements table, so when doing the crosstab, I've only got a few columns. How can I build a query to get around that? I can send documentation of the DB itself to anyone who asks. Thanks! Amy |
#2
|
|||
|
|||
Combine crosstab with other query info
Join your tables in the crosstab and concatenate fields into a single
expression for the row heading lke this --- TRANSFORM First(tblTrainReview.ReviewDate) AS FirstOfReviewDate SELECT [FName] & " " & [MI] & " " & [LName] AS Trainee FROM tblTrainee INNER JOIN tblTrainReview ON tblTrainee.lTrainID = tblTrainReview.TrainID GROUP BY [FName] & " " & [MI] & " " & [LName] PIVOT IIf([Complete]=-1,"Yes","No"); -- KARL DEWEY Build a little - Test a little "LadyIlsebet" wrote: I have a database with many tables. One of them tracks employee ID (linked back to the main table), a requirement ID (linked to a table defining the requirements), a date, and a text field defining the way the requirement was met (linked back to a table defining the ways to meet the requirements). A crosstab query works great to have columns for the requirements, a row for each employee and the detail is when they met the requirement. However, I need to combine this in with another query so I can make a pretty report that shows information from the main table (age, sex, employee number, etc), as well as the info from the crosstab, all on the same line, to meet Ministry requirements. Right now, I've only got a couple of "dummy" records in the requirements table, so when doing the crosstab, I've only got a few columns. How can I build a query to get around that? I can send documentation of the DB itself to anyone who asks. Thanks! Amy |
#3
|
|||
|
|||
Combine crosstab with other query info
I'm sorry this has taken me so long to read and get back to - work got
rather crazy. Unfortunately, I also do not understand this explanation. Does anyone else have any ideas on how to do what I described originally? In a nutshell, I need to combine my cross tab query that shows who got what training when (so not all rows will have dates in all columns, as not all training is required), with "regular" queries that can give me the names, drivers license info, etc for each employee. Thanks, Amy On Jun 5, 3:04*pm, KARL DEWEY wrote: Join your tables in the crosstab and concatenate fields into a single expression for the row heading lke this --- TRANSFORM First(tblTrainReview.ReviewDate) AS FirstOfReviewDate SELECT [FName] & " " & [MI] & " " & [LName] AS Trainee FROM tblTrainee INNER JOIN tblTrainReview ON tblTrainee.lTrainID = tblTrainReview.TrainID GROUP BY [FName] & " " & [MI] & " " & [LName] PIVOT IIf([Complete]=-1,"Yes","No"); -- KARL DEWEY Build a little - Test a little "LadyIlsebet" wrote: I have a database with many tables. One of them tracks employee ID (linked back to the main table), a requirement ID (linked to a table defining the requirements), a date, and a text field defining the way the requirement was met (linked back to a table defining the ways to meet the requirements). A crosstab query works great to have columns for the requirements, a row for each employee and the detail is when they met the requirement. However, I need to combine this in with another query so I can make a pretty report that shows information from the main table (age, sex, employee number, etc), as well as the info from the crosstab, all on the same line, to meet Ministry requirements. Right now, I've only got a couple of "dummy" records in the requirements table, so when doing the crosstab, I've only got a few columns. How can I build a query to get around that? I can send documentation of the DB itself to anyone who asks. Thanks! Amy |
#4
|
|||
|
|||
Combine crosstab with other query info
Post the SQL of your crosstab query. Open in design view, click on menu VIEW
- SQL View. Highlight all in the new window, copy, and paste in a post. Post the SQL of your select query. Post an example of what you want the results to look like. Post sample data from the tables. -- KARL DEWEY Build a little - Test a little "LadyIlsebet" wrote: I'm sorry this has taken me so long to read and get back to - work got rather crazy. Unfortunately, I also do not understand this explanation. Does anyone else have any ideas on how to do what I described originally? In a nutshell, I need to combine my cross tab query that shows who got what training when (so not all rows will have dates in all columns, as not all training is required), with "regular" queries that can give me the names, drivers license info, etc for each employee. Thanks, Amy On Jun 5, 3:04 pm, KARL DEWEY wrote: Join your tables in the crosstab and concatenate fields into a single expression for the row heading lke this --- TRANSFORM First(tblTrainReview.ReviewDate) AS FirstOfReviewDate SELECT [FName] & " " & [MI] & " " & [LName] AS Trainee FROM tblTrainee INNER JOIN tblTrainReview ON tblTrainee.lTrainID = tblTrainReview.TrainID GROUP BY [FName] & " " & [MI] & " " & [LName] PIVOT IIf([Complete]=-1,"Yes","No"); -- KARL DEWEY Build a little - Test a little "LadyIlsebet" wrote: I have a database with many tables. One of them tracks employee ID (linked back to the main table), a requirement ID (linked to a table defining the requirements), a date, and a text field defining the way the requirement was met (linked back to a table defining the ways to meet the requirements). A crosstab query works great to have columns for the requirements, a row for each employee and the detail is when they met the requirement. However, I need to combine this in with another query so I can make a pretty report that shows information from the main table (age, sex, employee number, etc), as well as the info from the crosstab, all on the same line, to meet Ministry requirements. Right now, I've only got a couple of "dummy" records in the requirements table, so when doing the crosstab, I've only got a few columns. How can I build a query to get around that? I can send documentation of the DB itself to anyone who asks. Thanks! Amy |
Thread Tools | |
Display Modes | |
|
|