Criteria in Crosstab query 1

[Nacar]

Hello.
I have a report based on a crosstab query. It works fine.
I have other report based in a normal query.
Now I need to put the first one (based on crosstab query) inside the second one.
But I find two problems:
1. I cannot link the two reports (Link Child Field with Link Master field)
2. The crosstab query doesn't recognize the criteria in a field. (Criteria like this: [Reports]![rptShoeRef]![ShoeID])
This is driving me crazy and the people are pushing me.
I will appreciate your worth help.
Thanks.

Nacar

 

[Nacar]

The based crosstab report looks like this:
ShoeID Color Run12 Run32 Run75
BBOOJJKK Red 10 5 15
BBOOJJKK White 5 8
BBOOJJKK Blue 3 6

ShoeID Color Run06 Run14 Run36
HHOOUUTT Black 7 15 2
HHOOUUTT Green 2 4

You can see the same shoe has different colors and different runs and different quantities.

The master report must be look like this:

***********************************************************
* Field.ShoeID

*Field.Picture Here must go

*Field.DeliveryDate the above report

***********************************************************

I hope this help to understand my problem.
Thanks.

 

[dhookom]

You can use criteria with crosstab queries as long as you identify the criteria data types. Select Query->Parameters and enter:
[Reports]![rptShoeRef]![ShoeID] Text

You haven't provided any information on why you can't link master child or how you are getting your crosstab to appear in your report.

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]

 

[Nacar]

Hello Duane,
The Master report contains the general info for each shoe (Material, Style, Picture, etc.). It prints five shoes per page. No problem.
But I need to insert a subreport with a matrix Color vs. Run, and Qty in the intersection.
I created a Crosstab query for its record source and it prints good by itself.
Now, when I try to insert this crosstab based report into the master report, it doesn't work.
This is the query:
PARAMETERS [Reports]![rptLineSheet3]![ShoeID] Long;
TRANSFORM Sum(tblShoeRef.IniQty) AS SumOfIniQty
SELECT tblShoeRef.ShoeID, tblShoeRef.ColorID, Sum(tblShoeRef.IniQty) AS [Total Of IniQty]
FROM tblRun INNER JOIN tblShoeRef ON tblRun.RunID = tblShoeRef.RunID
WHERE (((tblShoeRef.ShoeID)=[Reports]![rptLineSheet3]![ShoeID]))
GROUP BY tblShoeRef.ShoeID, tblShoeRef.ColorID
PIVOT "R" & tblRun.Run;

There is a message saying: "you can't use crosstab query as a record source for a subreport"

The Master report has to have 5 little squares with the subreport for each shoe. I tried to draw previously.

What else do you need, please?
Thanks.

 

[Nacar]

Duane,
Really I need only three columns. Let's try to put three fixed-columns. Could you please tell me how to do this. I have tried but I get the column name without qty.
Thanks.

 

[dhookom]

Which three columns? You might be able to enter the columns into the Column Headings property.

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]

 

[Nacar]

Duane,
let's start from the beginning.
I have four tables. tblShoes (general info for each shoe + ShoeID), tblColors (Colors + ColorID), tblRuns (Runs + RunID), and tblShoeRef (with these fields: ShoeID, ColorID, RunID, Qty.)

TblShoeRef is the “many” part of all others.

I have a Master report showing me each shoe with picture, five per page. Now I need a subreport, one for each record in the page, showing me the quantities, color and run. I made one and shows me in columnar way what it is not good. Like this (for each main record):

Color Run Qty
Black R12 5
Black R36 10
Blue R12 15
Blue R36 7

But what I need is this:

Color R12 R36
Black 5 10
Blue 15 7

You see?

 

[dhookom]

I understand your issue. You stated earlier

Really I need only three columns. Let's try to put three fixed-columns.

I don't know which three columns to display in the subreport. If the three columns are dynamic, this might be an issue.

Consider downloading and view the various crosstab report samples I created at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

One or more of these might meet you specifications.

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]

 

[Nacar]

I visited it already last night. They are great!!

The columns are from tblRuns. There are 20 runs but each shoe uses normally two runs and sometimes three.
Because no more than three, let's put only three columns for simplicity.
The actual problem is the message: "you can't use crosstab query as a record source for a subreport". The report opens and show me everything from the tblShoe and a blank square (where the subreport should go).
If I open the subreport by itself, it works and looks great. But when you open the main report, nothing!!

 

[dhookom]

You could use the solution for your crosstab found in the Crosstab.MDB. This creates an alias for each dynamic column generated by the crosstab. You could then use your crosstab report as a subreport.

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]

 

[Nacar]

Duane,
your subreport is based on a select query.Then, you use it to generate the columns in your main report which is based on a crosstab query.
Mine is the other way around. The subreport is based on a crosstab query and the main report is on a select query.
The subreport looks like a matrix in a square frame. In the main report, each shoe has to have that small square frame with its pertinent matrix.
The main report has in the Detail section the following fields: ShoeID, DeliveryDate, Material, Picture and the subreport.
Each record must generate its own matrix.
Have I been clear enough?

 

[dhookom]

You could take the Crosstab.mdb solution and use it as a subreport. The other consideration is to add all your data into the crosstab. I can't see a reason why this wouldn't work.

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]

 

[Nacar]

Duane,
Thank you for your help. Because your great samples I could find out my mistakes.
There were two mistakes. Both in the SQL statement.
Here is my original statement:

TRANSFORM First(tblShoeRef.IniQty) AS FirstOfIniQty
SELECT tblShoeRef.ShoeID, tblShoeRef.ColorID, Sum(tblShoeRef.IniQty) AS [Total Of IniQty]
FROM tblRun INNER JOIN tblShoeRef ON tblRun.RunID = tblShoeRef.RunID
WHERE (((tblShoeRef.ShoeID)=[Reports]![rptLineSheet3]![ShoeID]))
GROUP BY tblShoeRef.ShoeID, tblShoeRef.ColorID
PIVOT "R" & tblRun.

1. I was getting this message:
“The Microsoft Jet database engine does not recognize ‘[Reports]![rptLineSheet3]![ShoeID]’ as a valid field name or expression.”
You fixed this with this: PARAMETERS [Reports]![rptLineSheet3]![ShoeID] Long; at the beginning of the statement.

2. On the second mistake I was getting this message:
“You can’t use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query’s ColumnHeadings property.”

OK. Because I needed only three columns in my report and the table tblRun had 10 different runs, the Pivot clause didn’t work.
I modified it in this way: PIVOT "R" & tblRun.Run In ("R1","R2","R3”)
It didn’t work either.
Then I wrote the entire different runs with the same names, like this:
PIVOT "R" & tblRun.Run In ("R06","R08","R58","R02","R72","R68","R80","R83","R09","R04");

Great!!!! It worked fine!!!!
Thanks Duane for keeping my studying with your very powerful site.


This is the final statement:
PARAMETERS [Reports]![rptLineSheet3]![ShoeID] Long;
TRANSFORM First(tblShoeRef.IniQty) AS FirstOfIniQty
SELECT tblShoeRef.ShoeID, tblShoeRef.ColorID, Sum(tblShoeRef.IniQty) AS [Total Of IniQty]
FROM tblRun INNER JOIN tblShoeRef ON tblRun.RunID = tblShoeRef.RunID
WHERE (((tblShoeRef.ShoeID)=[Reports]![rptLineSheet3]![ShoeID]))
GROUP BY tblShoeRef.ShoeID, tblShoeRef.ColorID
PIVOT "R" & tblRun.Run In ("R06","R08","R58","R02","R72","R68","R80","R83","R09","R04");