Crosstab Query and where 4

[dorling]

i got a query and a form, On the form it as the year which i need to display, it is in a text box.[Forms]![switchboardforH&S]![txtyears] that is where it is.

i need the crosstab query to only display this year results

this is the SQL of the query this work fine

TRANSFORM Count(tblAccident.[Date/Time of Accident]) AS [CountOfDate/Time of Accident]
SELECT tblDepartment.Department
FROM (tblDepartment INNER JOIN tblPerson ON tblDepartment.Department = tblPerson.Department) INNER JOIN tblAccident ON tblPerson.ID = tblAccident.PersonID
WHERE ((((Format([Date/Time of Accident],"yyyy")))="2004"))
GROUP BY tblDepartment.Department
PIVOT Format([Date/Time of Accident],"mmmm");

but if i use this, which is the one that take the year value from the text box on the form it does not work

TRANSFORM Count(tblAccident.[Date/Time of Accident]) AS [CountOfDate/Time of Accident]
SELECT tblDepartment.Department
FROM (tblDepartment INNER JOIN tblPerson ON tblDepartment.Department = tblPerson.Department) INNER JOIN tblAccident ON tblPerson.ID = tblAccident.PersonID
WHERE ((((Format([Date/Time of Accident],"yyyy")))=[Forms]![switchboardforH&S]![txtyears]))
GROUP BY tblDepartment.Department
PIVOT Format([Date/Time of Accident],"mmmm");

the error msg I get is "The Microsoft Jet database engine does not recognize '[Forms]![switchboardforH&S]![txtyears]' as a valid field name or expression. (Error 3070)

How can i fix this problem

thanks a lot

 

[dhookom]

You must select Query|Parameters and then enter:
[Forms]![switchboardforH&S]![txtyears] Integer

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dorling]

THAT WORK FINE

if say the year in [Forms]![switchboardforH&S]![txtyears] is 2004

and i want to display 2004 and 2003 and 2002

how want i go about it

i try [Forms]![switchboardforH&S]![txtyears]-1 but no luck

 

[dhookom]

Try:
WHERE Year([Date/Time of Accident]) Between [Forms]![switchboardforH&S]![txtyears] - 2 AND [Forms]![switchboardforH&S]![txtyears]


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cdavisdccb]

Duane -- your a bloody genius!
Thanks,
Chris

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"
- Bazooka Joe

 

[spoox2]

Hi!,
I am having the same problem and did not understand where to add the "Integer" part.
I am working on a cross-tab query which i have never worked with before and am taking over a project from another employee who has used it for the project. I have 2 cross tab queries, the results of which are used joined using a union query. The Union query results are dumped into a table using a make-table query. I now need to just get records from this table where the date field (format is mmmm, yyyy) matches the datefield in the form which is in the same format. But i get the message saying that the database engine does not recognize the Forms![abc]![def] as a valid field name.
Please help me address this issue. I have read that i need to declare the type of data the object contains whether it is text or integer ect., but don't know where to add it.
Thanks.

 

[dhookom]

While in the design view of the query using the criteria, select from the menu [tt]Query|Parameters[/tt] and enter the exact parameters and their data types.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[spoox2]

Thanks Duane.
I jus' made a make table query out of the final query and used the check on the table data in a new query and thus got the problem solved. Thanks for ur input still as i learnt quite abit of crosstab queries.

 

[JoeMo]

I have:

TRANSFORM Sum(nz(a.Credit)-(nz(a.Debit))) AS TotalThisMonth
SELECT Month(a.Date) & "/" & Year(a.Date) AS MonthYear
FROM AccountEntries AS a
WHERE ((Year([a].[Date])=2004) AND ((a.Property) Is Not Null)) AND ((Exists (Select b.ID, b.ParentID from AccountEntries as b where b.ParentID = a.ID))=False)
GROUP BY Year(a.Date), Month(a.Date) & "/" & Year(a.Date)
ORDER BY Month(a.Date) & "/" & Year(a.Date), Year(a.Date)
PIVOT a.Property;

to which I get:

The Microsoft Jet database engine does not recognize 'a.ID' as a valid field name or expression.

So I try putting in a parameter:

PARAMETERS [a].[id] Long;
TRANSFORM Sum(nz([a].[Credit])-(nz([a].[Debit]))) AS TotalThisMonth
SELECT Month([a].[Date]) & "/" & Year([a].[Date]) AS MonthYear
FROM AccountEntries AS a
WHERE (((a.Property)<>"51 N. Maple" And (a.Property) Is Not Null) AND ((Year([a].[Date]))=2004) AND ((Exists (Select b.ID, b.ParentID from AccountEntries as b where b.ParentID = [a].[ID]))=False))
GROUP BY Year([a].[Date]), Month([a].[Date]) & "/" & Year([a].[Date])
ORDER BY Month([a].[Date]) & "/" & Year([a].[Date]), Year([a].[Date])
PIVOT a.Property;

To which it replies by asking me to supply a value for a.id

But I don't want to have to supply a value. I already specified a value by the sub-select. Any way around this?

 

[dhookom]

Your query may not like the use of the table alias. Also, I have found that crosstabs in general do not like subqueries.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

You may try this:
TRANSFORM Sum(Nz(a.Credit)-Nz(a.Debit)) AS TotalThisMonth
SELECT Format(a.Date, "mm/yyyy") AS MonthYear
FROM AccountEntries AS a LEFT JOIN AccountEntries AS b ON a.ID=b.ParentID
WHERE Year(a.Date)=2004 AND a.Property Is Not Null AND b.ParentID Is Null
GROUP BY Format(a.Date, "mm/yyyy")
ORDER BY Format(a.Date, "mm/yyyy"), Year(a.Date)
PIVOT a.Property;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JoeMo]

dhookom: When a subquery acts on the same table as the main part of the query, I see no other way besides using an alias at least for the subquery portion. I chose to use aliases both in the main portion as well as the subquery for brevity's sake; retaining the table's full name for the main part of the query seems to make no sense. But I think you're right about subqueries and transform statements.

PHV: Yours seems to do what I need it to. I guess I was making things needlessly complicated with that subselect. Thanks.