Help with crosstab query and combo boxes

[kcsims]

The following is my SQL from my crosstab query:

TRANSFORM Count(INTERVAL.NEXT_INSPECTION_DATE) AS [The Value]
SELECT INSPECTIONTYPEGROUP.INSPECTIONTYPEGROUP_DESC,
UNITNAME.UNITNAME_DESC, Count(INTERVAL.NEXT_INSPECTION_DATE) AS [Total Of
NEXT_INSPECTION_DATE]
FROM UNITNAME INNER JOIN ((INSPECTIONTYPEGROUP INNER JOIN INSPECTIONTYPE ON
INSPECTIONTYPEGROUP.INSPECTIONTYPEGROUP_ID =
INSPECTIONTYPE.INSPECTIONTYPEGROUP_ID) INNER JOIN (ASSET INNER JOIN
INTERVAL ON ASSET.ASSET_ID = INTERVAL.ASSET_ID) ON
INSPECTIONTYPE.INSPECTIONTYPE_ID = INTERVAL.INSPECTIONTYPE_ID) ON
UNITNAME.UNITNAME_ID = ASSET.UNITNAME_ID
WHERE (((Format([NEXT_INSPECTION_DATE],"yyyy"))=2006) AND
((UNITNAME.UNITNAME_DESC)="45"))
GROUP BY INSPECTIONTYPEGROUP.INSPECTIONTYPEGROUP_DESC,
UNITNAME.UNITNAME_DESC
PIVOT "Qtr" & Format([NEXT_INSPECTION_DATE],"q") In
("Qtr1","Qtr2","Qtr3","Qtr4");



I want to be able to feed the year which I have typed in WHERE as
Format([NEXT_INSPECTION_DATE],"yyyy"))=2006 and the UnitName which I have
typed in WHERE as UNITNAME.UNITNAME_DESC)="45"
I want to be able to control the inputs through a combo box on a form.

I also have the WHERE statement modified in another queries to track
whether or not the inspection was completed. Here is the WHERE statement
for it with everything else being the same:


WHERE (((Format([NEXT_INSPECTION_DATE],"yyyy"))=2006) AND
((UNITNAME.UNITNAME_DESC)="45") AND ((INTERVAL.COMPLETED) Is Null Or
(INTERVAL.COMPLETED)=2))


It works fine except of course for not being able to control them through a
combo box on a form. I know I can add Parameters to the Query such as
[inspection data] , then add [inspection date] to the criteria for that
field, then type them in manual and it works, but like I said, I want to be
able to control it from a combo box.

Last question (I think) : Since I have 2 separate queries to count up the
inspections that are completed and one with just the total scheduled (both
quarterly), is there any *easy* way to make 1 report that would have a
count (by year and broken down quarterly) of all the inspections that were
completed and the total scheduled? I would want the output to just have the
quarters listed across the top and have the type of inspection for the row
header with the totals for that type of inspection broken out to total and
completed.

If any more information is needed such as table structure and what not,
just let me know. In addition to my main problems, if anyone has any
pointers maybe streamline this or let me know better practices to perform
these operations, input is greatly appreciated. Thanks.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Form reference parameters

1. Use the proper functions: Year() instead of Format().
2. Use form references:

PARAMETERS Forms!MyFormName!cboInspecDate Short,
Forms!MyFormName!cboUnitName Text;
SELECT...
FROM ...
WHERE Year([NEXT_INSPECTION_DATE])=Forms!MyFormName!cboInspecDate AND
UNITNAME.UNITNAME_DESC=Forms!MyFormName!cboUnitName
.... etc. ...

Substitute your form's name for MyFormName and the appropriate Control
names for cboInspecDate and cboUnitName.

Re the Year(): It is better to use the BETWEEN predicate than to apply
a function to a column value, 'cuz the function causes the query to scan
the whole table in order to evaluate the function expression. Using the
BETWEEN predicate means that table indexes can be used & the run time of
the query shortened:

NEXT_INSPECTION_DATE BETWEEN
DateSerial(Year(Forms!MyFormName!cboInspecDate),1,1) AND
DateSerial(Year(Forms!MyFormName!cboInspecDate),12,31)

