Work Database (1 Viewer)

[kruptminz]

Hello,
I hope this thread finds you all well. So I have been exposed to almost all aspects of Microsoft Office since 1996. Access, however, is my grail of sorts. I accepted a project to streamline our recordkeeping and general day to day operations within my office. That being said I will probably be posting many threads, each introducing a new chapter of this project. Please keep in mind that I have only been doing VBA coding for 2 weeks now, and am reading a 916 page text book on Access VBA.... Pretty much teaching myself at this point... :banghead:

So problem one:

I am working on designing a work schedule. This schedule is atypical of a normal schedule. Dates on top, People's names on the sides, and positions worked in the middle. the first issue that I am having is I am trying to have the dates appear in the top Fields boxes, and every month the dates would automatically assign next months dates. I want to set this code so that I will be long dead before anyone has to change anything. What I have so far is... well... my 11 year old son could probably do better...

Option Compare Database
Option Explicit
Sub CreateTableDAO()
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim MyDate

MyDate = #5/3/2013#

Dim varMonth As Variant

varMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Do Until varMonth = 'Don't even know if this is relevant, and don't know what to do to make it loop...


Set db = CurrentDb
Set tblNew = db.CreateTableDef("Schedule")

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 1
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 2
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 3
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 4
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 5
tblNew.Fields.Append fld
Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 6
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 7
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 8
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 9
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 10
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 11
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 12
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 13
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 14
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 15
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 16
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 17
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 18
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 19
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 20
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 21
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 22
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 23
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 24
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 25
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 26
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 27
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 28
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 29
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 30
tblNew.Fields.Append fld

Set fld = tblNew.CreateField("Date", dbDate, 10)
fld.DefaultValue = MyDate + 31
tblNew.Fields.Append fld

Loop

End Sub

I have not finished it yet, and I jknow there has to be an easier way to do this, is there an easier code that I can put in to acheive the desired effect? I need every date of the month to show up in separate fields.

thanks

Eddie

 

[spikepl]

You need to do some reserach on the subject of normalization - use google.

A relational database is NOT a spreadsheet. Specifically, it means that the way you store data is not influenced by how you input it or how you display it. The governing "light" is data normalization. There is no "normalization police" though - the built-in penalty of unnormalized data is a huge PITA when setting up queries, forms and reports.

I forgot the "reality check": What is so unique about your business that you are willing to spend countless hours on developing something that deals with the pretty mundane tasks of almost any company, so it consequently can be bought out-of-a-box for not much money (compared to the self-flaggelating development exercise)?

 

[kruptminz]

I did as you suggested and googled normalization. pretty straight forward. I do see that I need to tidy this up quite a bit, which is why I have asked for assistance from experts (you guys), and not purchased an off the shelf kit. I would like to learn how to do this. This fascinates me. I would like my boss to come into work, and when she gets a phone call from an employee saying that they can not make it into work she can find a replacement in 15 minutes using this database versus 2 hours using a printed work schedule, phone roster, word of mouth, senority, and manually calculating hours worked. Access is my Grail. I would like to learn and enjoy working this. Is there a way to noramlise this first problem?

 

[Galaxiom]

I did as you suggested and googled normalization. pretty straight forward. I do see that I need to tidy this up quite a bit,...

Ironically, Excel power users with many years of experience often have the biggest problem with Access because it is so completely different from a spreadsheet despite the superficial similarities between a worksheet and a table.

Sorry but it needs more than a "tidy up". It is a start over scenario.

Go back and read about Normalization again until you understand it in the context of what you are trying to achieve. Then put together some tables. Focus on the relational structure of the tables.

Forget about VBA for now. You are trying run before you can walk.