Listing headings where no data exists

[AdamO]

Hello,

I have an appointments database. It enables appointments to be booked with start and end times. Appointments are shown by selecting the day from a calandar object in a form. A report shows appointments for a given sales person by a date range selected.

The user has now asked for a report to show a date heading where no appointments exist. For example, if a weeks appointments are selected and there are appointments for every day except Wednesday, at the moment all appointments are listed for all days except Wednesday. They would like a heading for Wednesday in the report.

I cannot see how this could be achieved and would appreciate any guidance.

Thank you.

 

[TessB]

The answer lies in your query.
Make a seperate table or query where the days of the week are defined.
In your existing query, make a field that identifies the day of the week where the appointment exists.
Join the two queries on Day of Week, with a relationship saying Show All records in Day of Week query exist and only those from the appointment query.
Then base your report on this query and group by Day of week.

Do you understand this?

 

[TessB]

I just realized you would want to put a "Sort By" number in that table as well so that when you go to group on the report, it won't group the days alphabetically! You'll want to Group on the Day number field, and display the Day Name.
So choose Day Name and Day number for that query as well.

Have you tried this yet or have you gone home and started your weekend already?

 

[AdamO]

Thanks for your reply.

I have been trying a number of options without success so far.
In your reply you mention making a table where days of the week are defined. Am I right in assuming I would have to manually enter dates for the next five years of so in order for the join query to work.
Apologies if I am not understanding your solution.
Thank you.

 

[DBL]

I think Tess means for you to do a table where you can enter:

1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

And use this table for your query, sorting by the number rather than by the day name.

 

[TessB]

Thank you DBL for taking care of that reply for me. I just got back from a long weekend.
Yes, that is exactly what I meant.
AdamO, were you able to implement this solution?
I won't be able to respond this week during business hours as I will be on an assignment that will not have internet access to this site. However, if you still need help, DBL seems to understand the solution and may have more suggestions.

Thanks.
Tess

 

[AdamO]

Hello,

Thank you for your replies. Apologies, I believe I may not have made myself clear from the outset.

The database enables appointments to be entered. The field supports a date being selected. There will be some days where there are no appointments. When I run a report (by date range selected) for a salespersons appointments, all the dates where appopintments have been entered will be shown. What is not shown is dates where there are no appointments because these dates are not stored as values in my database.

I am looking for a solution which will show these "empty" dates, if this is possible.

Any help would be very much appreciated.

Thank you for your patience.

 

[TessB]

You were perfectly understood. You want the report to display headings for Sun - Sat whether or not appointments exist.

Allow me to outline the steps you must take to achieve your goal.

Step One:
Create a table named tblDaysOfWeek
Include in this table two fields:
DaySortOrder Numeric (Integer)
DayOfWeek Text (length 9)

Step Two
Enter into this table the following data
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

(Use this if you want Sunday to be your first day of the week, of course)

Step Three
Modify the current query upon which your report is based to include this field:
DayOfWeek: Format(YourAppointmentDatefield,"dddd")
Save your query

Step Four
Create a new query.
In this query, add the tblDaysOfWeek and the previously saved query.
Join the two as "Show all records in tblDaysOfWeek and only records in (Whatever your query name was) where DayOfWeek = DayOfWeek.

Select all fields of the previously saved query for your new query, as well as DaySortOrder from the tblDaysOfWeek.

Save this new query.

Step Five
Change your data source of the report to your new query.
Add to your report a grouping level (the first grouping level this will be) and group on DaySortOrder.
You can delete or hide the actual field [DaySortOrder] (your choice)
Add to this header [DayOfWeek]
Save

Run your report now.

Trust me!
Tess

 

[Rich]

Create a table with dates in for as many years as you need, then use a Union query to select the missing dates

 

[TessB]

My way would only be useful for one week date ranges starting Sunday through Saturday.

 

[AdamO]

Create a table with dates in for as many years as you need, then use a Union query to select the missing dates

I am still having some problems unfortunately. I thought the requirement was no longer needed, but I have just hear it is.

I have an SQL as follows:

SELECT tblAppointments.ApptID, tblAppointments.ClientID, tblClients.ClientPCTitle, tblClients.ClientPCForename, tblClients.ClientPCSurname, tblEPCAgents.EPCAgentID, tblAppointments.EPCAgentName, tblAppointments.ApptDate, tblAppointments.ApptStartTime, tblAppointments.ApptEndTime, tblAppointments.ApptStatus, tblAppointments.ApptOutcomeComments
FROM tblClients INNER JOIN (tblAppointments INNER JOIN tblEPCAgents ON tblAppointments.EPCAgentName = tblEPCAgents.EPCAgentName) ON tblClients.ClientID = tblAppointments.ClientID
WHERE (((tblEPCAgents.EPCAgentID)=[EPC or Agent ID]) AND ((tblAppointments.ApptDate)>=[forms]![frmReportDateRange]![BeginDate] And (tblAppointments.ApptDate)<=[forms]![frmReportDateRange]![EndDate]) AND ((tblAppointments.ApptStatus)<>"Unavailable"))
ORDER BY tblAppointments.ApptDate
WITH OWNERACCESS OPTION;

I have created a list of dates qryDate which has a field ApptDate for all dates in the next 5 years. I cannot seem to Union this. Is it too complex to achieve?.

 

[Rich]

Post a 97ver with some dummy data in

 

[AdamO]

Hello Rich,

Thanks for getting back. I thought I was getting to a desired result.

I created a table tblDates with dates for the next five years. The field name is the same as the date field in my appointments table tblAppointments. I ensured the relationship between tblDates and tblAppointments was a ‘One-To-Many’.

In my query I want to select for a given period a name of a sales person. To include missing dates I placed IsNotNull in the date field criteria where I also request a ‘to and from’ date. Unfortunately the IsNotNull will include other sales people. My database is in Access 2000 however I am attaching a screen print of the query and hope this shows my attempts.

Any guidance would be appreciated.

Adam

 

[Rich]

I think you use the export to option to get a 97ver, I don't have A2K

 

[abezuide]

Hi,

I just had to do the same thing on a report. I did it by doing a union query, the first part of the union will capure all the stuff within the daterange, the second part selects all null except for the date field and the where clause selects all the dates where not in the daterange

Hope this helps

 

[abezuide]

Oh ya, and the second part of the union does not need to be as complex as the first, as long as you have what you need in there and the selected fields should be the same amount. You can select null where you don't want a field to show up

 

[AdamO]

Thanks Rich & abezuide for your replies.

Rich, unfortunately the export option does not give me an option to save to a lower version.

Abezuide, I have no experience of Union queries but will try. If you have an example it would be appreciated. I am enclosing my stripped down database anyway and if I am on the wrong tracks could you let me know.

Thanks to you both.