Now how do I count the number of results i get in the report/query?

[gnibbles]

Ive tried dcount and count and all that crap but they keep asking me for more stuff. even with the dcount i put in criteria and it still looks for something. And the help feature on ms access is useless, sorry to anyone it may offend.

 

[famousb]

ok, where are you trying to count the records in the query.<br>you can't count them in the same query that you have them in, this screws it all up. so you either have to build another query, or do it in an unbound field in a form or report.<br>and the Count function should work fine for counting just the number of records, DCount works like the CountIf function in Excel and is used when counting according to criteria is needed. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[DougP]

Make a report and put <br>=Count([SomeField]) <br>in the Footer, works like a charm.<br><br>Plus a report is much prettier for the Boss <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[gnibbles]

yeah thats how I was doing it thatnks doug, but will it screw up other counts, cause I was trying to do it with another one and there are 5 records and it comes out with like 183000

 

[Shirley]

is the field you are counting text or numerical.

 

[gnibbles]

thanks I got it figured out after

 

[ErikZ]

I'm counting a text field. I need to count the number of &quot;Yes&quot; that come up.

In my report I have:
=Sum([vpIsReplacement]=&quot;Yes&quot

Which works great except for one thing, it counts yes as negative. So instead of a 2, I get a -2.

Any ideas? I've tried:
=Sum(Abs[vpIsReplacement]=&quot;Yes&quot

but Access gives me an error on that.

 

[gnibbles]

what if you, multiplied it then by -1, you'd then get the answer you are looking for, right, then just test it further and if you are still having problems let me know. But this seems like a nice simple solution.



gnibbles
nturpin@excite.com

 

[ErikZ]

Hurm, I can simplify that a bit...

Old way:
=(Sum([vpIsReplacement]=&quot;Yes&quot)

New way:
=-(Sum([vpIsReplacement]=&quot;Yes&quot)

It's simple and it works, but it's a hack. :-/

Ah well,
Erik Z.

 

[gnibbles]

whatever works thats my motto

gnibbles
nturpin@excite.com

 

[tlbroadbent]

ErikZ,

Why not use a Count function to count records rather than Sum? Sum adds the values of the fields which could return very different results than a simple count.

Try using DCount on your report.

=DCount(&quot;*&quot;,&quot;tablename&quot;,&quot;[vpIsReplacement]='Yes')

This statement actually counts records and no hacks are necessary. Terry

X-) &quot;Life would be easier if I had the source code.&quot; -Anonymous

 

[ErikZ]

Because that way is hard and confusing.

My report takes the output of a query. A list of agents. It sorts them by agency. It lists every sale they make, so it looks like this.

AGENCY1
Agent1
Sale1
Sale2
2 sales, 50% local.
Agent2
Sale1
1 sales, 0% local.

So, how do I use Dcount on each agent? The help file say that I can only use it on Tables and Querys.

 

[tlbroadbent]

Correct! You use DCount on tables and queries with selection criteria that could include Agent. However, I see the error in my understanding. DCount is not appropriate the point you are applying the count.

There are other ways to design the report and underlying queries to get the results you seek. However, as you already have a design in place and a workaround to provide your count, go for it.

You can use absolute value in your calculation. You had a small syntax error in your attempt. This should work for you if want to use it. You just needed an extra set of parens.

=Sum(Abs([vpIsReplacement]=&quot;Yes&quot) Terry

X-) &quot;Life would be easier if I had the source code.&quot; -Anonymous

 

[EllieFant]

I have a question that is somewhat related (I think).

In my database I have a field called category. It is a dropdown field (gets the information from a table called cat_lookup).

What I want to do is a trend report telling me how many times each category has been used if any. I have made the report tell me how many times it finds the categories used (ie confined space, drinking water, and etc.) What I can't get it to do is tell me when one of these &quot;catagories&quot; haven't been used. I would like it to do this so I can tell if certain type of inspections are not being done without comparing a list of the &quot;catagories&quot; with those that the report generates to find out what is missing.

Does this confuse you? Can you help? I can't redo the database to make this easier as there are 1,000's of records in it and I DON'T want to have to reinput them.

Thanks

 

[raskew]

Ellie-

In an application that uses tblCats as the lookup for a list box, I tried the following to simulate your situation. It worked well and only took about five minutes to set up.

1. Add a number field [Selected] to tblCats, setting its default value to 0.
2. Fill the existing [Selected] records with 0, either manually or with an update query.
3. Add the following code to your list box's On Click or AfterUpdate event (whatever you're now using):

Dim strSQL As String
strSQL = &quot;UPDATE tblCats SET tblCats.Selected = [tblCats]![Selected]+1 &quot; _
& &quot;WHERE (((tblCats.CatName)=[Forms]![Your form Name]![Your control name]));&quot;
docmd.SetWarnings False
docmd.RunSQL strSQL
docmd.SetWarnings True

4. After testing, you should find that the Selected field has been appropriately incremented.
5. From that point it was a simple matter to create a query to review the results, e.g.

SELECT tblCats.CatName, tblCats.Selected
FROM tblCats
ORDER BY tblCats.Selected DESC;

6. And finally use the reports wizard to create a report from the above query, grouping on Selected and sorting descending.

Hope that helps

 

[EllieFant]

Thank you for that information. That sounds like it will work great. I do have one question though......What if I want to find out what categories have been used between two dates? Will this same method work?

My co-workers keep me challenged with this database!! I should be a pro in about 10000 years

Thanks for all your help. lena.wood@wgint.com (work)
ellefant@bossig.com (home)

 

[raskew]

OK, scrap the first idea since it won't work well with you date requirement. Think about going with a table (tblCatHit) with this structure:

FieldName DataType

CatHitID Autonumber
CatName Text
HitDate Date/Time (short date)

In your listbox's (lstCats) on click event, add the following code:

Dim strSQL As String
strSQL = &quot;INSERT INTO tblCatHit ( CatName, HitDate )&quot; _
& &quot;SELECT tblCats.CatName, Now() AS dDate FROM tblCats &quot; _
& &quot;WHERE (((tblCats.CatName)=Forms!YourFormName!YourControlName));&quot;
docmd.SetWarnings False
docmd.RunSQL strSQL
docmd.SetWarnings True

Now, when you select from the list box, the code will be adding records to tblCatHits, looking like this:

CatHitID CatName HitDate
1 Staff 5/16/2001
2 Clients 5/16/2001
3 Training 5/16/2001
4 Billing 5/16/2001
5 Billing 5/16/2001

Now you need a totals query which will count the number of hits for each category, including those where there were no hits. A locally modified variation of this will do it:

SELECT tblCats.CatName, Count(tblCatHit.HitDate) AS CountOfHitDate
FROM tblCats LEFT JOIN tblCatHit ON tblCats.CatName = tblCatHit.CatName
GROUP BY tblCats.CatName
ORDER BY Count(tblCatHit.HitDate) DESC , tblCats.CatName;

…returning this (from my example)

CatName CountOfHitDate
Billing 2
Clients 1
Staff 1
Training 1
Documenter 0
Help 0
Instructions 0
Residential 0
Utilities 0

Now, all you need is a report based on the above query, and a mechanism to specify the date range.

 

[EllieFant]

With the above code, what happens if the user chooses staff and then changes their mind and makes it billing.

Is there a way to delete the line if the user changes their mind about what category it falls under?

Thanks
Ellie
7122 lena.wood@wgint.com (work)
ellefant@bossig.com (home)

 

[EllieFant]

Guess I should have mentioned that I use Access 97. lena.wood@wgint.com (work)
ellefant@bossig.com (home)

 

[PhilipSchroevers]

I think the best approach (if I understand your problem correctly) would be to use the concept of &quot;right join&quot;. This will work because you have this lookup table 'cat_lookup'. Let'suppose your &quot;main&quot; table is called &quot;MaintMain&quot;. This is what you should do:

- define a query (using design view), select MaintMain and cat_lookup;
- create a link between these tables on the 'category' field.
- IMPORTANT: right click on the link and select Join Properties and choose option 3 (that should be something like: Include ALL records from Cat_lookup and only those records from 'Maintmain' where the joined fileds are equal);
- Now select the fields that you need in your Query from either table;
-If you just want to know which categories have not been used in MaintMain, enter in 'Criteria' for a field from Maintmain: Is Null. Do this on a field that should never be Null (e.g. the category field itself!).
This selection will give you all records from Cat_lookup that are not used in Maintman.

Please let me know if you have any problems with this. This construct of Left or Right joins can be very powerfull (e.g. checking the integrity of your data almost on the fly).

Philip.Schroevers@nl.abnamro.com