Calendar Thread - Where does the Code tell the DB to put Info in "dayblock"

You refer to a flat file database. Did you mean that as this is an Access forum?

Sorry NeoPa, I suppose I was indicating that my db is super simplistic. I have lots of info tied to each entry.

My guess is that your database and your understanding is not as basic as some we've seen, but we'll probably see that in time. I'm happy that we're talking about Access anyway so no problems there. I've given ADezii (two 'i's no 'l') a prompt so hopefully he'll see this when next he's online. He'll probably understand your reference better than I can and certainly understands the database better (having been the main architect - although not the original as he's always at pains to mention).

Thank you, on both counts. I did notice ADezii's notation in code about it not being his original design, but either way he's done amazing things with it.

The best way to explain how the individual Date Blocks get populated in to show you a segment of the Code contained in the PopulateCalendar() Sub-Routine. Lines 18 and 21 populate the Day Blocks from an Array with the Time on the 1st Line, then the Last Name & First Initial & . & a Single Character Code Value. The only difference between these Lines is that Line #18 will populate the Block if no Text previously exists in the Block (via Line #17), whereas Line #21 adds a Carriage Return/Line Feed before doing so (Text exists). Any other questions, feel free to ask.

NtaCLuNm9Hd:
I did notice ADezii's notation in code about it not being his original design, but either way he's done amazing things with it.

No argument there :-)

Aha, I searched for some of the text you show me to find it in your/mine and I don't find any of the code you are showing me. I just downloaded your Calendar from the front page last week, but you appear to have a newer version. I do somewhat see what you've pointed out. I get the carriage return and the 'first' and 'last', but it would help to see it live. Is your updated version posted here for me to look at? Thanks a lot to both of you for answering so quickly. I've tried to figure it out on my own for about a week, before I succumbed to aking questions. That way I know I've exhausted all my avenues.

Wow that first line makes no sense. I meant to say....I searched for some of the code on your line 18 or 21 and it doesn't show up in the one I downloaded from here last week. ;-)

It won't show up on your DB, since I used an earlier Demo that just so happened to have a Time Field and additional information. How about providing some basic info such as your Table Name, Fields along with their Data Types, and sample Data for October 2011. I'll then create a simple Demo for you to learn and study. I suggest this approach since the MS Access Calendar can be a little daunting for a Novice, and a Picture is worth a Thousand Words!

Alright, thanks! My main table is 'Projects', with fields:
ProjID
ProjName(txt)
ProjNbr(txt)
ProjCity(txt)
ProjSt(txt)
METravel1(txt-combo from a query First and Last Name)
METravelDate1(dt/tm)
[3 total of above only 1, 2, and 3 at the end]
ELTravel1(same as above)
ELTravelDate1(same as above)
[3 total of above only 1, 2, and 3 at the end]
FPTravel1(same)
FPTravelDate1(same)
[3 total of above onle 1, 2, and 3 at the end)
I'm not sure how to get you sample data or if what I've given you above is what you want to see, but I'll try to see if I can explain what I'm trying to do. I have 3 sets of different people that I have in 3 tables, one for ME, one for EL and one for FP. There are up to 9 people that will travel over a course of months for each ProjID entry that I want to show on your Calendar. Then when someone clicks on the date, it shows up all the info in the Proj table, such as city, state, etc. How should I get you some data to populate my 'picture'? You are right about daunting, but it's fun to try and have it succeed!

I'm a little confused as to your explanation, so I'll simply state what I feel is correct, and you can let me know one way or the other.

1. You have a Table consisting of Project related Data.
2. It would appear as though you have a seperate Table for each individual, is this correct?
3. You can have up to nine individuals, designated by two-character codes (ME, EL, FP, etc), who can travel any specified number of day(s) during a given Month, for a given Project.
4. The only other information relevant to these individuals, other than their two-character codes, are their Date Ranges that they travel as related to a Project. Sample Data posted (November 2011):
   
   Expand|Select|Wrap|Line Numbers

   1. ProjID      Person      Start          End
   2. 1             ME        11/2/2011      11/7/2011
   3. 1             EL        11/5/2011      11/15/2011
   4. 1             YZ        11/30/2011     11/30/2011
   5. 1             OP        11/12/2011     11/20/2011
   6. 1             TY        11/17/2011     11/21/2011
   7. 'Other Data to follow for additional Projects
   8.  
