Crosstab Query and Specific Date Ranges

R

Renee

I seriously need some help here. I'm fairly good with queries, but I
struggle with crosstab queries. I have a crosstab query in which my columns
are dates and my rows are names I have tried typing in the criteria line
"Between [Start Date] And [End Date]" in the date field to get data within a
specific period (i.e., all the columns would be between 09/01/2009 and
09/30/2009), but when I run it, I get an error message that says "The
Microsoft Office Access Database engine does not recognize '[Start Date]' as
a valid field name or expression."

Here is my SQL statement:
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

Can someone help me right a parameter comment that will give me the pop up
boxes that asks for the start date and end date based on itInspectDate?

I really appreciate anyone's help on this.

Thank you.
 
D

Duane Hookom

You must set the data type of query parameter when creating crosstabs. You
can enter them into the Query->Parameters. They will result in something like
the below. Note, IMO, parameter prompts are never appropriate user interface,
consider using controls on forms for all user interaction.

PARAMETERS [Forms]!frmDates![txtStartDate] DateTime,
[Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
WHERE [DateField] BETWEEN [Forms]!frmDates![txtStartDate] And
[Forms]!frmDates![txtEndDate]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");
 
R

Renee

I guess I'm not sure what you are suggesting. I am trying to keep a running
tally of all the types of inspections our inspectors do on a daily basis. I
want to print a report based on a crosstab query, but I want to be able to
select specific date ranges to show in the report for myself to use or anyone
else to use through parameter prompts in the crosstab query. Since the data
is broken down by date in columns and the types of inspections and inspector
names are broken down by rows, I want the user to get a parameter prompt to
enter the beginning of the period of data they want to look at and then a
parameter prompt to enter the ending of the period of data they want to look
at, so they can look at a specifc week, a specific month, etc. in a report.

I need the parameter prompts, otherwise, two years down the road, my users
will get two years of columns of data to look at when they may only want to
see one week.

At this point, the SQL parameter statement suggestion you provided does not
work and I'm at a loss as to what to do since the tried and true parameter
prompt "Between [Start Date] And [End Date]" does not appear to work in
crosstab queries.

Duane Hookom said:
You must set the data type of query parameter when creating crosstabs. You
can enter them into the Query->Parameters. They will result in something like
the below. Note, IMO, parameter prompts are never appropriate user interface,
consider using controls on forms for all user interaction.

PARAMETERS [Forms]!frmDates![txtStartDate] DateTime,
[Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
WHERE [DateField] BETWEEN [Forms]!frmDates![txtStartDate] And
[Forms]!frmDates![txtEndDate]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

--
Duane Hookom
Microsoft Access MVP


Renee said:
I seriously need some help here. I'm fairly good with queries, but I
struggle with crosstab queries. I have a crosstab query in which my columns
are dates and my rows are names I have tried typing in the criteria line
"Between [Start Date] And [End Date]" in the date field to get data within a
specific period (i.e., all the columns would be between 09/01/2009 and
09/30/2009), but when I run it, I get an error message that says "The
Microsoft Office Access Database engine does not recognize '[Start Date]' as
a valid field name or expression."

Here is my SQL statement:
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

Can someone help me right a parameter comment that will give me the pop up
boxes that asks for the start date and end date based on itInspectDate?

I really appreciate anyone's help on this.

Thank you.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Crosstab queries are very, very fussy about data objects that are not fields
from tables.
As Duane said you must declare them. Use a form and the first line of SQL
that was posted or this first line --
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
--
Build a little, test a little.


Renee said:
I guess I'm not sure what you are suggesting. I am trying to keep a running
tally of all the types of inspections our inspectors do on a daily basis. I
want to print a report based on a crosstab query, but I want to be able to
select specific date ranges to show in the report for myself to use or anyone
else to use through parameter prompts in the crosstab query. Since the data
is broken down by date in columns and the types of inspections and inspector
names are broken down by rows, I want the user to get a parameter prompt to
enter the beginning of the period of data they want to look at and then a
parameter prompt to enter the ending of the period of data they want to look
at, so they can look at a specifc week, a specific month, etc. in a report.

I need the parameter prompts, otherwise, two years down the road, my users
will get two years of columns of data to look at when they may only want to
see one week.

At this point, the SQL parameter statement suggestion you provided does not
work and I'm at a loss as to what to do since the tried and true parameter
prompt "Between [Start Date] And [End Date]" does not appear to work in
crosstab queries.

Duane Hookom said:
You must set the data type of query parameter when creating crosstabs. You
can enter them into the Query->Parameters. They will result in something like
the below. Note, IMO, parameter prompts are never appropriate user interface,
consider using controls on forms for all user interaction.

PARAMETERS [Forms]!frmDates![txtStartDate] DateTime,
[Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
WHERE [DateField] BETWEEN [Forms]!frmDates![txtStartDate] And
[Forms]!frmDates![txtEndDate]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

--
Duane Hookom
Microsoft Access MVP


Renee said:
I seriously need some help here. I'm fairly good with queries, but I
struggle with crosstab queries. I have a crosstab query in which my columns
are dates and my rows are names I have tried typing in the criteria line
"Between [Start Date] And [End Date]" in the date field to get data within a
specific period (i.e., all the columns would be between 09/01/2009 and
09/30/2009), but when I run it, I get an error message that says "The
Microsoft Office Access Database engine does not recognize '[Start Date]' as
a valid field name or expression."

Here is my SQL statement:
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

Can someone help me right a parameter comment that will give me the pop up
boxes that asks for the start date and end date based on itInspectDate?

I really appreciate anyone's help on this.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Renee, what I am suggesting is that you abandon the [Start Date] and [End
Date] in your query criteria. Check for my reasoning at
http://www.tek-tips.com/faqs.cfm?fid=6763.

Best practice is create a form with two text boxes for a user to enter both
dates on one screen with the possibility of using a calendar/date picker,
provide defaults, and/or check integrity.

Since you are using date intervals (single dates) as column headings, you
should consider the solution for crosstab reporting at
http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to easily
change this to daily rather than monthly.
--
Duane Hookom
Microsoft Access MVP


Renee said:
I guess I'm not sure what you are suggesting. I am trying to keep a running
tally of all the types of inspections our inspectors do on a daily basis. I
want to print a report based on a crosstab query, but I want to be able to
select specific date ranges to show in the report for myself to use or anyone
else to use through parameter prompts in the crosstab query. Since the data
is broken down by date in columns and the types of inspections and inspector
names are broken down by rows, I want the user to get a parameter prompt to
enter the beginning of the period of data they want to look at and then a
parameter prompt to enter the ending of the period of data they want to look
at, so they can look at a specifc week, a specific month, etc. in a report.

I need the parameter prompts, otherwise, two years down the road, my users
will get two years of columns of data to look at when they may only want to
see one week.

At this point, the SQL parameter statement suggestion you provided does not
work and I'm at a loss as to what to do since the tried and true parameter
prompt "Between [Start Date] And [End Date]" does not appear to work in
crosstab queries.

Duane Hookom said:
You must set the data type of query parameter when creating crosstabs. You
can enter them into the Query->Parameters. They will result in something like
the below. Note, IMO, parameter prompts are never appropriate user interface,
consider using controls on forms for all user interaction.

PARAMETERS [Forms]!frmDates![txtStartDate] DateTime,
[Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
WHERE [DateField] BETWEEN [Forms]!frmDates![txtStartDate] And
[Forms]!frmDates![txtEndDate]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

--
Duane Hookom
Microsoft Access MVP


Renee said:
I seriously need some help here. I'm fairly good with queries, but I
struggle with crosstab queries. I have a crosstab query in which my columns
are dates and my rows are names I have tried typing in the criteria line
"Between [Start Date] And [End Date]" in the date field to get data within a
specific period (i.e., all the columns would be between 09/01/2009 and
09/30/2009), but when I run it, I get an error message that says "The
Microsoft Office Access Database engine does not recognize '[Start Date]' as
a valid field name or expression."

Here is my SQL statement:
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

Can someone help me right a parameter comment that will give me the pop up
boxes that asks for the start date and end date based on itInspectDate?

I really appreciate anyone's help on this.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
R

Renee

Can I create a form with two text boxes for the purposes of quering dates for
a crosstab query without the use of VBA language?

Duane Hookom said:
Renee, what I am suggesting is that you abandon the [Start Date] and [End
Date] in your query criteria. Check for my reasoning at
http://www.tek-tips.com/faqs.cfm?fid=6763.

Best practice is create a form with two text boxes for a user to enter both
dates on one screen with the possibility of using a calendar/date picker,
provide defaults, and/or check integrity.

Since you are using date intervals (single dates) as column headings, you
should consider the solution for crosstab reporting at
http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to easily
change this to daily rather than monthly.
--
Duane Hookom
Microsoft Access MVP


Renee said:
I guess I'm not sure what you are suggesting. I am trying to keep a running
tally of all the types of inspections our inspectors do on a daily basis. I
want to print a report based on a crosstab query, but I want to be able to
select specific date ranges to show in the report for myself to use or anyone
else to use through parameter prompts in the crosstab query. Since the data
is broken down by date in columns and the types of inspections and inspector
names are broken down by rows, I want the user to get a parameter prompt to
enter the beginning of the period of data they want to look at and then a
parameter prompt to enter the ending of the period of data they want to look
at, so they can look at a specifc week, a specific month, etc. in a report.

I need the parameter prompts, otherwise, two years down the road, my users
will get two years of columns of data to look at when they may only want to
see one week.

At this point, the SQL parameter statement suggestion you provided does not
work and I'm at a loss as to what to do since the tried and true parameter
prompt "Between [Start Date] And [End Date]" does not appear to work in
crosstab queries.

Duane Hookom said:
You must set the data type of query parameter when creating crosstabs. You
can enter them into the Query->Parameters. They will result in something like
the below. Note, IMO, parameter prompts are never appropriate user interface,
consider using controls on forms for all user interaction.

PARAMETERS [Forms]!frmDates![txtStartDate] DateTime,
[Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
WHERE [DateField] BETWEEN [Forms]!frmDates![txtStartDate] And
[Forms]!frmDates![txtEndDate]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

--
Duane Hookom
Microsoft Access MVP


:

I seriously need some help here. I'm fairly good with queries, but I
struggle with crosstab queries. I have a crosstab query in which my columns
are dates and my rows are names I have tried typing in the criteria line
"Between [Start Date] And [End Date]" in the date field to get data within a
specific period (i.e., all the columns would be between 09/01/2009 and
09/30/2009), but when I run it, I get an error message that says "The
Microsoft Office Access Database engine does not recognize '[Start Date]' as
a valid field name or expression."

Here is my SQL statement:
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

Can someone help me right a parameter comment that will give me the pop up
boxes that asks for the start date and end date based on itInspectDate?

I really appreciate anyone's help on this.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Yes, there would be no VBA required. Your query criteria would replace:
[Enter Start Date]
with something like:
[Forms]![frmDates]![txtStartDate]

You might use some type of date selection calendar, defaults values, etc
which would not be available with a parameter prompt.

--
Duane Hookom
Microsoft Access MVP


Renee said:
Can I create a form with two text boxes for the purposes of quering dates for
a crosstab query without the use of VBA language?

Duane Hookom said:
Renee, what I am suggesting is that you abandon the [Start Date] and [End
Date] in your query criteria. Check for my reasoning at
http://www.tek-tips.com/faqs.cfm?fid=6763.

Best practice is create a form with two text boxes for a user to enter both
dates on one screen with the possibility of using a calendar/date picker,
provide defaults, and/or check integrity.

Since you are using date intervals (single dates) as column headings, you
should consider the solution for crosstab reporting at
http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to easily
change this to daily rather than monthly.
--
Duane Hookom
Microsoft Access MVP


Renee said:
I guess I'm not sure what you are suggesting. I am trying to keep a running
tally of all the types of inspections our inspectors do on a daily basis. I
want to print a report based on a crosstab query, but I want to be able to
select specific date ranges to show in the report for myself to use or anyone
else to use through parameter prompts in the crosstab query. Since the data
is broken down by date in columns and the types of inspections and inspector
names are broken down by rows, I want the user to get a parameter prompt to
enter the beginning of the period of data they want to look at and then a
parameter prompt to enter the ending of the period of data they want to look
at, so they can look at a specifc week, a specific month, etc. in a report.

I need the parameter prompts, otherwise, two years down the road, my users
will get two years of columns of data to look at when they may only want to
see one week.

At this point, the SQL parameter statement suggestion you provided does not
work and I'm at a loss as to what to do since the tried and true parameter
prompt "Between [Start Date] And [End Date]" does not appear to work in
crosstab queries.

:

You must set the data type of query parameter when creating crosstabs. You
can enter them into the Query->Parameters. They will result in something like
the below. Note, IMO, parameter prompts are never appropriate user interface,
consider using controls on forms for all user interaction.

PARAMETERS [Forms]!frmDates![txtStartDate] DateTime,
[Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
WHERE [DateField] BETWEEN [Forms]!frmDates![txtStartDate] And
[Forms]!frmDates![txtEndDate]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

--
Duane Hookom
Microsoft Access MVP


:

I seriously need some help here. I'm fairly good with queries, but I
struggle with crosstab queries. I have a crosstab query in which my columns
are dates and my rows are names I have tried typing in the criteria line
"Between [Start Date] And [End Date]" in the date field to get data within a
specific period (i.e., all the columns would be between 09/01/2009 and
09/30/2009), but when I run it, I get an error message that says "The
Microsoft Office Access Database engine does not recognize '[Start Date]' as
a valid field name or expression."

Here is my SQL statement:
TRANSFORM Sum([All Inspections by Insp Name].itInspectNo) AS SumOfitInspectNo
SELECT [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm, Sum([All Inspections by Insp Name].itInspectNo) AS
[Total Of itInspectNo]
FROM [All Inspections by Insp Name]
GROUP BY [All Inspections by Insp Name].itInspectType, [All Inspections by
Insp Name].inInspectorNm
PIVOT Format([itInspectDate],"Short Date");

Can someone help me right a parameter comment that will give me the pop up
boxes that asks for the start date and end date based on itInspectDate?

I really appreciate anyone's help on this.

Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query by date ranges 3
Yes / No query problem 4
Limit data in field to within 90 days of another field... 4
Adding Calcs to a Crosstab? Or Form? 4
Query not returning nulls 2
crosstab query parameters for dates 1
crosstab query 2
Crosstab query question 2

Top