Crosstab query (1 Viewer)

[U226RM2]

Hi Everyone

I am trying to create a crosstab query that is based on another query with a date range in its criteria. Crosstab doesn't seem to like it but I really need to be able to use data from a particular date. Any ideas?

 

[Rabbie]

That shouldn't be a problem. I nearly always use a query as source for a crosstab query without problems. A Select query can be used in stead of a table as a data source in Access. We would need more information about what you are trying to do to be able to help you. Also we would need to see what the underlying query looked like and what the crosstab looked like and what your error message was.

Help us to help you.

 

[U226RM2]

Hi Rabbie
This is the query my xtab is based on:

SELECT [Master Table deduplicated].[Lab Nos], [Master Table deduplicated].Source, [Master Table deduplicated].[Infection acquired], [Master Table deduplicated].[Source of bacteraemia], [Master Table deduplicated].Specialty, [Master Table deduplicated].Directorate, [Master Table deduplicated].[Date collected]
FROM [Master Table deduplicated]
GROUP BY [Master Table deduplicated].[Lab Nos], [Master Table deduplicated].Source, [Master Table deduplicated].[Infection acquired], [Master Table deduplicated].[Source of bacteraemia], [Master Table deduplicated].Specialty, [Master Table deduplicated].Directorate, [Master Table deduplicated].[Date collected]
HAVING ((([Master Table deduplicated].[Date collected]) Between [start date] And [end date]));

This is the xtab query:

TRANSFORM Sum([List of HA episodes].[Lab Nos]) AS [SumOfLab Nos]
SELECT [List of HA episodes].[Source of bacteraemia]
FROM [List of HA episodes]
GROUP BY [List of HA episodes].[Source of bacteraemia]
PIVOT [List of HA episodes].Directorate;

and this is the error message:

The Microsoft Jet database engine does not recognise '[start date]' as a valid field name or expression.

Thanks Rabbie, I really appreciate any help you can give me.

 

[Rabbie]

Firstly run your base query and check that is working correctly. It will probably prompt you for values for [Start date] and [End date] If that works OK then try substituting actual values for [Start date] and [End date] in the query. remember they will need to be enclosed with # signs. (#mm/dd/yyyy#).

Then after saving the query try running the crosstab query again.

 

[stopher]

Are you entering Start Date and End Date as parameters? If so, take a look here. See the section on "Handle Parameters"

Edit:
Sorry, just to qualify my post. What Allen Browne is saying is that there is a problem using parameters in crosstabs. But the reason Allen Browne was put on this earth is that he always has a solution (see the link).

hth
Chris

 

[U226RM2]

The original query works fine. I have substituted [start date] and [end date] with actual dates and now the message is "Data type mismatch in criteria expression"

 

[stopher]

The original query works fine. I have substituted [start date] and [end date] with actual dates and now the message is "Data type mismatch in criteria expression"

Could you clarify. Are you saying that you select query gives a Data type mismatch error when you replace Start Date and End Date with actual date values (enclosed in ##)?

Or are you saying the select query works fine when you do this but the cross tab is throwing the error?

Chris

 

[U226RM2]

Yes, the select query works fine but the crosstab gives the error.

 

[stopher]

I ran your querys and they worked fine for me. Can you post your d/b?

 

[U226RM2]

How annoying (did you like the way I managed not to swear?)! Sorry, I can't post my db as it has sensitive data on it. I guess I'll have to forget all about this particular crosstab.

 

[stopher]

I've attached my test. See if you can spot the difference.

Chris