By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Query Results

P: 73
I have a Query that essentially runs a report by date. So every month I go in and run my results have the following columns:
Date
TO: Count(Account Num)
Amount: Sum(Amount)
Except: Where Like "2" (which is for the Exception box on my input sheet that says Yes/No and If it is an exception (Yes) it defaults to 2 and I am pulling the data to see if there were any exceptions for the month.)

However, when I run my report this is the first time I had an occurence where there weren't any accounts set to Yes. and then says there is an error on my report and doesn't fill out anything and I need my report to reflect something. Is there a way either in my query or my report that I can run this and it will reflect 0 (Zero) for TO or Amount if there were no exceptions for the month??
Feb 14 '08 #1
Share this Question
Share on Google+
11 Replies


Scott Price
Expert 100+
P: 1,384
Could you paste in the actual SQL of the query you are using?

Basically I think you'll end up using and IIF() statement to fill out the 0 value, but we'll be able to tell better after seeing the SQL.

Please include the [code] tags after pasting by selecting all the SQL text and clicking on the # button on the top of this reply window. Thanks!

Regards,
Scott
Feb 14 '08 #2

P: 73
Could you paste in the actual SQL of the query you are using?

Basically I think you'll end up using and IIF() statement to fill out the 0 value, but we'll be able to tell better after seeing the SQL.

Please include the [code] tags after pasting by selecting all the SQL text and clicking on the # button on the top of this reply window. Thanks!

Regards,
Scott

Sorry, was trying to say what I needed instead of pasting this big SQL text. Hope you can still help me. I am lost on this. I know this SQL works because I have 2 results in January from table (a) that I have checked as Yes (2) and it pulled up my report just fine. However, I came accross doing my second test and running it that it didn't have any results set to 2 that it had nothing there in my query. I tried to do this if statement iif((a.NAcctnum)IS NULL, "0", Count(a.Nacctnum)) but it didn't take it. I have spent 3 hours trying to figure this one thing out and haven't gotten anywhere. I also tried a CASE WHEN statement but that didn't seem to work either.


Expand|Select|Wrap|Line Numbers
  1. SELECT CC.Date_ID, Test.Test, Colors.Color, Employees.FN & " " & [LN] AS Name, CC.CC, Testing_Freq.Freq, Lend.Lend, PP.Com, PP.Meth, PP.Samp, Count(a.NAcctnum) AS [TO], Sum(a.Onus) AS Onus
  2. FROM (Lend INNER JOIN (Testing_Freq INNER JOIN (PP INNER JOIN (Test INNER JOIN ((CC INNER JOIN Colors ON CC.Color_ID = Colors.Color_ID) INNER JOIN Employees ON CC.Name_ID = Employees.Name_ID) ON Test.Test_ID = CC.Test_ID) ON PP.Test_ID = CC.Test_ID) ON Testing_Freq.Testing_ID = PP.Testing_ID) ON Lend.Lend_ID = PP.Lend_ID) INNER JOIN 28_Prepay_Input AS a ON (CC.Test_ID = a.Test_ID) AND (CC.Date_ID = a.Date_ID)
  3. WHERE (((a.Except) Like "2"))
  4. GROUP BY CC.Date_ID, Test.Test, Colors.Color, Employees.FN & " " & [LN], CC.CC, Testing_Freq.Freq, Lend.Lend, PP.Com, PP.Meth, PP.Samp
  5. HAVING (((CC.Date_ID)>=[Forms]![28_Prepay_Main]![date] And (CC.Date_ID)<=[Forms]![28_Prepay_Main]![date1]));
Feb 14 '08 #3

Scott Price
Expert 100+
P: 1,384
The correct syntax would be:

IIF(Isnull(a.NAcctnum), "0", Count(a.NAcctnum))

Give that a try!

Regards,
Scott
Feb 14 '08 #4

P: 73
The correct syntax would be:

IIF(Isnull(a.NAcctnum), "0", Count(a.NAcctnum))

Give that a try!

Regards,
Scott

I tried that also. It ran but doesn't show any data though. I think it has something to do with the where statement but I don't know how to go around it. Do you think I am going to have to run 2 different queries and to count all where they are set No and one that counts all Yes and combine the 2 queries together....I am not sure that this would even work this way...
Feb 14 '08 #5