On the report:

You might be able to use a UNION query of the scheduled and the
completed & then use the UNION query as a data source for a GROUPing
query (as long as both queries have the same columns & data types):

SELECT grouping, <other columns>
FROM (the scheduled cross-tab query
SELECT "Scheduled" As grouping ,....
... etc. ...
UNION ALL
the completed cross-tab query
SELECT "Completed" As grouping, ....

) AS U

You'll get rows like this:

grouping InspectionTypeGroup_desc UnitName_desc
--------- ------------------------- --------------
Scheduled xxxx xxxxx
Completed xxxxx xxxxxx
Scheduled xxxxx xxxxxx

that can be used by the Report to group by the InspectionTypeGroup_desc
& sort by the "grouping" column, or some other combination.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFf18oechKqOuFEgEQKTQgCfXt8M/JpcVLTNgYRP+6Mni+3TVGUAn31Z
Qq1OFP+FpZ+iIwPH7nehceFz
=7gy9
-----END PGP SIGNATURE-----

The following is my SQL from my crosstab query:

TRANSFORM Count(INTERVAL.NEXT_INSPECTION_DATE) AS [The Value]
SELECT INSPECTIONTYPEGROUP.INSPECTIONTYPEGROUP_DESC,
UNITNAME.UNITNAME_DESC, Count(INTERVAL.NEXT_INSPECTION_DATE) AS [Total Of
NEXT_INSPECTION_DATE]
FROM UNITNAME INNER JOIN ((INSPECTIONTYPEGROUP INNER JOIN INSPECTIONTYPE ON
INSPECTIONTYPEGROUP.INSPECTIONTYPEGROUP_ID =
INSPECTIONTYPE.INSPECTIONTYPEGROUP_ID) INNER JOIN (ASSET INNER JOIN
INTERVAL ON ASSET.ASSET_ID = INTERVAL.ASSET_ID) ON
INSPECTIONTYPE.INSPECTIONTYPE_ID = INTERVAL.INSPECTIONTYPE_ID) ON
UNITNAME.UNITNAME_ID = ASSET.UNITNAME_ID
WHERE (((Format([NEXT_INSPECTION_DATE],"yyyy"))=2006) AND
((UNITNAME.UNITNAME_DESC)="45"))
GROUP BY INSPECTIONTYPEGROUP.INSPECTIONTYPEGROUP_DESC,
UNITNAME.UNITNAME_DESC
PIVOT "Qtr" & Format([NEXT_INSPECTION_DATE],"q") In
("Qtr1","Qtr2","Qtr3","Qtr4");



I want to be able to feed the year which I have typed in WHERE as
Format([NEXT_INSPECTION_DATE],"yyyy"))=2006 and the UnitName which I have
typed in WHERE as UNITNAME.UNITNAME_DESC)="45"
I want to be able to control the inputs through a combo box on a form.

I also have the WHERE statement modified in another queries to track
whether or not the inspection was completed. Here is the WHERE statement
for it with everything else being the same:


WHERE (((Format([NEXT_INSPECTION_DATE],"yyyy"))=2006) AND
((UNITNAME.UNITNAME_DESC)="45") AND ((INTERVAL.COMPLETED) Is Null Or
(INTERVAL.COMPLETED)=2))


It works fine except of course for not being able to control them through a
combo box on a form. I know I can add Parameters to the Query such as
[inspection data] , then add [inspection date] to the criteria for that
field, then type them in manual and it works, but like I said, I want to be
able to control it from a combo box.

Last question (I think) : Since I have 2 separate queries to count up the
inspections that are completed and one with just the total scheduled (both
quarterly), is there any *easy* way to make 1 report that would have a
count (by year and broken down quarterly) of all the inspections that were
completed and the total scheduled? I would want the output to just have the
quarters listed across the top and have the type of inspection for the row
header with the totals for that type of inspection broken out to total and
completed.

If any more information is needed such as table structure and what not,
just let me know. In addition to my main problems, if anyone has any
pointers maybe streamline this or let me know better practices to perform
these operations, input is greatly appreciated. Thanks.