Problem with Sub Reports/linkage 1

[kentwoodjean]

Have 1 table that contains 4 catergories (effic, prod, non-prod, & Lost) necessary in a report by month. Because each item can be composed of several components with different descriptions, I have made 4 crosstab queries from the same table. Each query uses a field "EmpID" as the linking field, so the 4 crosstab reports can come together for each employee. What I can't seem to do is get my final outcome to happen. It continues to show the same employee over and over again with each of the 4 reports. Each crosstab consists of the following:

EmpID - row heading
Description - row heading
Date - column heading
Category - Value
TotOfCategory - row heading

I have tried using a blank report with just a page header and detail section where I put each of the 4 crosstabs. No linkage taking place and each crosstab shows individual entries for each employee before going to the next crosstab.

I then set up the report by making a 5th query from the same table using just the EmpID, section# and name. I used that for the page header, EmpID header, & Detail. I then include each of the 4 crosstabs in the detail where I indicate the EmpID will be used to link to the EmpID header. No luck and get the same employee.

Hopefully this makes sense and I have given the proper information. I have spent over a week trying different combinations to get it working. Thanks.

 

[dhookom]

Try binding EmpID to a control in the same section as the subreports. Then use EmpID to link master/child.

It looks like your table struction is un-normalized. I assume effic, prod, non-prod, & Lost are all time categories.

Are you using actual dates or relative dates or other for your column headings?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Here are the entries on each of the 2 tables:

Tbl_Emp_Perf by Operation (I pull the Efficiency # using Empid, Category, Desc, and Date as the other entries for my crosstab)

EMPID Text
Emplstnam Text
EmpfstName Text
CostCtr Text
Dept Text
SectID Text
SectName Text
Category Text
Desc Text
Date Text
Units Number
Efficiency Number

Tbl_Emp Detail (I pull Lost, Non-Prod, Production as separate crosss tabs using EMP ID, type, Desc, Date as constants in each one)

EMPID text
EmplstName text
EmpfstName Text
CostCtr Text
SecID Text
Category Text
DESC Text
DATE Text
Lost Number
Non-Prod Number
Production Number

Does that explain a bit better my table set up? Thanks.

 

[dhookom]

First of all, I wouldn't name a field "DESC" since DESC has a specific meaning in SQL. Also, Date is the name of a function.

I would create a union query of your Tbl_Emp_Perf by Operation and Tbl_Emp Detail (3 times). Then create a single crosstab based on the union query. The employee as well as the time categories would be the row headings. I would use relative dates for the column headings.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Since my files are at work and I don't have my laptop here, I will rework it tomorrow and let you know the outcome. The data that is coming to me was not of my design and are text files. I set my tables based on the headings that were on the files orginally when I made my first import over a year ago. No reason I can't do some adjusting though in order to meet the needs of my Boss's latest request.

 

[kentwoodjean]

I was not able to work it until this morning and, pardon my ignorance, but I have spent about 2 hours on something that should probably be very simple but I guess I am just not getting it. I have done very little with Union queries and in looking at my how to sources, here is what I have now (after other attempts)

SELECT [EMPID],[EMPLSTNAME],[EMPFSTNAME],[DATE],[SECTID]
FROM [WMI_EMP_PERF by Operation]

UNION ALL SELECT [EMPID],[EMPLSTNAME],[EMPFSTNAME],[DATE],[SECTID]
FROM [WMI LNN EMP DETAIL 92660];

These are all the like fields that would be included in both tables and I am not sure what else should go in. You had indicated to do the Union 3 times which I am not sure I understand why and how each of them will look, then to create 1 cross tab. I need to get my effic. # from WMI_EMP_PERF by Operation and the Lost, Non-Prodt and Production values from WMI LNN EMP DETAIL 92660. Where do all these fit in the Union Query(s)?

 

[dhookom]

I would create a union query to normalize these values effic, prod, non-prod, & Lost like:
[blue][tt]
SELECT EMPID, DATE as WorkDate, [Desc] as Description, Lost as Hrs, "Lost" as Category
FROM [tbl_Emp Detail]
UNION ALL
SELECT EMPID, DATE, [Desc], [Non-Prod], "NonProd"
FROM [tbl_Emp Detail]
UNION ALL
SELECT EMPID, DATE, [Desc], [Production], "Production"
FROM [tbl_Emp Detail]
UNION ALL
SELECT EMPID, Date, [DESC], [Efficiency], "Efficiency"
FROM [Tbl_Emp_Perf by Operation];
[/tt][/blue]
Save the query as "quniWorkByCategory"
You can then create a crosstab based on the union query. Use EMPID, Description, and Sum of Hrs as Row Headings; a calculation using WorkDate as the Column Heading; and Sum Of Hrs as the Value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Beautiful!! Now I see what you meant. I was approaching from the wrong angle. Makes sense and saves a million queries. Its also a very good learning experience to which I can use for other problems. AS always, you are a great help.

2 additional questions. (1) If I wanted to make this a rolling 12 months I assume I would put that in the Union Query? I have a sample of the rolling 12 month format using 3 letter months but when I tried it on the Union, it did not work. Could you show how I would handle that? (2) Each individual is appearing in the report just as I want and each of the categories is indicated. In a few of the categories, all the descriptive reasons are shown, whether they were used or not by that person. How do you restrict the reasons to only those used by the person in question?

 

[dhookom]

I was going to make some suggestions prior to your post but didn't want to take the next step until you had success with the previous steps. Check this faq703-5466 which creates dynamic monthly columns.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Thanks for the FAQ. It was helpful. Prior to the union query, I had separate crosstabs for each of my categories (4). AS a result, I used a parameter of "Is Not Null" for the listed field on each query, Lost, Efficiency, Production, Non-Prod. This prevented unused items within each category from appearing on an individuals report. Only those that they personally used appeared. I tried using that with "category" on the Union query, but it did not work. Now that I am working with a Union query, how do I make that happen.

 

[dhookom]

I'm not sure what you are asking. Have you tried something like?

UNION ALL
SELECT EMPID, DATE, [Desc], [Non-Prod], "NonProd"
FROM [tbl_Emp Detail]
WHERE Nz([Non-Prod],0) <> 0

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Sorry, but that didn'g work. Below might give you an idea. These are a few of the many reasons that fall in the "Lost' Category. IF this emp only used 2 items over a 12 month period, all the other reasons will still show up also and I don't want that. In my previous 4 crosstabs, I put "is not null" in the criteria, and it would only give me the entries that were populated with a value of time unlike the appearance below. It appears to be taking all the reasons used by all employees rather than those specific to that employee. Does that Help? In addition to your idea, I also tried "WHERE ([Production]) Is not Null" and that was unsuccessful as well.

EMPID Category Description Jan Feb +other 10 mo
103424 Lost Surveys 7.75 103424 Lost Break
103424 Lost Meetings
103424 Lost Sick
103424 Lost Unsched
103424 Lost Vac 15.50

 

[dhookom]

What's the SQL view of your crosstab query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Believe it or not, I got it working. Had a minor spacing issue but all is well now. It looks so good. Thanks so much for all your help. The outcome is just what I had hoped.

Your patience and assistance are appreciated.

 

[dhookom]

No problem. In the future, always consider normalizing your data for ease of reporting.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]