Why is logic not logical in Access?

[JediYodaNT]

Hey folks. I could really use your help again.

So, I created a query that lists all the transactions entered with a certain entry date, and as a criteria used "Between [Enter Start Date] And [Enter End Date]" which worked like a charm.

Next, I wanted to have a crosstab query that would run from the results of the 1st query, but I keep hitting a brick wall. Every time I try to run it, Access hangs up on the [Enter Start Date] as an unrecognizable item.

Any idea what I need to do to get this to work?

If I simply make a crosstab query off of the complete entries table, the dates along the top are out of order because of the formatting, and if I add a "Between [Enter Start Date] And [Enter End Date]" the crosstab query gives me the same wonderful error message.

 

[ajetrumpet]

Hey folks. I could really use your help again.

So, I created a query that lists all the transactions entered with a certain entry date, and as a criteria used "Between [Enter Start Date] And [Enter End Date]" which worked like a charm.

Next, I wanted to have a crosstab query that would run from the results of the 1st query, but I keep hitting a brick wall. Every time I try to run it, Access hangs up on the [Enter Start Date] as an unrecognizable item.

Any idea what I need to do to get this to work?

If I simply make a crosstab query off of the complete entries table, the dates along the top are out of order because of the formatting, and if I add a "Between [Enter Start Date] And [Enter End Date]" the crosstab query gives me the same wonderful error message.

formatted SQL postings would help greatly

 

[dbDamo]

Unless you declare parameters in the parameter dialog box, then they will not work with crosstab queries.

 

[The_Doc_Man]

The problem is that you don't understand what you asked Access to do.

When you run a query against a query, it is the same in effect as directly opening the inner query. If the inner query needs parameters, it needs them whether you opened that query directly or from another query.

Therefore, I'm not surprised that it asked you for something.

 

[JediYodaNT]

The problem is...it didn't ask me for anything. When I rn the 1st query in it's own, as I wanted, two prompt windows come up asking that the user supply a start date and an end date, which is what I wanted. Now, the next process needed to be a crosstab query that would provide my breakdown counts from the results of the 1st query. I know that each query works fine on their own, it's only when I try to create a limitation to the crosstab query that I hit a roadblock.

 

[dbDamo]

Use the parameter dialog box when in design view of the crosstab query to declare the parameters.

In your case it would be:-

Parameter-------------Data Type
[Enter Start Date]-----Date/Time
[Enter End Date]------Date/Time


Your criteria would then be:-

BETWEEN [Enter Start Date] AND [Enter End Date]

 

[JediYodaNT]

Here's the crosstab query without any filtering. What would I need to add in order to get receive prompts asking for a start date and end date?

TRANSFORM Count(Entries.ID) AS CountOfID
SELECT Entries.Process, Count(Entries.ID) AS [Total Of ID]
FROM Entries
GROUP BY Entries.Process
ORDER BY Format([InputDate],"Short Date")
PIVOT Format([InputDate],"Short Date");

 

[Banana]

Give this a try.

PARAMETERS InputDate DATE;
TRANSFORM Count(Entries.ID) AS CountOfID
SELECT Entries.Process, Count(Entries.ID) AS [Total Of ID]
FROM Entries
GROUP BY Entries.Process
ORDER BY [InputDate]
PIVOT [InputDate];

 

[JediYodaNT]

Thanks Banana.
So, I gave it a try, and as I was hoping for, I got the prompt to enter an Input Date, but the values it returned were way off. Any idea what might have caused that?

dbDamo, is this were I'd enter the Parameter fields like you had mentioned? I was looking for a specific box while in the design view, but did not find one.

 

[Banana]

In design view, you have to right-click in query builder and choose "Parameters" to get that box dbDamo spoke of. You should see one parameter already there as I've given you the SQL code that replicate just that.

You will have to explain more about what was wrong with the values. Maybe a sample of what you actually had and what you expected. You could also check the underlying query and verify the result there. (e.g. strip off the TRANSFORM and PIVOT clause and see if the query returns same results.) More often than not, the problem originates in the query, not in how it's transformed/pivoted.

 

[JediYodaNT]

It seems as if the values I'm getting are the grand total from the entier table, instead of the values for that specific date. I'm not really sure why I'm getting counts that high.

 

[Banana]

I think in that case, you need to group by dates as well. As I said, a sample data (even bogus one that's a good representation of actual one) would help us provide specific suggestions.