Counting "True" Values in a Query

[Evon]

I just hope someone here is able to help with this. I have on a form a combo box with its list of values derived from a table. The values selected from the combo box are all stored in one field in another table.

I want to count all the like values so I can group the like values into different groups on a report. To do this, I have created a query including the table where the values are stored. I have been able to separate/filter the values into individual columns/fields inside the query. However, whenever I run the query I don't get actual constants but instead I get a -1 or 0 value in each column.

So far I have been able to count the values using the DCount function from within a report.

However, the challenge is that I am still not able to add any further grouping to the totals on the report. Whenever, I create a group heading on the report, the total count of all the values keeps coming up for each group instead of the quantity in that group.

Anyone interested can view a scaled down version of the db here

 

[Evon]

Thanks Pat, I don't play bridge, but I am starting to believe you know all the tricks. The Abs(Sum(YourField)) did not work but you motivated me to persist with the sum function instead of the count and I think I have finally got it.

There is an added trick I had to use though. When the True values are added they return a negative total on my report so I had to include a (*-1) to my expression to get it back to a positive display.

 

[Evon]

Trouble again! Now I want to print some labels. I now need to convert back the true values to the string names as displayed in the combo box.

I have been fiddling around with some string expressions in the query and not able to do it.

Help!

Pat my friend are you there?

 

[Evon]

In other words I have this expression in a query:
Expr3: [DietSelection]=1
This returns a value of True for the first row of the first column of a combo box. But how can I get the expression to return:
1.The third column in the first row
2.The Third column in the second row
3.The Third column in the third row
and so on? These are the cells that have the names I want to display on a report.

 

[Evon]

Or if I was able to convert the expression Expr3: [DietSelection]=1 to say If Expr1=1 then "Diabetic"

 

[Evon]

Eureka!!!

I got it, it is called the "Switch Function"

 

[Evon]

Thanks Pat, as I mentioned in the post before this that the "Switch() Function" actually solved my problem. However, I am going to look again at the Abs() Function.
I also found myself two new challenges:

1. I want to be able to make my database run for say 30 days after which it will become non-functional until I have it re-set.
Any ideas how this can be done.
2. I want to be able to export, via the web, any changes I make in the structure of a database. I need to do this as I write databases for clients at a distance and would like to be able to post these on a site where the structural changes can be download as updates to these databases without affecting the data in them.

Any ideas on these?

I should add that I have begun to read and research on my database skills (especially SQL) and wouldn't mind if I could be directed to some reading material or even an online training course at an advanced level.

 

[Evon]

Yes Pat, good answers. For shutting down the database I was thinking about setting a macro to count the the times the database is opened and then shut it down when the amount of times opened has been met. Or setting the macro to some 24 hrs. count.

I actually haven't looked into it as yet.

For the Switch() Fn, I use it in the query, not on the form and I haven't seen any problems.

 

[Evon]

Thanks again Pat, I tried the Abs fn and it worked fine. However, using the Switch() in the querry still allowed me to update the form, and the changes were reflected in the report like how I wanted to.

Still looking into how to limit the life of the database. The times opened would be good, since I could set a number which I think would be more than adequate. Also, the users would have no idea how to enter the engine to counter the time.

It would be good if you could expand some more on using a table to count down to that event.

 

[Evon]

Yes but the Switch() was not used at the form level, it was used in the query which feeds from the updatable form. So everytime the form (whichever field) is updated, the query is automatically updated and then switched.
Honestly, I have tested it over and over and it runs smoothly.

 

[Evon]

You really need to use an external file that the user won't easily find so it can't be placed in the same directory as the database.

Yes, but I have tried everything I know and so far I am not able to do anything external (via the internet) with objects in MS Access 2007.
Could you enlighten us lesser ones please?

 

[Evon]

The "switched" field is a calculated field and as such CANNOT be updated. You can update the source of the switch but not the result.

And therefore we are not in diagreement, because I am saying that the new results are "Switched" everytime a table/form is updated because the switching is done at the query and/or the report level.

Anyway,thanks Pat. I will do some more research on using a FTP site.