query with conditions by date (1 Viewer)

[ctieppo]

I am so frustrated right now that I could scream. I have a database which tracks attendance and referrals for a networking organization - it meets weekly on Thursdays. It has a report which displays totals of both attendance and referrals for each weekly meeting for an entire month. The problem I am having is related to the fact that some months have 4 Thursdays and some have 5. If I get my queries to display the meeting dates correctly for the 4-Thursday months, then it displays incorrectly for the 5-Thursday months, and vice versa.

Is there any way to put a criteria on a missing record? If I could have it just populate the 5th week with spaces or null when it doesn't exist for that month, it would make my life a lot easier.

I can send a copy of the database to anyone who thinks they can help. It's too big to post.

 

[CraigDolphin]

I think you need to provide more information for us to be able to offer any help. Perhaps a screenshot of your table/data structure and a description of your queries and how you are using them would be a good starting point. The SQL of your query might also be useful.

That said, it sounds to me like you may be needing a report with a subreport embedded within it. The main report would be bound to a query listing thursday dates that fall into a particular month. The subreport would be bound to a query that lists attendance etc for a particular thursday meeting. The subreport would be linked to the main report using the date field common to both queries. That way, the number of thursdays within a month is irrelevant to how your report is structured.

HTH

 

[ctieppo]

more information

Yes, sorry, I realize that I didn't give enough details.

Here is the scenario:

I have a table called tblMeeting with two fields, MtgDate and Week (for the week # in the month).

My report has a subreport which displays the meeting dates for each week above the columns of numbers for referrals and attendance statistics.

The subreport is based on a series of queries:

QUERY qselMeetingDates:
SELECT tblMeeting.MtgDate, tblMeeting.Week
FROM tblMeeting
WHERE (((tblMeeting.MtgDate) Between [forms]![frmdaterange]![startdate] And [forms]![frmdaterange]![enddate]));


QUERY qselMtgsWeek1:
SELECT tblMeeting.MtgDate, tblMeeting.Week
FROM tblMeeting
WHERE (((tblMeeting.MtgDate) Between [forms]![frmdaterange]![startdate] And [forms]![frmdaterange]![enddate]));

(there are five queries that do this, one for each week)

QUERY qselMtgsWeek5: (the 5th week is slightly different)
SELECT DISTINCT IIf([week]=5,[mtgdate]," ") AS Week5
FROM qselMeetingDates;


QUERY qselMtgsbyWeek:
SELECT DISTINCTROW qselMtgsWeek1.Week1, qselMtgsWeek2.Week2, qselMtgsWeek3.Week3, qselMtgsWeek4.Week4, qselMtgsWeek5.Week5
FROM qselMtgsWeek1, qselMtgsWeek2, qselMtgsWeek3, qselMtgsWeek4, qselMtgsWeek5
ORDER BY qselMtgsWeek5.Week5 DESC;

the data from qselMtgsbyWeek is used by the subreport

It displays correctly for a month with only four weeks. However in a month with five weeks, it displays two rows of data, one on top of the other, in the subreport. This is because there are two records that come up in qselMtgsWeek5 data - one with a space and one with date. I can't have it filter out the blank one because then it won't display the dates for any month with only four weeks.

I'm attaching a PDF copy of the reports. I zipped the database and attached it, too.

Let me know if anything else would be helpful. Thanks so much.

 

[CraigDolphin]

Changing the sql of your qselMtgsWeek5 query to the following should solve the problem.

SELECT DISTINCT Nz(DLookUp("MtgDate","[qselMeetingDates]","[Week]=5"),"N/A") AS Week5
FROM qselMeetingDates;

 

[ctieppo]

dlookup trouble

Thanks for your idea. I tried your SQL and I'm having trouble with it. The underlying query (qselMeetingDates) has criteria on it to filter based on a user-entered date range. When I use your SQL in the week 5 query, I get an error message that those date range fields can't be found.

I'm not very familiar with dlookup, could you please tell me the difference between doing it that way and my original query? Can I have multiple criteria, so that the week=5 and the mtgdate is between the start and end dates entered?

Thanks.

If anyone has other ideas, I'm open to anything that might fix this (or improve it).

 

[CraigDolphin]

Well it seems to work just fine for me. See attached db. Can you tell me what you're doing when you trigger the error?

And as for the difference between your original sql and the dlookup method. Your original method was to show records from your qselMeetingDates and if the week value was 5 return the matching date, and if it wasn't =5 then return a " ". If you open the query you got one line with a date (corresponding to the week 5 record if present) and one line with a " " corrsponding to the records that were not equal to 5. Because your query returned two results, you got two lines in your subreport. This is not an issue with your other week# queries because you do not use the IIF to test whether that week# exists.

Dlookup returns only one result no matter how many results are in the source table. In this case it looks for the date corresponding to week=5. If it can't find one that matches, the Nz function puts in something else instead (I used N/A but you could use Null, or " " or whatever you like.

 

[ctieppo]

hey, that works!

That's freaky, it works fine if I run the actual report. I got the error message when I tried to run just the week 5 query, since I hadn't entered the dates it freaked out. Sorry, I should have tried to run the report. DUH!

Thanks very much, report works great now.

Any ideas how I can fix the averages on the same report? We occasionally don't have a meeting due to holidays (like no meeting on 7/5), but the totals still show zero and are counted. I think I need to change it so that if there is no data, it stays blank for that week, then the totals will be nulls and the average calc won't include it. I'll have to change the denominator in the average calc to be calculated (possibly use iif on previous week, if null, don't add one, if not null add one).

Any suggestions? Is my thinking along the right lines?

Thanks again!

 

[CraigDolphin]

Sorry...I've never been fond of reports and how the controls work on them. Maybe someone more experienced can chime in. The best advice I can offer it to try and do as much of the work in the source query as possible. For example, it's easy enough to get the GWk1-5 values to be null if no meeting occured using an iif statement

eg...
iiif ( meeting occured, your existing iif statement, Null)

could be represented by...

IIf(DSum('[1]','[qselAbsenceSumbyMember_Crosstab]')>0,IIf(IsNull([Give1]),0,[Give1]),Null)

But getting those 'total' controls on the report to work requires testing for nulls and from what I can tell, deeply nested iifs that cause my head to hurt.

 

[ctieppo]

thanks for your help

Thanks for your help with the report.

I'll keep trying on the averages. I agree, nested iif statements give me a headache too...