DCount question

[zookeeperbobbie]

Hi- first I want to thank everyone who has helped answering my questions in the past. in comparison to you all- i'm still for the most part a newbie, For those of you who have sent me the links to use access I appreciate it and yes i have been reading...

However I am stuck on a problem and i'm sure that someone will look at it in 2 seconds and say. Oh yeah thats an easy fix...I have been trying to work on this since 6:00 this morning and I still cant fiqure this out.

I;m setting up a query that will count the number of times shortage appears in a table, and across three columns. This is what i;m currently using-but getting errors:

field:Expr1: DCount([Problem 1] & [Problem 2] & [Problem 3] & [Problem 4],"Problems","shortage")
Table: Problems
Total: Group by
Show yes
Criteria:

Thanks for your help in advance....

 

[Andrew Fergus]

Hi

Is the word "shortage" going to be on its own within the field? Or will it be buried amongst other words?

Secondly, are you wanting one value (ie a total) for the entire table? Or do you want a total for each group? If so, what is the grouping?

BTW you mentioned 3 columns but your query shows 4 - which is it?

You are currently using the incorrect syntax for the DCount function - but your answers will dictate whether or not we need to use the DCount function. In the meantime, if you want to see the correct syntax, have a read of this.

Andrew

 

[zookeeperbobbie]

Hello-
I;m sorry trying to read through everything and fiqure this out gave me a migrane and i coudl not think straight.
Yes there is 4 columns. The word shortage could be contained in one or more of those columns.

I would like to have a count of how many shortgages occured by vendor.
I was going to try to set this up in another query after I fiqured this out. I have the feeling I;m making this more difficult than it needs to be though....

 

[VicRauch]

Andrew asked:

Is the word "shortage" going to be on its own within the field? Or will it be buried amongst other words?

You answered:

The word shortage could be contained in one or more of those columns

The question still remains, which I will re-word a little bit.
Is there only one word in each column, or can there be more than one word in any of these 4 columns?
It makes a big difference in how to look for the word "shortage".
Maybe you could give us a small sample of the data in two or three rows of these 4 columns.

 

[zookeeperbobbie]

I'm sorry-

in these 4 columns problem1-2-3 & 4 each is a drop list list with several types of "problems" Shortage in one of those options. So Shortage could appear in one or more of these columns or it may not appear for that record.

for example:


Vendor Po# Date Problem1 Problem 2 Problem 3 Problem 4
Twyla 23652 9/15/06 Shortage Damaged
Born 25421 9/18/06 Late
Express 26411 9/11/06 Spec issue Shortage

 

[VicRauch]

The question is, how many words can be in one column for one record. I think, because you have a drop down, there is only one word, but in your last example, you show three words "Spec", "Issue", and "Shortage" All three words COULD be in one column, or there could be two words in one column or there could be only one word in each column. We still do not know if there is a rule that says there will NEVER be more than one word per column. So, can there ever be more than one word in a column?
And, now that we do know that you use a drop down box, do you store the word (or words) from the drop down box into the record, or do you store a foreign key in this table that points to the word (or words) selected?

 

[zookeeperbobbie]

oK- Each record will have a max of 4 problem types. only one in each of the columns.
There will never be more than one problem type in each of these columns. There may be more than one word because of the drop down box. These are stored in a seperate table i created called "problem types".
So to answer your question yes there may be more than one word in each of these columns.

 

[VicRauch]

OK, you store the words in a spearate table. Therefore, you are using a foreign key in your problem table?

 

[zookeeperbobbie]

Yes I store the words in a seperate table.

I dont believe I;m using a foreign key- I only have the key in my table when it was first created. I then started entering data into via a form that I created...

I;m so sorry this is so confussing
Thanks for your help...

 

[Andrew Fergus]

Hi

Following on from Vic, the following query will return a total count of 1 per record where the word shortage appears in any of the 4 problem fields :

SELECT Sum(IIf(Nz(InStr([Problem1],"shortage"),0) + Nz(InStr([Problem2],"shortage"),0) + Nz(InStr([Problem3],"shortage"),0) + Nz(InStr([Problem4],"shortage"),0)>0, 1, 0)) AS ProblemCount 
FROM MyTable;

So if, for example, the first record had the word "shortage" somewhere in 'Problem1' and 'Problem2' this query would only count that record once. If however, you want your query to count that record twice, then use the following SQL instead :

SELECT Sum(IIf(Nz(InStr([Problem1],"shortage"),0)>0,1,0) + IIf(Nz(InStr([Problem2],"shortage"),0)>0,1,0) + IIf(Nz(InStr([Problem3],"shortage"),0)>0,1,0) + IIf(Nz(InStr([Problem4],"shortage"),0)>0, 1, 0)) AS MyOtherCount
FROM MyTable;

You can copy and paste the SQL into a query by opening a query and clicking View > SQL View. BUT make sure you use your actual table name where I have used 'MyTable'!!! If you add any of the fields from the underlying table to this query then you will lose the total for the table and instead get a total by whatever field you included in the query.

HTH, Andrew