P: 73
I tried that also. It ran but doesn't show any data though. I think it has something to do with the where statement but I don't know how to go around it. Do you think I am going to have to run 2 different queries and to count all where they are set No and one that counts all Yes and combine the 2 queries together....I am not sure that this would even work this way...

Well, Apparently that is not going to work either. Any ideas?
Feb 14 '08 #6

Scott Price
Expert 100+
P: 1,384
How are you opening the report?

Do you currently have code in the On No Data event of the report?

There are two approaches that I'm thinking of, one being what you are thinking of, which includes your first query in a second, final query that converts a null result to a zero. The other that just occurred to me is using the On No Data event to make invisible your normal text box configuration on the report, and make visible a label, sized and placed directly over the text boxes with a caption of "0" or a message stating that there is no data for that query.

Let me know what you would like to attempt.

Regards,
Scott
Feb 14 '08 #7

P: 73
How are you opening the report?

Do you currently have code in the On No Data event of the report?

There are two approaches that I'm thinking of, one being what you are thinking of, which includes your first query in a second, final query that converts a null result to a zero. The other that just occurred to me is using the On No Data event to make invisible your normal text box configuration on the report, and make visible a label, sized and placed directly over the text boxes with a caption of "0" or a message stating that there is no data for that query.

Let me know what you would like to attempt.

Regards,
Scott


I tried doing a couple things and a couple of if statements if one is blank to use the other one to combine the 2 queries together that way,however it didn't seem like that was working. I will probably try it again tomorrow... I def don't think it is my report that is going to help me thru this....My query is totally blank with no information. I need 80% of that information to show up in my report it is only the 3 columns of my where statement/sum statement and count statement that I can't seem to get the right equation to work to reflect if there are no Except = 2 that if will say 0 or even better "No Findings". I think it lies something to do with the where statement that I am running it where Except = Like 2. Is there another way of pulling a count(Except) where Except = 2 if not then 0. I tried kind of doing this however it counts all the Except in that column not only the ones that are 2's.

If you have any more insight it would be GREATLY appreciated

Thanks,

Matt
Feb 15 '08 #8

Scott Price
Expert 100+
P: 1,384
Your WHERE statement could end up complicated looking :-)

Try something like:

Expand|Select|Wrap|Line Numbers
  1. WHERE Iif(Isnull(a.Except), a.NAcctnum, (a.Except) LIKE "2")
This is intended to substitute the account number in your where criteria if the exception ends up empty. I haven't tried this in my test database, I'm a little tired tonight. However, I'll give it a go in the morning to see if I run into any problems with it.

Regards,
Scott
Feb 15 '08 #9

P: 73
Your WHERE statement could end up complicated looking :-)

Try something like:

Expand|Select|Wrap|Line Numbers
  1. WHERE Iif(Isnull(a.Except), a.NAcctnum, (a.Except) LIKE "2")
This is intended to substitute the account number in your where criteria if the exception ends up empty. I haven't tried this in my test database, I'm a little tired tonight. However, I'll give it a go in the morning to see if I run into any problems with it.

Regards,
Scott

Hello Scott,

I think I figured it out after 3 more long hours. I tried to play with the Where statement as you suggested, but the problem with it was it keep giving me all the results whether it was set to 1 or 2. Couldn't get that to work, so I just added all lines into the query whether it was a 1 or 2 and tried to adjust my report to only reflect the ones that were 2 but I came across the same problem. It wouldnt add them correctly if one of my results was set to 1 and the other set to 2. So, I went back to the original idea of having two queries and them combining them into one main one. It took me a little while to get it together but it seems like it is working correctly. Here is my new Code:

Expand|Select|Wrap|Line Numbers
  1. SELECT CC.Date_ID, Test.Test, CC.CC, Colors.Color, [FN] & " " & [LN] AS Name, PP.Com, PP.Meth, PP.Samp, Testing_Freq.Freq, Lend.Lend, IIf(([2].TO)>0,[2].TO,"0") AS [TO], IIf(([2].Onus)>0,([2].Onus),"0") AS Onus
  2. FROM 1 RIGHT JOIN (2 RIGHT JOIN (((Lend INNER JOIN PP ON Lend.Lend_ID = PP.Lend_ID) INNER JOIN Testing_Freq ON PP.Testing_ID = Testing_Freq.Testing_ID) INNER JOIN (Test INNER JOIN (Colors INNER JOIN (Employees INNER JOIN CC ON Employees.Name_ID = CC.Name_ID) ON Colors.Color_ID = CC.Color_ID) ON Test.Test_ID = CC.Test_ID) ON PP.Test_ID = CC.Test_ID) ON ([2].Date_ID = CC.Date_ID) AND ([2].Test_ID = CC.Test_ID)) ON ([1].Date_ID = CC.Date_ID) AND ([1].Test_ID = CC.Test_ID);

