Form Count by CheckBox criteria 1

[netrusher]

Below is the SQL that I have so far in trying to accomplish a goal. In our database we have Engineers who fill out a form and submit it to others for work to be done. It works pretty good. Each form has some yes/no check boxes that when checked, shows what forklift needs the work. For instance: (CheckBoxes) F187, A910, E010, and C809 indicates a particular Tonnage Forklift that needs to be worked on. I am trying to get a total Forms count that are submitted for a particular date range. The code below works and gives me a total when I have limited the conditions to one CheckBox. When I try to add another CheckBox to the mix my total does not equal the correct total for the date range. Also I need to state here that one or all of the CheckBoxes could be checked on each Form and I would only want to count that Form as 1 no matter how many of the CheckBoxes are checked. I hope this makes sense and someone out there can help me.

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND F187=True and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]") AS FORMS
FROM all_trucks_table
GROUP BY
DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and  F187=True AND [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]");

 

[dhookom]

If you really have fields with names like " F187, A910, E010, and C809 " then I think your structure is wrong. I would read up on normalization so that your field names become data values in related records.

You could possibly normalize this data for reporting purposes with a union query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

Thanks Duane,

I certainly am not in the position to change everything at this time. I have read up on normalization and appreciate what you are telling me. Right now I have what I have and need to try and get the information I need for totals. I am sure somebody out there can help me as I already have started the query. I am open to a better query but I do not have the authority to change the database.

Please help with this query anyone!

 

[VRoscioli]

What happens when you try to add another checkbox? Have you tried something like (this example adds "A1"):

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and [blue]([/blue]F187=True [blue]AND A1=True)[/blue] and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]") AS FORMS
FROM all_trucks_table
GROUP BY
DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and [blue]([/blue]F187=True [blue]AND A1=True)[/blue] and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]");

-V

 

[dhookom]

You could try create a union query like:

SELECT Date_of_Change, "F187" as  ForkLift, [F187] As NeedsWork
FROM all_trucks_table
UNION ALL
SELECT Date_of_Change, "A910" , [A910]
FROM all_trucks_table
UNION ALL
SELECT Date_of_Change, "E010" , [E010]
FROM all_trucks_table
UNION ALL
--- etc ---
FROM all_trucks_table;

Then query this query for your date range and where NeedsWork = True.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

VRoscioli,

When I add another criteria like your example I get a huge number. It jumps from around 12 to over a thousand.

 

[VRoscioli]

First of all, if you added it exactly as I showed, it couldn't possibly return more results (the AND between the two checkbox types should have been an OR). I'll assume you used an OR... Make sure that you put parentheses around that, or else it will not count the other criteria correctly, i.e the blue parentheses below are quite important:

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and [blue][b]([/b][/blue]F187=True OR A1=True[b][blue])[/blue][/b] and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]") AS FORMS
FROM all_trucks_table
GROUP BY
DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and [b][blue]([/blue][/b]F187=True OR A1=True[b][blue])[/blue][/b] and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]");

-V

 

[netrusher]

VRoscioli,

The problem using AND statements is I am not looking for AND I think I am looking for or and each BuildID can be on each form or only One BuildID can be on a Form and either way I only want to count that form once.

 

[netrusher]

Sorry Vroscioli,

I did not see your timely posting. I will give it a try and get right back to you.

 

[netrusher]

That seems to have worked. My question is: Is this code only counting a form once even if it has one of the Build ID or all six? If that is true this is AWESOME!!

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true or (F187=True or a910=True or e010=true or c809=true or f001=true or c810=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]") AS FORMS
FROM all_trucks_table
GROUP BY
DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and (F187=True or a910=True or e010=true or c809=true or f001=true or c810=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]");

 

[VRoscioli]

The "OR"s indicate that the expression in those parentheses will be true if at least one of them is true. If you want it to return only results where all of the expressions in the parentheses are true, you should use "AND"s instead of "OR"s.


-V

 

[VRoscioli]

I think I misunderstood your question when I first read it. This query will only count a row once, regardless of how many of those conditions is true (as long as your database doesn't have duplicate entries in it).


-V

 

[netrusher]

Below is the code for the three queries. They seem to be working good. The first code is for 1-3Ton, The second code is for 4-7Ton, the third code is for 6-9Ton. My question is: Is there a way to combine the three to show the totals for each query in one query so it would look like this?

1-3Ton 4-7Ton 6-9Ton
33 54 28

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and  (F187=True or a910=True or e010=true or c809=true or f001=true or c810=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]") AS FORMS
FROM all_trucks_table
GROUP BY DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and (F187=True or a910=True or e010=true or c809=true or f001=true or c810=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]");

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and  (g004=True or e818=True or m005=true or f813=true or d024=true or c879=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]") AS FORMS
FROM all_trucks_table
GROUP BY DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and (g004=True or e818=True or m005=true or f813=true or d024=true or c879=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]");

SELECT DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and  (h006=True or c878=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]") AS FORMS
FROM all_trucks_table
GROUP BY DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true and (h006=True or c878=true) and [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt]");

 

[dhookom]

Sure, you can get that result by using the union query that I suggested to normalize and then create a crosstab query based on the union query. You would need to add the FORM # field to each SELECT in the union query first.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[VRoscioli]

I would suggest rewriting those queries as:

SELECT count([Date_of_Change]) AS FORMS
FROM all_trucks_table
WHERE [FORM #]=True
    AND (F187=True or a910=True or e010=true or c809=true or f001=true or c810=true) 
    AND [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt];

I think that should work the same as your first query and is much more easily read. As for combining them, I would try:

SELECT Sum(Iif((F187=True or a910=True or e010=true or c809=true or f001=true or c810=true),1,0)) AS "1-3 Ton", 
    Sum(Iif((g004=True or e818=True or m005=true or f813=true or d024=true or c879=true),1,0)) AS "4-7 Ton",
    Sum(Iif((h006=True or c878=true),1,0)) AS "6-9 Ton"
FROM all_trucks_table
WHERE [Form #] = True AND [Date_of_Change] Between [Forms]![OmsStatus]![StartDateTxt] And [Forms]![OmsStatus]![EndDateTxt];

Let us know how this works out.


-V

 

[netrusher]

VRoscioli

That is a much cleaner query and the combination works great!

I certainly appreciate your help!!!

 

[dhookom]

I would normalize with a union query like:

SELECT Date_of_Change, [Form #],"F187" as  ForkLift, [F187] As NeedsWork
FROM all_trucks_table
UNION ALL
SELECT Date_of_Change, [Form #], "A910" , [A910]
FROM all_trucks_table
UNION ALL
SELECT Date_of_Change,[Form #], "E010" , [E010]
FROM all_trucks_table
UNION ALL
--- etc ---
FROM all_trucks_table;

Then create a crosstab that uses [Form #] as the Row Heading, Sum(Abs([NeedsWork])) as the Value and Forklift as the Column Heading. Set a where clause on the Date_of_Change column.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]