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:Advertise rID, Company Name, Person In charge)
Order Table (Field:PressOrd er ID,AdvertiserID , Ad Size, Rate,)
InsertionDateTa ble:(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
(...) InsertionDateTa ble:(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_paper name]
[[newspaper_other detail]

And rework InsertionDateTa ble (I'll slightly rename it here so as not to get it mixed with the OP version):
tbl_InsertionDa te:
[InsertionDate_P K]
[InsertionDate_D ate]
[InsertionDate_F K_PressOrder]
[InsertionDate_F K_NewsPaper]

Now one should be able to query against the [InsertionDate_D ate] in tbl_InsertionDa te 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 InsertionDateTa ble 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!