Create a calendar-view

[henry1988]

To Any People who kind in help,

Currently I am trying to create a Booking Database for my department (advertising). Is it possible to show the record in Calendar View?

My Table are as below:
AdvertisorTable ( Field:AdvertiserID, Company Name, Person In charge)
Order Table (Field:PressOrder ID,AdvertiserID, Ad Size, Rate,)
InsertionDateTable:(Field: Press Order ID, Ins Date For Newspaper A, Ins Date For Newspaper B, and Ins Date For Newspaper c)

Is it possible to create a calendar to show the Insertion Date of the Newspaper A,B & C and the Advertiser's Company Name?

Very thank you if anybody can help me to resolve this question as I has been searching this solution for 2months..

T.T

[Frinavale]

What technology are you using to implement your application?

[henry1988]

Hi Frinavale,

Thank you for your reply!
I am using Ms Access 2010.

Is it possible to do it?

[ADezii]

As soon as this Thread gets moved to the Access Forum, I am sure that we can help you. In the mean time, are these the correct Table Relationships for the Database?

AdvertiserTable.[AdvertiserID]{1} ==> [Order Table].[AdvertiserID]{MANY}
 

[Order Table].{PressOrderID]{1} ==> InsertionDateTable.[PressOrderID]{MANY}

[henry1988]

Hi ADezii,

Thank you for your replied!:') (touch)

Yes, the relationship was linked as to your perception.

Henry

[ADezii]

Let me see what I can come up with. The Calendar Database was designed to Map a single Date Field or Date Range (To Date...From Date) to a Calendar Grid. Off hand, I do not see how three distinct Date Fields (Insertion Dates for Newspapers A, B, and C) per Record can be mapped in this manner. A Hybrid approach may be needed - be patient and I will get back to you.

[ADezii]

For now, I wasn't able to modify the Calendar Database so that it will display all three Newspaper Insertion Dates at the same time. What I was able to do, was to to Open the Calendar displaying Insertion Dates, Company Names, and Order IDs for Newspaper 'A' with the option to dynamically switch to Newspapers 'B' and 'C' while re-populating the Calendar with the new data. If you are interested I'll give you the specifics, but I must advise you that the Core Code for the Calendar DB can be a little intimidating. Let me know either way.

[henry1988]

Hi Adezi,

Very thank you for ur help. I am ok with the calendar which showing 3 newspaper at the same time. i am a newbie for ms access. So i think the second option is not suiting me.......

Henry

[zmbd]

OP
(...) InsertionDateTable:(Field: Press Order ID, Ins Date For Newspaper A, Ins Date For Newspaper B, and Ins Date For Newspaper c)
(...)

Rabbit, correct me if I'm wrong, and I may very well be, it appears that this table isn't normalized and a slight tweek to the structure should make the Calendar Database hummm happily.

There should be at least one more table

tbl_newspaper:
[newspaper_PK]
[newspaper_papername]
[[newspaper_otherdetail]

And rework InsertionDateTable (I'll slightly rename it here so as not to get it mixed with the OP version):
tbl_InsertionDate:
[InsertionDate_PK]
[InsertionDate_Date]
[InsertionDate_FK_PressOrder]
[InsertionDate_FK_NewsPaper]

Now one should be able to query against the [InsertionDate_Date] in tbl_InsertionDate to find all of the press order information and which paper the insertion happened - One field, which, if I followed correctly in ADezii's Post (#6) should work - no?

I think I may be missing something; however, until I do this to the OP DB table design, it doesn't make sense to me.

[Rabbit]

@zmbd, you are correct, the data is not normalized and should not contain three different newspapers in one record.

[ADezii]

I now feel that the OP may be well over his head with this one hinting that Normalizing may not be an option at this time. On the other hand, understanding the Logic and Coding involved with the Access Calendar would be even more difficult. Not sure how to proceed from here.

[ADezii]

@zmbd & Rabbit:
I simplified matters for the OP by maintain a 3-Table structure but modifying InsertionDateTable in the following manner:

[InsertID]{PK}
[PressOrderID]{FK to Order Table}
[Insertion Date](DATE/TIME}
[Newspaper]{TEXT 1}[A, B, or C]
 
NOTE: Unique Index on [Insertion Date] AND [Newspaper]

The SQL needed to create a Recordset in order to populate the Calendar now becomes:

strSQL = "SELECT AdvertisorTable.[Company Name], [Order Table].PressOrderID, " & _
         "InsertionDateTable.[Insertion Date], InsertionDateTable.Newspaper " & _
         "FROM (AdvertisorTable INNER JOIN [Order Table] ON AdvertisorTable.AdvertiserID = " & _
         "[Order Table].AdvertiserID) INNER JOIN InsertionDateTable ON " & _
         "[Order Table].PressOrderID = InsertionDateTable.PressOrderID ORDER BY " & _
         "AdvertisorTable.[Company Name]"

Rather than rambling on, I'll Attach the Demo that I threw together that will display the Company Name and Newspaper designation (A, B, or C) on the Calendar with the actual Grid Date representing the Insertion Date.

[zmbd]

ADezii,
Wow, that {code is} something else! {I don't think I could have come up with it so easily!}

{As for the normalization,} IMHO, In the long run, it will be much better for OP to normalize the DB and would have made the calendar much easier to code.

Hener1988:
If you go to the insight articles there is one covering normalization (sorry, on a guest account tonight so I don't have my quick links). It is well worthy learning about as it will make your databases more efficient .

[ADezii]

In the long run, it will be much better for OP to normalize the DB and would have made the calendar much easier to code.

I could not agree with you more should the OP be minimally proficient with Database Structure and VBA Coding. My only reasoning is that the addition of a 4th Table to the overall structure would add to the overall complexity, but you are correct, of course, in your statement. Let's see exactly where this goes from here.

P.S. - What do you say, Rabbit?

[zmbd]

ADezii,
Wow, that's something else!

Just re-read that and thought, that could be taken wrong! :(

My intent was to express how impressed I was by the solution you arrived at... frankly, I don't think I would have arrived there within a week or two!

[Rabbit]

@ADezii, I think your new three table layout is fine since it normalizes the data. The fourth table is really only needed if they want to store additional newspaper data.

[ADezii]

@zmbd:The basic Access Calendar has been around for many years and was actually created by a friend of mine. Over the course of time it has gained much popularity, and with my friend's permission I sort of took it over and have adapted it to individual's specific needs. It was in a constant state of flux in that it continually was improved, modified, fine tuned, etc. Even Members of this Forum such as NeoPa and Mary have contributed to its evolution. It is at the point now where it essentially is a shell and can easily be modified for specific needs. I didn't want you to think that I simply 'whipped' this Calendar up on my own in a short time period - I am nowhere near that smart! (LOL).

P.S. - What you see in the Attached Demo is just part of the functionality that the Basic Access Calendar provides. Dbl-Clicking on any Date Grid (Text Box) would normally display a List Box at the bottom of the Screen (where the 3 Command Buttons are now). This List Box would contain expanded information relating to the Items within the Date selected.

[ADezii]

Thanks for your opinion Rabbit, it is highly valued.