Crosstab lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

A rather simple question I think:

I want a crosstab query to use values typed into a form by a user. I think
it is possible but can't remember how to set it up.

Any help greatly appreciated. I have looked through the past questions and
can't find anything.

Thanks
 
You must set the query parameter data types. Select Query->Parameters and
enter something like:
Forms!frmDates!txtStartDate Date/Time
 
Hello

Thanks very much for your help.

I have used the parameters as you said but can't seem to get it working.
When I add the form lookups into the query it gives a message saying the
query is too complex to be calculated. Here is my SQL if thats any help.

PARAMETERS [Forms]![frmReportMenu]![cmdName] DateTime,
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] Text ( 255 );
TRANSFORM Sum(qryWorkingHoursName.TotalminutesWorked) AS
SumOfTotalminutesWorked
SELECT qryWorkingHoursName.txtName
FROM qryWorkingHoursName
WHERE (((qryWorkingHoursName.dtmDate) Between
[Forms]![frmReportMenu]![txtstartdate] And
[Forms]![frmReportMenu]![txtenddate]))
GROUP BY qryWorkingHoursName.txtName
PIVOT qryWorkingHoursName.dtmDate;

I am trying to get working hours for several different team members by date.
qryworkinghoursname just sums up the number of minutes worked AM and PM.

Thanks in advance for any help you can offer.

F Jones.




Duane Hookom said:
You must set the query parameter data types. Select Query->Parameters and
enter something like:
Forms!frmDates!txtStartDate Date/Time
Click to expand...
 
Hello again

I think I have resoved it. I had a similar problem before and remember that
I ended up having to append the data to the table and then using that instead
of trying to work it out in the query each time.

Thanks for all your help.

F Jones

F Jones said:
Hello

Thanks very much for your help.

I have used the parameters as you said but can't seem to get it working.
When I add the form lookups into the query it gives a message saying the
query is too complex to be calculated. Here is my SQL if thats any help.

PARAMETERS [Forms]![frmReportMenu]![cmdName] DateTime,
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] Text ( 255 );
TRANSFORM Sum(qryWorkingHoursName.TotalminutesWorked) AS
SumOfTotalminutesWorked
SELECT qryWorkingHoursName.txtName
FROM qryWorkingHoursName
WHERE (((qryWorkingHoursName.dtmDate) Between
[Forms]![frmReportMenu]![txtstartdate] And
[Forms]![frmReportMenu]![txtenddate]))
GROUP BY qryWorkingHoursName.txtName
PIVOT qryWorkingHoursName.dtmDate;

I am trying to get working hours for several different team members by date.
qryworkinghoursname just sums up the number of minutes worked AM and PM.

Thanks in advance for any help you can offer.

F Jones.
Click to expand...
 
I think you have your data types messed up in your parameters.
This:
PARAMETERS [Forms]![frmReportMenu]![cmdName] DateTime,
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] Text ( 255 );
Should be:
PARAMETERS [Forms]![frmReportMenu]![cmdName] Text ( 255 ),
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] DateTime;

--
Duane Hookom
MS Access MVP


F Jones said:
Hello again

I think I have resoved it. I had a similar problem before and remember that
I ended up having to append the data to the table and then using that instead
of trying to work it out in the query each time.

Thanks for all your help.

F Jones

F Jones said:
Hello

Thanks very much for your help.

I have used the parameters as you said but can't seem to get it working.
When I add the form lookups into the query it gives a message saying the
query is too complex to be calculated. Here is my SQL if thats any help.

PARAMETERS [Forms]![frmReportMenu]![cmdName] DateTime,
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] Text ( 255 );
TRANSFORM Sum(qryWorkingHoursName.TotalminutesWorked) AS
SumOfTotalminutesWorked
SELECT qryWorkingHoursName.txtName
FROM qryWorkingHoursName
WHERE (((qryWorkingHoursName.dtmDate) Between
[Forms]![frmReportMenu]![txtstartdate] And
[Forms]![frmReportMenu]![txtenddate]))
GROUP BY qryWorkingHoursName.txtName
PIVOT qryWorkingHoursName.dtmDate;

I am trying to get working hours for several different team members by date.
qryworkinghoursname just sums up the number of minutes worked AM and PM.

Thanks in advance for any help you can offer.

F Jones.




Duane Hookom said:
You must set the query parameter data types. Select Query->Parameters and
enter something like:
Forms!frmDates!txtStartDate Date/Time

--
Duane Hookom
MS Access MVP
--

Hello

A rather simple question I think:

I want a crosstab query to use values typed into a form by a user. I think
it is possible but can't remember how to set it up.

Any help greatly appreciated. I have looked through the past questions and
can't find anything.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Yes I did spot that during my investigations! the query worked ok with one
criteria but not with a between statement so I have appended the worked hours
to the table and changed the query to use these and it works ok now.

Thanks for your help. Its really speeds up the whole process for dummies
like me.

Fran

Duane Hookom said:
I think you have your data types messed up in your parameters.
This:
PARAMETERS [Forms]![frmReportMenu]![cmdName] DateTime,
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] Text ( 255 );
Should be:
PARAMETERS [Forms]![frmReportMenu]![cmdName] Text ( 255 ),
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] DateTime;

--
Duane Hookom
MS Access MVP


F Jones said:
Hello again

I think I have resoved it. I had a similar problem before and remember that
I ended up having to append the data to the table and then using that instead
of trying to work it out in the query each time.

Thanks for all your help.

F Jones

F Jones said:
Hello

Thanks very much for your help.

I have used the parameters as you said but can't seem to get it working.
When I add the form lookups into the query it gives a message saying the
query is too complex to be calculated. Here is my SQL if thats any help.

PARAMETERS [Forms]![frmReportMenu]![cmdName] DateTime,
[Forms]![frmReportMenu]![txtstartdate] DateTime,
[Forms]![frmReportMenu]![txtenddate] Text ( 255 );
TRANSFORM Sum(qryWorkingHoursName.TotalminutesWorked) AS
SumOfTotalminutesWorked
SELECT qryWorkingHoursName.txtName
FROM qryWorkingHoursName
WHERE (((qryWorkingHoursName.dtmDate) Between
[Forms]![frmReportMenu]![txtstartdate] And
[Forms]![frmReportMenu]![txtenddate]))
GROUP BY qryWorkingHoursName.txtName
PIVOT qryWorkingHoursName.dtmDate;

I am trying to get working hours for several different team members by date.
qryworkinghoursname just sums up the number of minutes worked AM and PM.

Thanks in advance for any help you can offer.

F Jones.




:

You must set the query parameter data types. Select Query->Parameters and
enter something like:
Forms!frmDates!txtStartDate Date/Time

--
Duane Hookom
MS Access MVP
--

Hello

A rather simple question I think:

I want a crosstab query to use values typed into a form by a user. I think
it is possible but can't remember how to set it up.

Any help greatly appreciated. I have looked through the past questions and
can't find anything.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top