Help With Time On Report

[CharlesWilliams]

Hello Access Experts,

I have a report that is grouped by the day and lists the salesman across the top and times down the left side. What I would like is to have all the times from 9am-9pm down the left side for each date weather there is a lead assigned or not for that salesman.

I cannot get my head around how to do this. I've seen calendar databases that do this but I cannot look at the way they do it because it is in an MDE file.

I've attached a scaled down version of the database for your review.

Any help would be greatly appreciated.

- Charles Williams

 

[PaulO]

Presumably you are feeding the report with a Query, not a Table. You'll need it to be a Query, which if created by Linking a Calendar/Times table to the Appointments table should be able to throw about an appointment for each slot whether full or not. To achieve this the Query needs a LEFT INNER join between the two Tables, this will force a blank entry to be assigned to the each vacant time slot or ID.

PS: I see you are storing Appointment Start Times in DIFFERENT Formats between tblTimes and Lead-Appointments ... that's a real no-no ... so straightening this out needs to be an immediate objective.

OR (perhaps a better idea)

You could instead Store values 1-12 (IDTime from tblTimes) in the Appointments Table rather than an actual Start Time. This will make for easy linking of the two tables in the Query, and the output you desire.

 

[CharlesWilliams]

PaulO,

Thanks for the response. It is fed through a query. I will try to use the calendar and set it up with a left inner join.

I will also change the time in the table (good catch)

Also, the database I attached is a part of a much larger database (80,00+ records). It would be too tedious to change all those records.

I'll let you know how it goes.

- Charles Williams

 

[PaulO]

Hi Charles

80000+ records sounds horrid but only if you're considering a manual update ... the reality if you use an UPDATE query is that it's a very small task. So, create an UPDATE query on Lead-Appointments table to create fresh Data in a extra new field called IDTime based on what's already in the APPT_TIME field e.g. where APPT_TIME = 11:00:00 then enter 3 in the new IDTime, etc etc.

The query will update the whole table in seconds, then you're free to link the two tables using the IDTime field that now exists in both tables.

 

[JHB]

Is that what you are looking for, se picture:

I've attached the modifed database, you have to run the "MakeTableTimeAndDate" query first and then open the report afterwards.

 

[CharlesWilliams]

PaulO - Yes 80,000+ records. You are correct running an update field would do the trick.

If you where to see the whole db it would make you scream. I was rushed to create the db and my boss didn't really care about normalization.

JHB - That was exactly what I was looking for. Thanks!

 

[JHB]

You're welcome.

Did you read my signature?