I had to right join the 2 queries together with all the other main tables and it appears to be working now. I played with it on different scenarios and still seems to give me the correct data. Do you forsee any problems down the road with the code?

Additional Question: I am the only one of the 4 on my team at work with some Access Background and I am trying to create a new Database for our entire team because we currently keep our monthly results in an excel file and is horrible with trending analysis. This report I created was only the 1st report I have created out of 35 tests that we currently run as a team. I am creating Front Ends for all 35 tests that link into the be. Every test is different as far as the input of data into there Front End, except for the fields State, Branch, Acctnum, Date, Test #, and Number of Exceptions would essentially be the same in almost all 35 tests (I think). I am creating a report like this one in each test so we can save our results for audit purposes. But the Main Goal is to have all 35 tests combined into one Main Report, so my manager can pull results for all 35 tests all together monthly and ultimately for a quarterly report (that is submitted up to the CEO). Either I haven't been able to figure it out properly and maybe I need to right join all of them together like I did with this one, cse the only other way I can even think about combining them together is running an Append Query and combining all tables into 1 main table then join them to the main tables, but that doesn't seem like that would be the best efficient way and could have user errors. Have any Ideas?
Feb 15 '08 #10

Scott Price
Expert 100+
P: 1,384
For your first question: No, I don't foresee problems with the Right Join as you are using it. Left/Right joins exist for purposes of allowing all results from one query and only the rows that match from another, which is what you are doing.

As for your other question: I would strongly suggest investigating a database design firm. It sounds like you are dealing with financial data, which requires rigid security and specific design methodology.

Not to take anything away from your abilities, but simply put, the task you are setting yourself to is greater than any one person. If, like you say, you are the only one on your team with Access experience, you lack the invaluable assistance of having someone else present on-site to bounce things off of, make sure you aren't forgetting something, etc. Also, there are pitfalls along the way that database design firms who deal in this work daily are aware of, that you will only become aware of in hindsight.

If you choose to go ahead on your own, we're here to give what help we can, but be aware of the liabilities of what you are choosing to do!

Dealing with financial information is getting more and more complicated, so from a legal standpoint, if you personally design and build a database containing this information and through oversight of your own in the design process, you end up exposing this information inadvertently, you are legally liable, which means you can be successfully sued. However, using a design firm shifts the liability from your shoulders to theirs! Think about it a bit...

Good luck with whichever way you go! And thanks for posting back here with what you got to work!

Regards,
Scott
Feb 15 '08 #11

P: 73
For your first question: No, I don't foresee problems with the Right Join as you are using it. Left/Right joins exist for purposes of allowing all results from one query and only the rows that match from another, which is what you are doing.

As for your other question: I would strongly suggest investigating a database design firm. It sounds like you are dealing with financial data, which requires rigid security and specific design methodology.

Not to take anything away from your abilities, but simply put, the task you are setting yourself to is greater than any one person. If, like you say, you are the only one on your team with Access experience, you lack the invaluable assistance of having someone else present on-site to bounce things off of, make sure you aren't forgetting something, etc. Also, there are pitfalls along the way that database design firms who deal in this work daily are aware of, that you will only become aware of in hindsight.

If you choose to go ahead on your own, we're here to give what help we can, but be aware of the liabilities of what you are choosing to do!

Dealing with financial information is getting more and more complicated, so from a legal standpoint, if you personally design and build a database containing this information and through oversight of your own in the design process, you end up exposing this information inadvertently, you are legally liable, which means you can be successfully sued. However, using a design firm shifts the liability from your shoulders to theirs! Think about it a bit...

Good luck with whichever way you go! And thanks for posting back here with what you got to work!

Regards,
Scott


Thanks for all your insight and your time with me on this. It is greatly Appreciated.

Matt
Feb 15 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.