Help with database

[JamieW]

Hi,

I'm designing a database but it is turning into a bit of a state.

I Understand that my approach is probably poor but i am fairly new to Access. and realln need some advice/help

All other programs i have written have used inputs from list and combo boxes to wirte to a table. This is the first that reqires me to manipulate data already stored in a fixed sized datasheet.

I have two tables: tblMainProgressList and tblLog

The database is used to check the status of different 'line numbers' in a construction project. like a time line; when all boxes have been ticked, the project is complete!

the different Status's have been named STN1,STN2,STN3.. etc (Station1,2,3..) This will enable the program to be migrated to a different project by simply changing it's name instead of digging deep into and changing lots of code.

So far, when i click any STN tickbox, the reletive STNDate will be assigned with the current date. and likewise removed if the STN is unticked. (all from the Afterupdate event of the subform)

I have managed this with a large bunch of udate querys in SQL. However, this is just a prototype; the final design will have nearly 30 different stations! This means a huge amount of update querys that will probably make the operation of the program very slow.

surely there's a better method to handle this? (i'm all ears )

In a similar way, each time a STN is ticked, a reletive field (STN1Val, STN2Val...etc) is given a load value (percentage of project completion). A TOTAL field adds all STNVals to produce the total percentage complete.

Now, This all works (although the coding will probably be laughed at).

but my next taks is to send the data to a log each time it is updated:

So- for example . if STN1 is ticked on line-20000

i would like the last record in tblLog to store the data:

| Line No | Name | Date/Time | Total Load |
| line-20000 | PIDNoRev | 06.11.06 12.00 | XX |

where name is the name of the station that was ticked.

I really need some advice on how to single out the STN that i was ticked last, and write it to the name field in tblLog . If this is solved i could work out the rest.

Also any help on my update query issue will be greatly appreciated..

I have attached the database to make all this a bit clearer (hopefully)



Many thanks, Jamie

 

[123James]

Im afraid im not replying to help you with your problem but thought you might be interested in looking at a piece of software called Microsoft Project.

It does everything you require and much, much more, including allowing you to transfer your project to other software and systems.

 

[JamieW]

Thanks for the reply james, I am aware of that program but this really needs to be designed in Access. There will be more functionality added to this project that Microsoft Project cannot handle and also the current databases need to be imported from excel.

Any other advice?

Thanks, Jamie

 

[gemma-the-husky]

are you clicking stn check boxes on a from or in a table

if its on a from, then it gets relatively easy - if its in the table then

a) it probably shouldn't be! and
b) its hard or even not possible!

 

[JamieW]

Gemma,

The Checkboxes are held in a datasheet subform that is linked to a table.

take a look at the .zip ^^ if you have time.

Am i destined for failure this way then??

 

[gemma-the-husky]

no if its on a form, then in the after update event of the checkbox (probably best, but you choose where), you can open a text log file, and append whatever data you need to the log file. (look at open, append etc methods for dealing with text files).

If you do it in a table, you can't get at after update events etc, so its virtually impossible.

 

[RuralGuy]

Hi JamieW,
After looking at your sample, I would strongly advise you look into normalization of your tables. You will continue to encounter grief the longer you work with the current structure. Search this site on normalization for some examples. The logging function you want can be looked at as simply an Audit Trail. Here's some links to get you started.
Fundamentals of Relational Database Design
Rules of Data Normalization
Creating an Audit Log

 

[JamieW]

Thanks, i really appreciate the heads up... as i expected really. It was all getting far too complicated

I'll have a good read though the examples and tutorials that you linked.

cheers, Jamie

 

[RuralGuy]

You're very welcome. Once you get the tables normalized you will be amazed how simple some tasks become.

 

[JamieW]

Right, after doing a fair bit of research on the Normalization process and relationships i have started my whole layout again.

I have also simulated the audit log RuralGuy posted as an example. now, am i right to believe that to have the audit log functioning correctly, you need to scrap all of the primary keys etc?

If so, doesn't this make the whole point of me normalizing my tables redundant?

I'm a bit unsure of what step to carry out next.

any help would be great

thanks, TriO`

 

[RuralGuy]

...now, am i right to believe that to have the audit log functioning correctly, you need to scrap all of the primary keys etc?

NO! Why do you feel you have to scrap the PKs?

 

[JamieW]

I think i was getting mixed up with the temp table which cannot have a primary key when copied.


But, Allen brownes audit example says 'each table to be audited must have an AutoNumber primary key; '

Is this just for the sake of the Audit log? or can these be the Primary keys already being used in my tables? if so, must they be Autonumbers?

I'm still a little confused

cheers, Jamie

 

[RuralGuy]

It has been a while since I implemented his Audit Trail and he has changed it a bit. The reason he wants AutoNumbers is because he uses LongIntegers to keep track of what records are being modified. I haven't tried it but I don't see anything in the code that requires the AutoNumber field be the Primary key of the table being logged. In order for the Log to function it must refer back to a specific record in the table with a unique value (such as an AutoNumber).

 

[JamieW]

Thanks again Rural, I'm going to test a few methods and then post back

Jamie

 

[JamieW]

Right, I've set up my database with new tables and relationships into what i believe to be a much better and more efficient layout.

I've attached the database below. What do you think, am i on the right track?

I haven't started the audit yet but will do soon.

My problem is that when i query the fields of interest and edit one of the check boxes, all the records with the same StationID as the checkbox picked will change.

I want to be able to just change the state of the one i picked and store it in that record

Is this due to the way i have set up the relationships or am i just missing something really obvious?

many thanks, Jamie

 

[JamieW]

Problem solved,

My status checkbox needed to be in my main table instead the station table...

Still would like any opinions on the new relationship layout.

I understand this is probably very simple for most people but i'm just getting started.

thanks, Jamie