Report/Crosstab-Query Problem 1

[rdgskier8]

Hello all,
I'm having a little trouble getting my queries to work for creating a graph in a report. If anyone can point me in the right direction, I'd really appreciate it. Here's my scenario:

I have a form that inputs dates for the data to be graphed (I can get this part to work). The query then lists all the entries, and my crosstab query counts up the entries and is used to display them in the graph (also works). My problem is, the form also has a checkbox that, when checked, needs to count up the entries in another table (for that date range) and subtract these from the the count of the first table. My problem is that I can't figure out a way to subtract them. A crosstab query won't let me use expressions like that. Do I have to use another select query to relate the two crosstab queries? Is that even possible?

A general answer of what kind of queries I need should be a good enough answer to get me rolling in the right direction. I hope someone can help!

Thanks in advance,
Ryan

 

[Crowley16]

have you considered subtracting the relevant data with a query, and the basing the cross tab on the results of that query?

--------------------
Procrastinate Now!

 

[rdgskier8]

I've tried that, but I don't think I can get around it because my WHERE statement includes an OR value (I'm looking at data range in 2005 and 2004, for example). Therefore, I need to keep the dates, which I can't do if I count the data in the first query... I have to split it up in the crosstab query.

 

[Crowley16]

why would that stop you?

keep the dates in the first query, and put the test for dates in there as well, so in your crosstab you would ONLY be rejigging the display of the data...

--------------------
Procrastinate Now!

 

[rdgskier8]

But I'm not subtracting values... I'm subtracting Count of one table from the Count of another. So if I put both fields in one query, they won't correlate. If I count them and subtract, that brings the query to one row, which eliminates the date data. What am I missing?

Thanks for your help, Crowley.

 

[rdgskier8]

Ahh I think I got it! Thanks again, Crowley.