An expression used in a to reference a value in a combobox

[Isskint]

The 11 fields you use for the diets, can be reduced to 1 field and the 11 combos then reduced to 1 with a list of the 11 diets. The diet name would then be stored in the table (Field name DietName?) which makes filtering and counting etc a much easier task, because instead of having 11 criteria in a query you would only have the 1 EG

 

[Evon]

I have been at it for two days now and still am unable to make a breakthrough. I think I need to be baby fed.

 

[Evon]

I have been at it for two days now and still am unable to make a breakthrough. I think I need to be baby fed.

 

[Isskint]

Well i am happy to help, however it may be a long road.

Perhaps the best way to start is if you could explain the process behind the table Diet. Some things are obvious but others not so.


[FONT=&quot]

[/FONT]

 

[Evon]

Many of those fields are either redundant or futuristic. As I went along creating the database I kept on trying different things.

However, the cleaning up can stay until I have decided on a final direction.

Well, I have made some headway with resolving the issue as posted.
I created the one combo box for the 11 fields and was able to separate the values/ rows into separate columns in the query. However, when I run this query it returns the values as either "-1" or "0" which I understand to be true or false.
Now the challenge has been how to count all the true values in each column of the query separately. If I could do this I would be able to reference them from my report and my problem would be solved.

 

[Evon]

Still making headway, however the DCount function is giving me the total of each set of filtered records in the entire table. I am not able to get a sub-total for each group I have in the report.

Probably I should try to explain:
This database is used by a catering service that provides meals to patients at three hospitals. There are 11 different types of diets.
The database will have to count how many of each diet for each hospital by wards. This will have to be done for Breakfast, Lunch and supper each day.

So I have created a combo box in a sub-form, on the main form. The list of the 11 diets are in a separate table exclusively, and is referenced by the combobox. Whenever any one of the 11 diets is selected that instance/value is stored in one field. In other words all the different values for each record created are stored in one field.

The challenge was to group all the diets together (from the field they are all stored in) based on their names. And then have them grouped by hospital, then by wards and also by the different meal times (Breakfast, Lunch & Supper).

I have been able to do all this already. However, the DCount is not referencing the Query that the Report is based on but the Table that has the combo box values. So even though the Table is in the database relationship, the DCount fn seems to be accessing the table before it is related in the query from which the report feeds.

Because of this, I am not able to group the sub-totals by Hospital, Wards etc.