Combine crosstab with other query info

[LadyIlsebet]

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

 

[KARL DEWEY]

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");

 

[LadyIlsebet]

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

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

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

 

[KARL DEWEY]

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

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

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

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