DCount Help Desperately Needed

[Jul]

I have a table, that records various information about cartons by date and lane and carton type and product type for those cartons. In the lane fields, there is 11 of them, it records the number of cartons with specific issues (tears, globs and fallbacks). I am trying to run a query that will count the number of cartons that have those issues on any given day, in each lane. I have used the DCount function, however, it still counts the cartons that have 0's in those fields. For example, one particular date, there were 18 entries, so no matter what I do for the DCount function, it still comes back with 8 instead of 1 issue in lane 1. I have been fighting with this for weeks! Please help!!!

 

[SamDeMan]

create a query with grouping. (find the sigma on the tool bar when the query is design view it looks like an a captical E) you will choose two fields the date field and the lane field. in the crouping row, choose groupby (default) for the date and for the Lane field choose "count" on the pull down menu. should you decide to limit this further use the criteria row.

sam

 

[Jul]

Thanks, but unfortunately, that is one that I already tried that didn't work. It gives me 18 for the result, where I should be getting 1 for that particular lane. The other thing is, if I do a "where" for the count, and say >=1 for lane 1, the copy that through all 11 lanes, I get no results. It's quite interesting, and really frustrating. Thanks again for the suggestion.

 

[SamDeMan]

you can either normalize your table so that you will have a field called "lane" and then you can crosstab the lane field. or you can Union Query. you will only select one field i.e. tears. then you will union select the next one. you will do this 11 times. tro to send some code and i will try to help you with it.

sam

 

[Jul]

Hi again! So, I set up the tables, and I think that will work great, I was so excited, we actually cheered in my office yesterday when it worked. Now, I have one other question, I am hoping you could help me with. I need to calculate the percent of cartons with tears, which I did, and I also did the percent of cartons without tears. However, it is only giving me those dates that have tears, and what I need is for it to show every day, and where there aren't any tears, I need it to show 100% for the cartons without tears and 0% for cartons with tears. How can I do that? Thanks again, I truly appreciate it!

 

[SamDeMan]

I have no idea what you are using. i will assume that you have a query and that it is something like: =cartonsWithTears/CartonsWithoutTears
there is a function NZ() look it up in the helpfile, basically it changes Null To Zero or any other charachter. i would use it nz(CartonsWithTears)/CartonsWithoutTears or some thing like that. remember you can have an issue with 0 in the denominator. if you are using a different method, please post and will reply.

sam