DB size?

[Mike375]

namliam

I don't disagree ar all with the principles you are putting forward and if I was to make this data base from scratch there would be several things I would change. However, these type of data bases build up over time, probably similar to furnishing a house over time.

Where I do disagree with you is that I think there are more of this type of data base in existance than you might think and they all have one thing in common. They are invariably made by people who are not in the computer business but do run their own business and very often those businesses are of a sales nature.

For someone like me there are two basic prioroites....how quickly can I make something and of course will it work.

Just the other week I made something in this data base and I already know that people such as yourself or Pat Hartman would not touch what I did with a 200 foot pole For various reasons I wanted to pull out the 10th, 20th and 30th call from telemarketing sessions. Now this gets ugly I made a simple macro which

1) opens a continoous form, sorts a Now() entry in descending order.
2) Then a bunch of GoToRecords and at 10, 20 and 30 a SetValue action sticking a 1, 2 and then 3.
3) A query that has as the criteria 1 o 2 or 3.

The macro runs and does the SetValues, then closes the form an opens a form based on the query. I have something even uglier!! I use the autonumber field to set the value of another field which I call NameNumber. When we dump maybe a 1000 new records in to the main table a form is opened which is opened for all records where the NameNumber is empty. A macro then does a RunMacro action and roars its way down through the records doing SetValue actions at each record. Crude to be sure, but very quick to make and it entertains me and fills me with confidence as I watch it fly through all of the records.

Regards,

Mike

 

[Rich]

It sounds as though your macro is updating at snails pace, not flying

 

[Mike375]

It sounds as though your macro is updating at snails pace, not flying

Fortunately it is doing it on a continuous form so at least it is a downhill run

By the way here is a small challenge for you blokes with the coding.

Could you sort across a row A to Z. I can and do do it. There are 10 appointment slots on a row for each day. The actual appointments are not stored here. This is a visual thing so you can see what slots are taken but the slots must go across the row in ascending time order, bearing in mind that the 8th appointment made for a day might be at 7am and the first made for the day might be at 8pm.

Mike

 

[Rich]

Use a crosstab query

 

[namliam]

I know that there are thousands and thousands, in the Netherlands alone, of "amatures" out there creating databases just to get what they want/need. And i understand the basics as well as the "child with a waterhead" as we call it. It starts something small and ends up doing everything in the world.

Its just that I AM a proffesional and see problems like these on a daily basis. You have a database that does the job, some day sooner or later (I hope for you later or even never) these halfway solutions are gonna byte (pun intended) you in the rear end. As they do all of my customers, and this allways happens when you asspecially DONT need it.

Aside from all that, there is the matter of supportebility. What happenes when you are no longer around, is it documented so someone else can maintain? I will bet NO.
I recently had a customer with a database that had:
1) code in a form
2) calling a macro that
3) called code in a module that
4) in turn called a macro to call code in another module.....

Works, yes, supportable, no... It takes for ever to backtrack, asspecially when the code is (as it was in this case) filled with goto's. It was a regular chalange (spelling), and they didnt want it but i think i would have gotten where they wanted to be faster by rebuilding the whole thing...

Greetz

 

[Mike375]

Greetz

Actually I do have a form of documentation for the data base which is for my business partner in case something does happen to me.

I liken the professional such as yourself as a business that works on cars. The people such as myself only work on our own car and get to know it like the back of our hand.

Mike

 

[Mike375]

Use a crosstab query

I am not sure that would work for what I do. The query I use has 12 months of dates and various other data some of which is totalled in a text box.

Also, if it sorted across the row and there was 7 blank slots would it put all of those to left.

Mike

 

[Rich]

Also, if it sorted across the row and there was 7 blank slots would it put all of those to left.

Mike

Code would, in fact MS provides an example of dynamic crosstab Reports

 

[Mike375]

Code would, in fact MS provides an example of dynamic crosstab Reports

I assume then that a form can be based on such a query.

 

[namliam]

Greetz

Actually I do have a form of documentation for the data base which is for my business partner in case something does happen to me.

Wow you dont say, realy.... Thats a first for me good for you !

I liken the professional such as yourself as a business that works on cars. The people such as myself only work on our own car and get to know it like the back of our hand.

Mike

And if you dont document the back of your hand you (or your partner(s) ) will get into trouble. But you documented.... wow which makes it a bit less off a bad thing, proffesionally speaking offcourse. Because if it works it works....

Regards

 

[namliam]

By the way here is a small challenge for you blokes with the coding.

Could you sort across a row A to Z. I can and do do it. There are 10 appointment slots on a row for each day. The actual appointments are not stored here. This is a visual thing so you can see what slots are taken but the slots must go across the row in ascending time order, bearing in mind that the 8th appointment made for a day might be at 7am and the first made for the day might be at 8pm.

Mike

Anything that can be done using a macro, can USSUALY be done better by code....

Greetz

 

[Mike375]

Code would, in fact MS provides an example of dynamic crosstab Reports

Rich,

I just looked up one o my Access books (Access 95) and it saying you can't sort on the cross tab if there are calculated fields of which I need at least one.

Mike

 

[Mike375]

Anything that can be done using a macro, can USSUALY be done better by code....

No argument there but I was interested in how you fellows would do it. My way would be crude by your standards but it works 100% and is very simple, a bit of lateral thinking.

Mike

 

[Rich]

Article ID: Q109939
and Article ID: Q155489
should be helpful to you

 

[Mike375]

Article ID: Q109939
and Article ID: Q155489
should be helpful to you

I have seen those type of articles referenced before....where do they come from.

What I do at present is that when a time is clicked from a combo a macro runs that inserts the time in the first available blank box. Then a macro a opens a form at a new record from a table with one field and then goes to the first box on the row to be sorted and a SetValue sticks the time from the box in the form and then the macro goes to another new record action and then a SetValue puts the time in from the second box and so on until a blank box or No 10 is reached. The macro then sorts the field in the open form for A to Z and then starts a series of SetValue actions to put the times in the boxes across the row. The form is then closed and a query deletes all the records and Presto, all is done.

Mike

 

[Rich]

The form is then closed and a query deletes all the records and Presto, all is done.

Eventually


The articles are in the MS Knowledge base, which I think is still downloadable

 

[Franknstuff]

Sorry Mike

This really wasn't a DB question? Think of it as a survey of what other peoples database have. Mike really is the only one who has answered.

Qty: Querys
forms
tables
etc...

 

[Rich]

I can't give any examples of mine, I've never bothered to count any of the objects, it just does what I want it to do.
Is your question important or just a general enquiry?

 

[Franknstuff]

Not important

I started in Watercooler and ended up here? It's not important just making conversation.

 

[Rich]

It's certainly created a lively debate