Access query in access report

[thtadthtshldntbe]

hello

I created a crosstab query that more or less works fine.

Due to how the databases i have to work with are set up, I have been trying to move some of my custom queries to access reports because it will automatically do a lot of the formatting and such that I would otherwise have to do manually with the query results over in word or excel.

In order to get a crosstab query to work in an access report, from what I understand I need to specify all column headings, at least that is what random googling and access error messages are telling me (note I am also trying to teach myself to do more and more of this in sql, because if I get my project approved, I get a real sql database for my emr records, as opposed to the situation I deal with now, anyway).

The table it pulls from LDExport

It uses the fields
PT-AGE
Method of Delivery Baby A
Infant Delivery Date Baby A DataTime


This is the query in SQL

PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Count(LDExport.[Method of Delivery Baby A]) AS [CountOfMethod of Delivery Baby A]
SELECT IIf(([PT-AGE]>=8 And [PT-AGE]<15),"Ages 8-14",
IIf(([PT-AGE]>=15 And [PT-AGE]<20),"Ages 15-20",
IIf(([PT-AGE]>=20 And [PT-AGE]<25),"Ages 20-24",
IIf(([PT-AGE]>=25 And [PT-AGE]<30),"Ages 25-29",
IIf(([PT-AGE]>=30 And [PT-AGE]<35),"Ages 30-34",
IIf(([PT-AGE]>=35 And [PT-AGE]<40),"Ages 35-39",
IIf(([PT-AGE]>=40 And [PT-AGE]<45),"Ages 40-44",
IIf(([PT-AGE]>=45 And [PT-AGE]<65),"Ages 45-64","unknown"))))))))
AS [Age Group]
FROM LDExport
WHERE (((LDExport.[Infant Delivery Date Baby A DateTime])>=[start date] And (LDExport.[Infant Delivery Date Baby A DateTime])<[end date]))
GROUP BY LDExport.[PT-AGE], LDExport.[Infant Delivery Date Baby A DateTime], IIf(([PT-AGE]>=8 And [PT-AGE]<15),"Ages 8-14",IIf(([PT-AGE]>=15 And [PT-AGE]<20),"Ages 15-20",IIf(([PT-AGE]>=20 And [PT-AGE]<25),"Ages 20-24",IIf(([PT-AGE]>=25 And [PT-AGE]<30),"Ages 25-29",IIf(([PT-AGE]>=30 And [PT-AGE]<35),"Ages 30-34",IIf(([PT-AGE]>=35 And [PT-AGE]<40),"Ages 35-39",IIf(([PT-AGE]>=40 And [PT-AGE]<45),"Ages 40-44",IIf(([PT-AGE]>=45 And [PT-AGE]<65),"Ages 45-64","unknown"))))))))
PIVOT LDExport.[Method of Delivery Baby A] In ("C/S","Vag");

I know that its kind of jumbled up. I am still at the stage where I need to build complicated queries in the access grid

The last bit highlighted in red from what I understand, should make this work in an access report.

but when I run it, i get a parameter error

I know this query works as a crosstab query however.

any help appreciated.

btw the results should be a crosstab that is basically

age category - total vag deliveries - total C/S deliveries

edit,

I should clarify, I have to use 2 queries to produce the crosstab as regular queries

 

[KB]

I don't see anything obviously wrong with your query. One suggestion would be to hardcode some dates in the WHERE clause to see if the dates are the culprit. Access can be funny with dates and parameters.


(((LDExport.[Infant Delivery Date Baby A DateTime])>=#2/1/2014# And (LDExport.[Infant Delivery Date Baby A DateTime])<#4/1/2014#))


One thing I would suggest would be to make your life easier and simplify your query.
Create an age range table and then join to that table instead of using IIFs

tblAgeRange
AgeStart AgeEnd AgeRangeName
8 15 "Ages 8 - 15"
16 20 "Ages 15 - 20"


this join would remove about half of your query and may make it easier to see the error.

SELECT tblAgeRange.AgeRangeName
FROM LDExport LEFT JOIN tblAgeRange ON LDExport.[PT-Age] BETWEEN tblAgeRange.AgeStart AND tblAgeRange.AgeEnd


If you are still stuck post the table structure in a CREATE TABLE statement.

 

[thtadthtshldntbe]

The table thing is a good idea, though I am not sure how to do it the way that you set it up.

If I set up as

Age AgeRangename
8 Ages 8-14
9 Ages 8-14
10 Ages 8-14
11 Ages 8-14
12 Ages 8-14
13 Ages 8-14


and so on, I would know how to make that work.

--------

I tried the set dates and then it starts asking me to input

method of Delivery.

What kills me is that it took 15 min to do this as a simple query.

 

[thtadthtshldntbe]

I redid the report from scratch sort of.

apparently, I can get it to work, IF I refer to my working crosstab query.

I thought the point of having that data source query in a report was to build the whole thing there.