DB size?

[The Stoat]

Hi Mike,

All arguments aside it is obvious that some parts of you database need "redesigning". I am not necessarily talking about the functionality i.e what the screens do but how the data is held. This will probably endup effecting how the data is presented and accessed anyway. I take your point that it works for you, and it is a big consideration changing something that is in place and you know how to use. However no one will touch this with a barge pole if it is presented to clients and it does to conform to the accepted standards for database design.

If you have an entity relationship diagram or a schematic for the current system that would be very helpful. I will PM you with my details. I will give you an honest review of your current setup and at the very least tell you exactly how this could be achieved using a normalized system. If i think there is some serious mileage in it I will design table structures and give you a story board breakdown on how the data can be entered and retrieved. If at this stage you are happy to go ahead then things are going to have to get legal

A simple fact of IT is there are a many ways of achieving the same goal. Access doesn't force you to use normalization. But it is designed to support a system that is designed that way. Normalization is platform independant. If i was designing an Oracle or SQLServer or any other database using a modern db paltform i would do a systems analysis to understand how the system fits together and once i had done that i would always normalize my data. Having done that any competent db developer could implement this on any platform.

I think what people are trying to say is normalization has been proved to be the most effective way of holding data. It doesn't mean that there are no other ways but like most things in life there are accepted standards so at the very least we can all speak the same language.

TS

 

[Mike375]

I'll make a one table diary system right now and post it for you to see.

Remember, I did say the appointments are in one table.

It is the display when making appointments that has a table for each salesman.

As I mentoned to Ian I could put all of that display in one table, but it slows things downs and has other disadvantages.

Mike

 

[Mile-O]

Remember, I did say the appointments are in one table.

It is the display when making appointments that has a table for each salesman.

As I mentoned to Ian I could put all of that display in one table, but it slows things downs and has other disadvantages.

I'd say it was tables bound to forms and the quantity of macros that was slowing it down.

 

[Mike375]

You need to lose the chip on your shoulder, nobody is attacking anybody!

I think it was Pat Hartman said "not picking on you" so I just added a bit of colour with "attacking"

You are over reacting.

But to come back to the matter we discussed I was able to post up how I sort across a row. Can you post up how you would you do it.

Mike

 

[Mile-O]

A properly designed database would not need to be sorted horizontally which is why there is no immediate option.

I'm guessing your data is of the structure Data1, Data2, Data3, Data4, Data5, which is a violation of the First Normal Form (1NF) of database design.

 

[Mile-O]

Αριστοτέλης - Aristotle?

Amazing, once you learn the Russian alphabet; it all becomes Greek.

 

[Mike375]

I'd say it was tables bound to forms and the quantity of macros that was slowing it down.

No, the table will be to big because that is how I first did it.

With 15 salesman and 2 years of dates there are about 11000 records. If we have 30 salesman then we hit 22000 records.

My way, each table only has 730 records for two years and the diary display opens and a Setvalue for RecordSource does the trick. In addition and as I said in an earlier post those tables just make it so easy to pull off one part of the data base.

I would also mention that Pat Hartman posted just recently about having a corrupt table. So there is another gain I get. All my eggs are not in one basket

Mike

 

[Mile-O]

All my eggs are not in one basket

You do sound particularly cuckoo.

 

[Rich]

All my eggs are not in one basket

Mike

You have a lot to clutch though don't you

 

[Mike375]

A properly designed database would not need to be sorted horizontally which is why there is no immediate option.

I'm guessing your data is of the structure Data1, Data2, Data3, Data4, Data5, which is a violation of the First Normal Form (1NF) of database design.

Read in an earlier posting on this thread on how I do it. It could be done with any row in any data base. But the appt slots must be displayed across the screen so several days can be seen at one.

Mike

 

[The Stoat]

Αριστοτέλης - Aristotle?

Amazing, once you learn the Russian alphabet; it all becomes Greek.

No offence, i could of guessed that

Try the rest of it without using an online translator. I'd be interested to know what you get.

TS

 

[Mile-O]

It could be done with any row in any data base. But the appt slots must be displayed across the screen so several days can be seen at one.

Yeah, but if you read the bit that you quoted by myself then you'll see I was talking about a properly designed database.

The days should go down and not across and there shouldn't be 365 (or 366 on leap years) records as this creates lots of empty records which are redundant - you should, therefore, only have records for dates when you need them. All these excess records, for one thing, contribute to the speed as it is having to process empty records.

Another speed issue can be solved by joining related fields with a numerical key than anything textual.

 

[Mile-O]

Try the rest of it without using an online translator. I'd be interested to know what you get.

Please, I actually do know Russian (I am currently learning) and from an alphabet with 33 letters I think I am more than capable of selecting the 24 that constitute Greek and making words from them.

 

[Mike375]

But the days to go down, both in the display and the table that contains the appoints.

The display looks something like this

Date Box 1 Box 2 Box 3 Box 4 and across to Box 10

Under that row of boxes is a second row of boxes. However it is on the same record, that is, it is only the form displaying it as a second row.

