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

 

[Pat Hartman]

You need to actually sum the true/false value rather than count them. Both functions work on the whole domain which is why your count always seems to count everything. If you use Abs(Sum(YourField)), you will get a "count" of the true values.

 

[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"

 

[Pat Hartman]

If you want to display -1 and 0 as True/False or Yes/No on a report/form, change the format on the control.

The Abs() functon was intended to turn the negative sum to a positive one.

 

[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.

 

[Pat Hartman]

You can't use the Switch() function on an update form since it will make the field not updateable so you need to know how to control how it is displayed.

1. If you don't need seriously robust security, have the start up form look for a file named xxxx.zzz in some directory that always exists such as Users/Public. If the file doesn't exist create it with a single record populated with the current date. If the file does exist, open it and check the date. If it is more than 30 days old, give the user a message and shut down. You could use an internal table but the user could more easily defeat that by simply reinstalling the application. When you choose the name for your date file, use something that the user won't easily be able to tie to your application.
2. You will need an FTP site and you will need to send the users instructions regarding how to download the new version. You could try to manage this with a batch file but I'm not sure what the syntax would be to refer to a directory on an FTP site. You'll need to experiment.

 

[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.

 

[Pat Hartman]

If you count the times the database has been opened, how will you handle multiple opens in a given day? You also need to create a table to store the count. And finally, this method can be easily circumvented by reloading a new copy of the database.

You haven't had a problem with Switch() because you are not updating the field in your form. There is nothing inherently wrong with using Switch(), you simply needed to know how to do it with property settings because otherwise you won't be able to create a form that displays the data as you want to see it and also allow you to update the field.

 

[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.

 

[Pat Hartman]

I thought I made it clear that I wouldn't use an internal table to count opens or to stave the install date. All the user has to do is to reinstall to start the count from 1 again or change the date. It is virtually useless as a limiting mechnizm. 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.

using the Switch() in the querry still allowed me to update the form

It doesn't prevent you from updating the form, it prevents you from updating the field whose value you switched.

 

[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?

 

[Pat Hartman]

Look up the Input # Statement for an example of reading a text file. The Write # Statement shows you how to write a text file.

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

As I said, you will need an FTP site and you will need a batch file or VBScript to log into the FTP site and download the file. I don't have any sample code I can post but I was able to find some when I searched for it a few years ago.

Honestly, I have tested it over and over and it runs smoothly.

We are clearly not speaking the same language. 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.

 

[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.