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....

 

[zookeeperbobbie]

Hi, thanks for your help-
I tried the first form and its working except thats its counting the total # of problems. NOt just one type. I also had to type on shortages in teh parimeter windows that kept popping up.

I did create a cross tab query which is working perfect... Now here is my problem. I cant use this as a subform which i need to do. I created a new query based on this, but i;m getting the error message you must set the query's column headings? Any idea what this means?


Thanks!!

 

[Andrew Fergus]

Hi

You mentioned it is working except it is counting the total number of problems. Is it counting problems that don't contain the word "shortage"? If so, can you post the SQL for this query?

To stop the pop-up go into the query design screen and check under menu option Query > Parameters > this should be empty (if it's not then delete it). If you are still having problems, create a new query using just the SQL code I provided.

Regarding the crosstab query and subform - crosstab queries don't naturally lend themselves to forms and reports given the column headings constantly change - this is the beauty of a crosstab query but a form / report requires fixed column headings so there will be issues.

I'm not saying it is impossible (just difficult) but before we head down that track, can you confirm what you are trying to achieve with the crosstab query and subform? And what data are you expecting to see in the column headings? You might want to post the SQL for the crosstab query too.

Andrew

 

[zookeeperbobbie]

Hello- The file is at work so i will post the sql tomorrow.

What I am trying to achieve is a master form which will when i select each vendor will have sub forms based on certain queries.
So far I have been able to achieve problems by each vendor, total products by each vendor and total deductions by each vendor.

Now I have to create some way of showing how many of each type of problem each vendor occured. (Eventually I am going to have to throw in a date range).. I am pretty pround that I have gotten this far in less than 2 weeks... So while i;m a newbie. I think i;m a fast learner..
Thanks for you help

 

[zookeeperbobbie]

Good Morning- Here are the sql's


From the query thats counting the total # of problems(supposed to count only shortage problems):
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 Problems;


My crosstab query:

SELECT Problems_Crosstab.Vendor, Problems_Crosstab.[Total Of ID], Problems_Crosstab.[Color Correction Fee], Problems_Crosstab.[Damaged/QC Issues], Problems_Crosstab.[Not fully utilized], Problems_Crosstab.[Po Late], Problems_Crosstab.Shortage, Problems_Crosstab.[Spec Issue]
FROM Problems_Crosstab;

What I really need is someway of being able to have a total count of how many total problems each vendor had and they break it down by how many of each type of problem a vendor had. I created a form and sub forms because its much easier to show the vendors when I have to give them reviews... So basically I need help finding a way to add in access... I;m so frustrated.. It seem slike it would be so simple... I wish access was much easier to do functions like excell... But excell doesnt have the database and linking like access does...


Thanks for any advise help you can give...
Thanks

 

[VicRauch]

In your crosstab query, it appears that you will always be having the same columns. Therefore, you can actually name the columns for your crosstab query. And, if the data would not generate that column, by naming the columns in advance, you are assured of always having that column when the query runs. There just will not be any data in that column. To name the columns ahead of time, be sure it is a crosstab query, then select menu View, option Properties. In the Column Headings line (property sheet), put in the names you want for the column headings (output from the query) that the "Column Heading" (Crosstab row in query grid) column in your crosstab query could generate. (Read that last sentence slowly! The information is there, just go slow and follow it.) By naming the columns this way, the form you create to show the output of this query will always have the fields it is looking for.
HTH,

 

[zookeeperbobbie]

Hi, Vic, ray:
Thank you!!!! Finally at least part of this is working. Its counting each of the problem types by vendor but only in one of the problem columns (I have 4)
Not any of the other problem columns.

Any suggestions?

 

[VicRauch]

You posted this SQL as (I think) the SQL that is to provide you with a count of all "shortage" problems.

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 Problems;

Is this the right SQL? I thought your words were in another table, which means this SQL is looking at fields that should not match. What type of data are you using in your Problems table to point to the correct word(s) in the Words table?

 

[zookeeperbobbie]

Hi, I am so confused.. And that doesnt happen very often.. Thanks for your patience with me.

I;m just copying and pasting whats in the query thats suppose dto be pulling the data, Everything is working ok.. except that its only pulling from one of the problem columns.



The problem type table contains the problem type that supports teh drop down list when i enter problems via my data entry form, but There will always be some type of problem entered in the problems table.

I tried going in to the cross tab query and changing the column headings and it worked perfect for the total # of problems per vendor...

But in the design view I only see problem 1 field listed.. The others are not listed... I tried to insert these and copy the same settings as those of problem 1 but nothing is showing up..

The problem seems to be that I cant get access to count any more than one of the columns of problems.

I dont know if this would work for my purposes, but if i entered all of the problems in one column, how would i seperate them so that access would realize that they are to be counted as two different things?

for example:

Vendor Problems
Twyla Shortage, Po late

I tried doing this but the query is counting "Shortage, Po late" as one word..



I think I may just give up on this... You'd think this would be a simple thing to do.. but instead Its so frustrating...

 

[VicRauch]

If you could provide a sample of your database, it would be MUCH easier. Create a new, blank database, then import the tables, queries, and forms that are being used with this situation. The only way I know to allow board members access to this new database is to copy it to a web site you have access to. Then post the address to the database, and we can download it from there. For example, I have a demo database on my personal website. When I put in the address to that database on my site, anyone can download that database: http://vicrauch.com/sponsors.mdb.
Hope this helps.

 

[zookeeperbobbie]

Thank you so much.. I will work on this tonight if i have time and if not this weekend- Thanks again for all your help!! I areally appreciate it