the first feedback describes that the counting process is going fine.
But when I try to get a new second feedback I find the counting process is the same as in the previous feedback which is feedback number 1
The counting process is accumulating the feedback numbers and also the values "yes", "no", and "null". it is supposed that each feedback should be seperate from each other.
For example The feedback number 1 should display only 4 records with 3 yes of Q1, 1 No of Q1 and null = 0 of Q1, while feedback number 2 should display only 2 records with 1 yes of Q1 and 1 null of Q1 and No = 0 like this (^_^)
and I think that the relationship diagram is fine and there is no problem in it.
Please can anyone provide me the right solution of my problem, I would be more delighted.
If I had not helped you before, I would not have understood this one because you have not given enough information. I would help when wondering why something is not working, that you give the detail of how the thing is currently working. In other words, the detail of how these numbers are currently being calculated, which I have copied from the last question I answered for you.
Total Number of Feedback: =DCount("form_id", "tblYourTable")
# of Yes(Q1): =DSum("Q1", "tblYourTable", "Q1='Yes')
# of No(Q1): =DSum("Q1", "tblYourTable", "Q1='No')
Sum of Q1: =DSum("Q1", "tblYourTable", "Q1='Yes') + DSum("Q1", "tblYourTable", "Q1='No')
When I answered that question, there was no mention of Ref_ID number. But, now that we can see that, here is what you need:
Code:
Total Number of Feedback: =DCount("form_id", "tblYourTable", "Ref_Id=" & Ref_Id)
# of Yes(Q1): =DSum("Q1", "tblYourTable", "Q1='Yes', "Ref_Id=" & Ref_Id)
# of No(Q1): =DSum("Q1", "tblYourTable", "Q1='No', "Ref_Id=" & Ref_Id)
Sum of Q1: =DSum("Q1", "tblYourTable", "Q1='Yes') + DSum("Q1", "tblYourTable", "Q1='No', "Ref_Id=" & Ref_Id)
This code has assumed that the text box labeled Ref_Id actually has "Ref_Id" as the ControlSource.
HTH,
You are such a helpful guy ... who really concern about helping others ... (^_^)
You provide me the object expressions to solve my problem, successfully and effectively it worked with total number of feedbacks (^_^),
but unfortunately it didn't work with the others such as Q1 and Q2 ...
There is a missing double qutation in expression that you give me:
=DSum("Q1","tableName","Q1='Yes') it didn't work
But
I add the " and it worked ... when I add the "Ref_Id=" & Ref_Id it didn't work ...
I'm sorry. I do that to myself too.
Here is what all 3 should be in the way to add Ref_Id:
# of Yes(Q1): =DSum("Q1", "tblYourTable", "Q1='Yes' And Ref_Id=" & Ref_Id)
In other words, remove the double quote (") after Yes', remove the comma (,)after that, add a space ( ) and AND, and remove the double quote (") just before Ref_Id.
Now I see what I did. I added then needed criteria to the DCount, but then added it wrong to the DSum lines.
How are you? I hope that you are fine and in an excellent health
I am still having problem in understanding the counting environment
I am trying to count the number of different leave types for a staff ... but it doesn't work well .. because this time I am working on some fields that have lookup function!!!!
such as staff (Lookup) field in the vacation details
V_No (Lookup) field in the vacation details and so on ...
So, please see the attachments and tell me how to solve it ... I hope they may help you ...
I am waiting for your kindly reply ... I feel that my head will be sick of counting ...
You need to create a query that adds all this information for you, then you can reference this new query with DLookup in your three totals boxes. Here is the SQL for the query, then in DLookup, it will look like this for Yearly. Change that for Quarterly and Monthly.
Code:
SELECT [vacation details].EmpId, Sum(IIf([Type]="Yearly",1,0)) AS Yearly, Sum(IIf([Type]="Quarterly",1,0)) AS Quarterly, Sum(IIf([Type]="Monthly",1,0)) AS Monthly
FROM vacation LEFT JOIN [vacation details] ON vacation.V_No = [vacation details].V_No
GROUP BY [vacation details].EmpId;
Put this SQL statement into a new query, save the query with whatever name you like, then reference that with DLookup.
HTH,
Thanks ... I am happy now ... the headache is gone!
Hello GolferGuy,
How are you? I hope you are fine and alright
the code that you provided was very effective and it worked very well ... How did you figure out the problem I am astonished about your consciousness and awareness of solving the problems I am really fun of you
I put the code in a new query in "Union Query", and after saving the query as Query1 it automatically brought the two tables [Vacation] and [Vacation Details] in the query design.
Then I saw SQL expressions which were automatically placed in the cells
I had taken a look at the expressions and they were a little bit confusion but I actually understand the purpose of the expressions ... but about the DLookup ... I haven't used it in my life and why we should use the DLookup?
I will be more appreciated if you answer to my humble questions? After you replied to my current question .. I have another personal question .. if you don't mind??? Very simple not complicated???!!!
DLookup is a "domain" function, and DSum is a domain function. Just as DSum was originally a good way to fill those three boxes with the sum (but because of the complication would not work), DLookup is now the function of choice for these three boxes. When something is suggested that you don't know how to use, look it up in the help file, and test it to see if you can make it work. If you can't make it work, then come back with specific questions about what you have tried, and someone can help you understand.
I am happy that you replied to my first question, and you didn't ask me about the second question what would be??? not interested (just kidding)
Anyway, thanks you a lot for providing us with suitable questions which really meet our requirements and be with us in the next episode of non-stoping questions (kidding )
How are you? I hope that you are fine and in an excellent health ...
Actually I tried to reference the new query "Query1" that you told me to create it ... but can not be referenced with a DLookup which is on the form ...
I hate myself ... I can not be the database expert in my life and never ... (Why in everytime I try to execute something and get silly errors ...
Reply With Quote
.
you really made my day by helping me a lot ... Again thanks a lot (^_^)
but I actually understand the purpose of the expressions ... but about the DLookup ... I haven't used it in my life and why we should use the DLookup?
After you replied to my current question .. I have another personal question .. if you don't mind??? Very simple not complicated???!!!
