Counting Yes/No results wrong???

kaluapig

Registered User.
Local time
Today, 00:39
Joined
Aug 16, 2004
Messages
17
Hello again!

I am now trying to sum a YES/NO field using an Expression in the Totals field of the query as follows:

Abs(Sum([myfield]))

I know for a fact that the total should equal 1, but the results keep on coming back as 2.

NOTE: I have many records but of all the records, I only checked [myfield] on one record. I queried the database and grouped by my primary key to see if there was a hidden one somewhere but came up only showing that the calculations are from only the one record I checked "Yes".

Does someone have any insight to this?

Thanks so much,

Nick
 
Check again. Select the rows where the field = true. Or open the table and sort ascending on the field in question.
 
Sum(ABS([myfield]))

???
kh
 
OK,

I'M IN DISTRESS!

FOR THE LIFE OF ME I CANNOT FIGURE THIS OUT!

I uncheck the only row that is checked yes and the query calculates 0 instances.

I check only one row and it counts it twice.

I tried other rows and found too that it calculates these twice. I thought it might have to do with "grouping by" certain fields but I have taken those fields out of the query and still the same problem.

I am definetly a newbie at this. If anyone there can guide me I'm ALL EARS!

Thanks a bunch,

Nick

P.S. the ABSOLUTE "ABS" function is used to give me a positive number instead of the negative number that is calculated for things that are checked "YES".
 
Are you using some kind of totals query or is this a simple select query?

Can you make a sample db small enough to post?

kh
 
Try the following:
Code:
field:   Count([fieldname])        fieldname
where:                                 True
(please see the attached photo.)


Tell me if that solves the problem.
 

Attachments

  • query.gif
    query.gif
    49.1 KB · Views: 164
Last edited:
I think this should work for you in a select query

FieldName Total: Sum((Abs([FieldName])))

Hope it helps

Jim
 
Thanks for your input thus far! I think the problem lay in my Totals Query. I'm doing some troubleshooting today to see if I can narrow the problem down. I truely think it is not the equation that is messing things up. I think it is the query somehow adding more rows with the same data than necessary. If I get stumped for the 10th time again today, I'll try to make my database postable and let all the seasoned veterans have at it!

Again, thanks for all your help thus far. I do not know how I could have gotton this far without your support!

Will get back to ya!

Thanks!

Nick
 
The query that the sum() is based on must include a 1-many join. This would cause a problem if you are trying to sum or count something from the 1-side table because the rows caused by the many-side join "duplicate" the 1-side data.

If you want to count/sum things in the 1-side table, remove any many-side table from the query. If your ultimate goal is a report, use a subreport to show the many-side data. That way the main report would only include data from the 1-side table and groupings and totals won't be multiplied by many-side rows.
 
OK, I found the problem. Nothing was wrong with the formula; it was the query that was retrieving the same table record multiple times due to another related record having multiple records relating to the first table. This caused multiple instances of the same data to appear and consequently get included into the totals. In short, I was summing duplicate entries.

NOW FOR THE NEW PROBLEM!

I have created multiple queries, each for all the tables in my database. I couldnt do just one query because of my above problem of summing duplicate entries.

In each query, I have a "Grouped by" field (which is exactly what I need to use to format the data to report). So for each table, I have a query.

The "grouped by" in the Totals query formats a result like this:

DELAY COUNT AVE DELAY TTL DELAY
UNITED 2 5 10
DELTA 6 5 30
CONTINENTAL 3 3 9

and another table query would display something like this:

FUEL CHARGE
UNITED 50,000
DELTA 60,000
CONTINENTAL 80,000

//SORRY - THE ACTUAL DATA I CANNOT PUBLISH DUE TO SOME SECURITY ISSUES THAT WOULD PROBABLY GET ME FIRED//

Anyways, what I need to do now is either:

1) Put these two queries into one query, now that I got the correct totals in each.
PROBLEM - Tried this already and even though the numbers are correct in each query, the new query totals gets WAY WORSE.

2) Put these queries into a ACCESS report.
PROBLEM - ACCESS does not allow me to put two-queries in one report for some reason.

Anyone with any suggestions is very much welcome at this point.

BTW - Thanks for listening & letting me vent!

Nick
 
Once you have grouped each query so that you have a single line per carrier, there shouldn't be any problem in joining the queries as long as each query has an IDENTICAL set of rows. If any carrier is missing from one of the queries for example because they had no delays during the time period, you'll need to use an outer join that uses the table that lists all the carriers as the left most table. That will force a row for every carrier even though they have no data.

The best way to handle this problem, however, is to use multiple subreports on a main report. The main report would be based on a query of all carriers. Each subreport would show a different type of stat. As long as the master/child links are properly set the main report will sync the subreports so that each page will contain data for a single carrier. If what you want is a report that compares carriers, your main report would be unbound.
 
Will try the query again and will read up on how to do reports and will jump right into both today!

Thanks so much Pat for your guidance and all the super people in this forum!
 
EUREKA!!!

Got the two queries, queried effectively. The reasons I was having the problems with the results were:

1) I did not link the two queries correctly, and,
2) I was using the SUM function to sum up the results when I did not need to sum anything since the results were already totaled in each query (got caught on the same duplicate record problem again). Instead of sum, I used grouped by and got exactly the same data as displayed in the query that I was querying. ===COOOL===

AGAIN THANKS SO MUCH FOR YOUR HELP!

Nick
 

Users who are viewing this thread

Back
Top Bottom