Query Results

[Supermansteel]

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

[Scott Price]

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

[Supermansteel]

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.

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
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)
WHERE (((a.Except) Like "2"))
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
HAVING (((CC.Date_ID)>=[Forms]![28_Prepay_Main]![date] And (CC.Date_ID)<=[Forms]![28_Prepay_Main]![date1]));

[Scott Price]

The correct syntax would be:

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

Give that a try!

Regards,
Scott

[Supermansteel]

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

[Supermansteel]

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?

[Scott Price]

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

[Supermansteel]

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

[Scott Price]

Your WHERE statement could end up complicated looking :-)

Try something like:

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

[Supermansteel]

Your WHERE statement could end up complicated looking :-)

Try something like:

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:

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

[Scott Price]

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

[Supermansteel]

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