5. The Calendar should ideally display the Individuals, and the Projects that they are on for any given Date.
6. Clicking on a Day/Date should display all Project-related information in a Listbox below the Calendar.
7. Before we move on, I need to know how accurate this depiction is, and if not, for you to correct me.

Thanks ADezii.
1. Correct
2. Seperate tables for EL, ME and FP with 9 different people in each table. ID, FName and LName for each table. (I had thought about combining them in one table with a case for a designation of ME, EL and FP but it was easier for me at the time)
3. ME table - 1-F.-Flintstone (ID, FName, LName with 8 or more names). The same for EL and FP. Yes to the travel throughout the months for each project, but no travel range(I hadn't thought of that, but it might be needed later, typically I show just one travel date for each person)
4. Please see #2 and #3
5. This is correct
6. This is correct
I've stripped down a version of my Access 2007 db that I could send you if this would help to explan more, but at any rate, the information above is correct. Thank you.

While I'll always see any updates I don't plan to interfere too much in your conversation. However I might post relevant stuff where I can without diverting the main thrust as it were. In that sense I would just suggest an article you may not have seen yet (Database Normalisation and Table structures). It pertains to some of what you were talking about and is something every database developer should understand as it's so fundamental to all things RDBMS. You're clearly an intelligent individual and I suspect this will help you greatly.

PS. If you're interested in attaching a database then see Attach Database (or other work).

1. First and foremost, please heed NeoPa's advice as given in Post #14, as these are truly words of wisdom. Trying to incorporate your Data into the Calendar, while it is not Normalized, is definitely not recommended. I'm sure that I can speak for NeoPa in stating that both he and I would be more than happy to assist you in this area.
2. What, exactly, do the ME, EL, and FP Tables represent? The storing of info relating up to 9 individuals (ID, FName, LName) per Table seems redundant to say the least.
3. A scaled down Version of your Database, in 2003 Format, 'would' be most helpful. Remove any sensitive information, and change the actual names if required.
4. I hope that you are in no hurry for results, since I have a feeling that there is not going to be an easy solution, and that an actual restructuring of the Tables may be called for.
5. You have NeoPa's attention so you are definitely in good shape - there is none better.
6. Waiting to here from you.

Thank you so much NeoPa! I always appreciate any and all input...wanting to learn is my priority, but learning correctly is paramount! I will check out the article, and thanks again for your input.

NeoPa and ADezii, I will read through the article and work it into my db as best I can. I'm sure I'll be back with questions about Normalization and Structure as I go, if you all don't mind.
1. Will do!
2. ME, EL and FP represent 3 groups of people(disciplines)
3. I'll get you a stripped down version in 2003 (I have to pull out some 2007 items first before it will translate)
4. I'm not in any hurry. I actually enjoy the process of getting 'there'.
5. I've been researching answers all along the way of building this and I always kept coming back to this site because you all always had the best advice and solutions. ;-)

Since ME, EL and FP represent 3 Distinct Groups of people(disciplines), can an individual belong to more than 1 Group, namely:
Can John Doe belong to the EL and FP Groups?

John Doe isn't supposed to belong to EL and FP, but I've already had instances where John was asked to do FP work and had to be added, so now he resides in both EL and now FP.

A separate Group table ([tblGroup]) might be called for here then, as well as an EmployeeGroup table ([tblEmployeeGroup]) to link them together. Consider something loosely based on (for illustrative purposes only) :

Table = [tblEmployee]
Table = [tblGroup]
Table = [tblEmployeeGroup]
PK = Primary Key and is a unique identifier for each record.
FK = Foreign Key. This indicates the record in another table that this record is associated with.

So, GroupName in tblGroup becomes ME, EL and FP designators?

That NeoPa is always a step ahead of me! (LOL). The reason that I asked the question in Post #18 (an individual belonging to Multiple Groups) was to see if we would have a MANY <==> MANY Relationship between Employees and Groups. After you replied to the question the answer becomes yes. MANY to MANY Relationships are not directly supported in Access except by the use of an Intermediate/Connecting Table, which was so eloquently illustrated by NeoPa. The concept is difficult to envision for some, which is why I created a Graphical Representation depicting the Relationships between the three Tables. Download the Attachment and Open it to see what I mean. Also, run the Query qryDemoJohnDoe (tricky, isn't it) to now see how John Doe can belong to two Groups (EL and FP) while still maintaining Database Integrity.

The next, logical step in this process would be to incorporate the Projects Table into the Relationships Schema.

Have fun...

Thanks all! I think I was sort of getting the concept that NeoPa was leading me to in post #20 and your attachment brought it 'home' for me. So if I follow all this through...then would I have an FK-EmployeeID-Numeric in the Projects table in order to see the employee on a form with the project info? I'm still working through the 'Normal..and Struc' and I'll try out the 'Employee' items you both have outlined for me.

I've gone through the steps outlined in NeoPa's posting #14. I've gotten rid of the items that were 2007 only, but I still can't get it to translate it to 2003. I've also stripped out a fair amount of info and changed any pertinent info. I'm going to attach it, but I'll still try to figure out what's keeping it from translating. Thanks for the help.

I've added the 3 seperate tables you both illustrated, and it works! I'm pleased. I don't know if it's 'proper', but I changed the EmployeeID and GroupID to read out 3 and 2 columns, respectively (0;0;1") to show the last name and two-digit code instead of a number. It was easier for me to deal with, but I can change it back if that's not correct. I'm still working through the 'Normal...Struc' to try and apply it to my db. regards

What is needed now is a Projects Table (Projects) and an Assignment Table (tblAssigned). The Projects Table will Link to the Assigned Table via [ProjID], and the Assignment Table will Link to the Employees Table via [EmployeeID]. The Revised Attachment will illustrate these new Relationships.

1. Execute qryEmployeeGroups to see to which Group(s) each Employee belongs.
2. Execute qryEmployeesAssignedToProject1 to see to which Employees are assigned to Project1, along with their Start and End Dates.
3. I think that you hve enough to keep you busy for awhile! (LOL).

No doubt here, on keeping me busy with this ;-) I'll check back when I get these new items implemented. Thanks!

(LOL) Foghorn..."That's mathematics, son! You can argue with me, but you can't argue with figures!" Sorry, I just kind of had to. I won't do it again. ;-)

: - d

I thought it over very carefully, and broke a Cardinal Rule allowing Multiple Entries in a Single Field. I added a [Group] Field in the Employees. This Field will contain any one of three Values, namely: EL, FP, and ME. In rare occurrences, as indicated by the OP, an individual can actually belong to two Groups, and in this case (John Doe), a Delimited Value will be entered into the Group Field (EL,FP). Although not usually recommended the advantages are great:

1. Greatly simplifies the Relationships. By eliminating tblGroup and the Intermediate Table (tblEmployeeGroup), there will only be three Tables involved in Relationships.
2. The Delimited nature of the rare, Multiple Entries makes this Field easy to Parse should this become necessary.
3. This will greatly simplify the SQL that is required for properly displaying/incorporating the Data into the MS Access Calendar.
4. In my humble opinion, the benefits gained will more that outweigh the costs involved.
5. All opinions are, of course, welcome.

It seems to me a perfectly valid option.

If the data stored is simply the names and no other group data is stored along with it then why not.

I made the structural changes indicated in my prior post (#29), entered some Test Data into the Tables (now 3 instead of 5), modified the Calendar Code in order to integrate the Data into it, and made the Calendar operational. All this is included in the latest Attachment listed below. This should keep you busy for awhile (LOL).

Ok, all, thanks. Wow, it's a beautiful thing!
Before I adjust my changes to this new layout, I have a question from your post #29 to NeoPa. I can in a general way understand your points 1 thru 3. My question comes in after looking at the Calendar. John Doe on 11-2-11, who might perform an EL and FP function, probably wouldn't do them on the same day. To me what the subform is illustrating is that John will perform both the electrical and fire protection checks on a building on that day, instead of what I think it's indicating is that he's a member of both EL and FP(which he's not). He probably wouldn't perform both checks that same day, but he might occasionally. More than likely he would perform it on a different scheduled date for one of the FP team. They will sometimes share responsibility to save travel expenses..it doesn't happen too often. Does that make sense and should I go ahead with the changes?

From your Reply in Post #17:

2. ME, EL and FP represent 3 groups of people(disciplines)

It is now my impression from your last Post, that EL (Electrical), FP (Fire Protection), and ME(????????) are actually Checks and not Groups/Disciplines which blows the lid off of everything. The entire concept of a Date Range appears to be no longer valid, because you would need a specific Value (EL, FP, or ME) for each Day of the Range along with an individual Name. If all the above is correct, you would need every Date listed in the Assignment Table along with the individual involved, and the corresponding Check Code. You must clarify this issue before going any further.

ADezii, I apologize if I've misled you. Sometimes it's hard to accurately describe something in text while not knowing the correct verbiage to use.
Let me try...John Doe is in the ME(mechanical) group and he is going to Project A, #1234 on 11-5-11 for his first mechanical check of the bldg. FP and EL don't have their first observations until later that month. This time around John Doe has to be at this bldg for two days. Two weeks later Fred Stone in the EL team goes to the same bldg for his first electrical check of the bldg and now ME is ready for their 2nd check, so they ask Fred Stone if he will also look over the mechanical work while he checks out his electrical. Fred stays for one day. Two weeks after that, Betty is ready for the first FP(fire protection) check of the bldg and John is ready for his 3rd and final mechanical look so he goes with Betty to the bldg.
We now have:
1. John (ME) visits Proj A #1234 for 1st time, two days
2. Fred (EL) visits Proj A for 1st time and performs 2nd ME visit for one day
3. Betty (FP) and John (ME) visit Proj A for 1st (FP) and 3rd (ME) time, respectively, for one day
4. Fred and Betty will each visit Proj A two more times at later dates. They could go together or seperately.
I hope this helps clairify how I plan to show info in this db. I'm still working on trying to figure out why I couldn't translate my db from 2007 to 2003...I followed the instructions, but I'm sure I'm missing something. Still trying...

This is the way I now 'literally', not structurally, see things. I need to know what, 'exactly', is wrong with the following scenario before I proceed any further. Kindly Post what the Data should look like, given the scenario in your prior Post.
P.S. - Do you need both Records for Fred, or can the one where he is listed as EL for 11/7/2011 be eliminated?

ADezii, your layout in prev post is correct. Fred, who is Electrical, is also performing the mechanical observation, so both would be needed. Question: So from your layout are you thinking of dispensing with the start and end date for those entries where the travel time is over one day?

are you thinking of dispensing with the start and end date for those entries where the travel time is over one day?

Yes, the way I see it there is no alternative. The Range is spanned programmatically, there is no mechanism that I can see whereas you can plug in EL for one day and ME for another for Fred. Give me a few days to sort things out and see what I can come up with.

NtaCLuNm9Hd:
I have a question from your post #29 to NeoPa.

I guess ADezii realised that was for him too. I was somewhat perplexed for a while, but I can see that all is progressing smoothly between you two, so all seems good.

Thanks to ADezii and your patience!!

I have put together what I feel is a Demo that truly resembles what you are looking for. Download the Attachment, pay attention to every detail, and should you have any further questions, simply ask.

Thanks ADezii! You've been busy. I'll check it out. I've been reading through the infomation about Normalizing and Structure that NeoPa and you suggested. I'm making some headway in understanding. ;-)