When the diary form opens it displays seven days and defaults to the current date for the first date and the display is of course for the salesman in question. The form is tablular and I can scroll right through to the end. When prospects are put into the data base they are assigned to a salesman. So we might names in categor 8, 13 and 19 and each of those names have the salesman's number.

So a prospect is being called and he agrees to an appointment on 17 June at 10 am. 17 June is clicked and the a drop down list opens with time and a time is clicked, which is inserted into the first blank box. The macro then continues on its merry way and among other things it opens a form based on a table with one field.

The macro opens the form at a new record and then goes to the first appt box and SetValues the new record in the open form with the time in the first box. It then goes to the second box and if there is an entry it then "goes to new record" in the open form and Setvalues the time from the second box to the new record in the form. It keeps going until it runs out of boxes with times or it comes to the 10Th box.

The macro then sorts the open form in ascending order and then goes to the first record and SetValues that time into box 1. Then it goes to the next record and SetValues that time to box 2.

That all results in the times going across the page in ascending order.

The macro then closes the form and then runs a query that deletes the supporting table.

Mike

 

[Mile-O]

I made a quickie - it may be a little buggy on the new employee as I added that as an afterthought.

It only deals with Dates, but it shows how you don't need too much. And simply adding a new person to the Employees table means that you immediately have a diary system for that employee - no new tables, no macros, no code.

I have an better diary system that deals with appointments but I'd need to strip it out of another database that I have. When I get a chance I'll upload that.

Alternatively again, myself and some others are working on an Access Calendar Project on this site.

 

[Mike375]

I made a quickie - it may be a little buggy on the new employee as I added that as an afterthought.

It only deals with Dates, but it shows how you don't need too much. And simply adding a new person to the Employees table means that you immediately have a diary system for that employee - no new tables, no macros, no code.

I have an better diary system that deals with appointments but I'd need to strip it out of another database that I have. When I get a chance I'll upload that.

Alternatively again, myself and some others are working on an Access Calendar Project on this site.

I am unable to get those attached files to work. I can extract but I think having Access 95 is giving me Unregognised Format. I get that wenh I try and open or import.

Could you describe roughly what it is doing and would it allow appt slots to be shown across the page for each day

Mike

 

[Mike375]

Hi Mike,

All arguments aside it is obvious that some parts of you database need "redesigning". I am not necessarily talking about the functionality i.e what the screens do but how the data is held. This will probably endup effecting how the data is presented and accessed anyway. I take your point that it works for you, and it is a big consideration changing something that is in place and you know how to use. However no one will touch this with a barge pole if it is presented to clients and it does to conform to the accepted standards for database design.

If you have an entity relationship diagram or a schematic for the current system that would be very helpful. I will PM you with my details. I will give you an honest review of your current setup and at the very least tell you exactly how this could be achieved using a normalized system. If i think there is some serious mileage in it I will design table structures and give you a story board breakdown on how the data can be entered and retrieved. If at this stage you are happy to go ahead then things are going to have to get legal

A simple fact of IT is there are a many ways of achieving the same goal. Access doesn't force you to use normalization. But it is designed to support a system that is designed that way. Normalization is platform independant. If i was designing an Oracle or SQLServer or any other database using a modern db paltform i would do a systems analysis to understand how the system fits together and once i had done that i would always normalize my data. Having done that any competent db developer could implement this on any platform.

I think what people are trying to say is normalization has been proved to be the most effective way of holding data. It doesn't mean that there are no other ways but like most things in life there are accepted standards so at the very least we can all speak the same language.

TS

Maybe I could send you the DB on a CD ROM. It is on Access 95

I think you will find that somethings it does are not quite as simple as some people here think. It has been developed since 1996 so there has been some input into it!!!!!

Mike

 

[Mike375]

Where is Franknstuff, the thred starter.

Time for bed here in Australia. OK for you Yanks at around 9am and the pommies and company in Europe at around lunch time.

Mike

 

[Mile-O]

Access 95? I thought you've moved on.

It was in A2000 but can only be converted back to A97.

It's one table for Employees, one for diaries, and one for subjects (just a memo summary I thought would be handy)

They are linked as per the attached picture.

I have one form linked to a query of the employees table and on that form there is a subform which contains a query of the diaries table. This latter query has two criteria - a parameter looking for the EmployeeID in the partent form and a parameter looking for a textbox with a date. Both form and subform are joined via the EmployeeID.

The textbox holds a date and when a new date is entered there it requeries the records for all diaries applying to that person on that date. They are displayed in the subform.

When you add a new person to the Employees table, they automatically have a diary system which is empty until diaries are added. No need for unnecessary records - people don't work this day or that day, they are on holiday, etc and therefore haven't worked - no need to keep a date aside for appointments for them on those dates.

This is what Pat meant by adding one record to create a wjole new appointment system for one person rather than creating lengthy tables - the design takes care of these issues.

 

[Cosmos75]

My, my!, this thread certainly has grown!


Pretty interesting thread...

Where is Franknstuff, the thred starter.

He stopped at about halfway through this thread!