John, as always, thank you so very very much. You conversation, interest, and knowledge, is alwys a great inspirartion to me. Especially when I burn the Late Night Oil.
I am comfortable that the Database is in good shape "code wise", there actaully is very little code in it!...mostly Option controls that feed yes/no,a couple of Body mass index calc, a couple of feet to metric conversions,but thats about it.
I learned from you early on to use BOUND forms as much as possible and let Access do the work :)
When I first got on this forum about 3 years ago, my first project was a time scheduler.... It has functioned for 3 years now without any failure....But I do go back and look at the code time to time and wonder what planet I was on at the time :)
TY SO SO SO much.
and in closing, just a side note, the number of users on the system at one time will probable be 1 or 2 and an occasional 3.
Yours, Terence M. Lepczyk
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Terence-
>
> 1. Maybe time to learn new tricks? Unless your database is really badly
> designed, it should upsize to SQL Server fairly easily. But be aware that
> Attachment and multi-value fields are NOT supported in SQL Server. The scanned
> documents should probably all be in a shared folder somewhere - not in the
> database. Use a text field to store the path to the document instead. In 2007,
> you can set the Control Source of an Image control to a text field containing a
> path, and the Image control will load the image from the stored document.
>
> 2. Yeah, you could probably do that. Start with an "empty" compacted database.
> Add 10 patient visits and all the data, then see how big the file is. The
> difference should be a reasonable estimate of that it takes per 10 visits.
> Extrapolate from that. I'm recommending 10 as a sample to give you a better
> guess.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> Sent: Friday, August 26, 2011 10:56 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text box
> created with ALL changes
>
>
> John, actually I have 2 questions, and on a side note I keep getting email =
> failures when trying to rely via web post.
>
> 1.
> Regarding SQL server......I no next to nothing about them....
>
> I can tell you that Hardware,and software is not an issue here, they will buy
> what they need....My concern or question is, HOW Portable will my code be ?
>
> 2. Can I get a fair estimate of how big this db may get by creating a single
> Office vist (average size), and than simply get an estimate on the expected
> number of patients in ayear and and do (Size * Patient) 3D BIGSPACE ?
> terence
>
>
> terence
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Terence-
> >
> > With the exception of the Attachment, everything is a number. Your tables
> might
> > look like:
> >
> > Patient
> >
> > Visit
> >
> > VisitTest
> >
> > VisitTestReading
> >
> > VisitTest would have the type of test performed - Berg Balance, Gaitright,
> etc.
> >
> > VisitTestReading would have the individual readings take for the particular
> > test. Some rows in it might look like:
> >
> > Berg Balance SittingToStanding Reading
> > Berg Balance StandingUnsupported Reading
> > Berg Balance SittinWBack Reading
> > ...
> >
> > You could have separate table for test and readings that would form a
> "template"
> > for the data to be collected. Let's say Berg Balance is test #1. Rows in the
> > readings table would look like:
> >
> > 1, 1, SittingToStanding
> > 1, 2, StandingUnsupported
> > 1, 3, SittingWBack
> > 1, 4, StandingToSitting
> >
> > ...
> >
> > When entering data, the user would select a test to update the VisitTest table
> > for the Visit and Patient. Your code would "copy" the template into
> > VisitTestReading with all 0 values ready to be entered.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > Sent: Friday, August 26, 2011 10:33 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text box
> > created with ALL changes
> >
> > here are 3 typical examples, sorry about the terrible formating.
> > terence
> > BERG BALANCE TEST BERG BALANCE TEST
> > ID Long Integer
> > 4
> > sitting_to_standing Long Integer
> > 4
> >
> > Standing_unsupported Long Integer
> > 4
> > Sitting_w/_back Long Integer
> > 4
> > Standing_to_sitting Long Integer
> > 4
> >
> > Transfers Long Integer
> > 4
> > Standing unsupported with eyes closed Long Integer
> > 4
> > Standing unsupported with feet together Long Integer
> > 4
> >
> > Reaching forward with outstreched arm while Long Integer
> > 4
> > Pick up object from the floor from a standing Long Integer
> > 4
> > Turning to look behind over left Long Integer
> > 4
> >
> > Turn 360 degress Long Integer
> > 4
> > Placing alternate foot on step/stool while Long Integer
> > 4
> > standing unsupported
> > Standing on one leg Long Integer
> > 4
> >
> > Standing unsupported one foot in front Long Integer
> > 4
> > berg_score Long Integer
> > 4
> > trial1 Single
> > 4
> >
> > trial2 Single
> > 4
> > trial3 Single
> > 4
> > avg Single
> > 4
> >
> > GAITRITE TEST GAITRITE TEST
> > Name Type
> > Size
> >
> > ID Long Integer
> > 4
> > Assisted_device_used Yes/No
> > 1
> >
> > Cane Yes/No
> > 1
> > Rigid_Walker Yes/No
> > 1
> > Front_Wheel_Rolling_Walker Yes/No
> > 1
> >
> > Four_Wheel_Rolling_Walker Yes/No
> > 1
> > Hemi_Walker Yes/No
> > 1
> > None Yes/No
> > 1
> >
> > GAITRite_att Attachment Data
> >
> >
> > SF-36 TEST SF-36 TEST
> >
> >
> > Name Type
> > Size
> >
> > ID Long Integer
> > 4
> > genral Long Integer
> > 4
> >
> > health Long Integer
> > 4
> > vigorous Long Integer
> > 4
> > moderate Long Integer
> > 4
> >
> > lifting Long Integer
> > 4
> > climbing_several Long Integer
> > 4
> > climbing_one Long Integer
> > 4
> >
> > bending Long Integer
> > 4
> > walking_more Long Integer
> > 4
> > walking_several Long Integer
> > 4
> >
> > walking_one Long Integer
> > 4
> > bathing Long Integer
> > 4
> > cut Long Integer
> > 4
> >
> > accomplished Long Integer
> > 4
> > were Long Integer
> > 4
> > had Long Integer
> > 4
> >
> > cut_down Long Integer
> > 4
> > accomplished_less Long Integer
> > 4
> > did Long Integer
> > 4
> >
> > past_4_weeks Long Integer
> > 4
> > bodily_pain Long Integer
> > 4
> > pain_interence Long Integer
> > 4
> >
> > full_of_pep Long Integer
> > 4
> > very_nervous Long Integer
> > 4
> > cheer_you_up Long Integer
> > 4
> >
> > calm Long Integer
> > 4
> > lot_of_energy Long Integer
> > 4
> > downhearted Long Integer
> > 4
> >
> > worn_out Long Integer
> > 4
> > happy_person Long Integer
> > 4
> > feel_tired Long Integer
> > 4
> >
> > during_the_past Long Integer
> > 4
> > get_sick Long Integer
> > 4
> > healthy_as_anybody Long Integer
> > 4
> >
> > get_worse Long Integer
> > 4
> > excellent Long Integer
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > >
> > > Terence-
> > >
> > > I would try to have one field per result, but if you have some tests with
> only
> > 1
> > > number or yes/no and others with a dozen or more, then it's going to be
> tough
> > to
> > > do. Give me an idea of some of the tests and the readings you have to
> > collect.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > > Sent: Friday, August 26, 2011 9:29 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text box
> > > created with ALL changes
> > >
> > > John, latex screening is simply a test (lots of yes/no) to determine the
> > > probability of latex alergic reactions. This is singled out because it must
> be
> > > done every visit and overlays the previous, it does not have a historic
> > > relevance.
> > >
> > > Its a federal requirement.
> > >
> > > John, How would you have handled say, a dozen variables half yes/no and
> half
> > > numeric in a single text field, would you have done something like comma
> > > seprated ?
> > > terence
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > >
> > > > Terence-
> > > >
> > > > I would have done:
> > > >
> > > > Patient_Info
> > > >
> > > > Visit_Table
> > > >
> > > > Visit_Tests (1 row per test per visit.)
> > > >
> > > > Not sure what you mean by latex screening. What is that for?
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > > > Sent: Friday, August 26, 2011 8:52 AM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text
> box
> > > > created with ALL changes
> > > >
> > > > Actually John, thats how I started it.
> > > >
> > > > The opriginal desing called for 3 tables,
> > > > Patient_INFO (1 per patient), name , address, soc, etc
> > > >
> > > > Then a latex screening Table...1 per Patient.
> > > >
> > > > And then a Visit_table.
> > > >
> > > > The visit table had all the stats.
> > > >
> > > > The Visit-table had the final score from each of the tests...thus the test
> > > would
> > > > be perfromed, written down on paper, totaled, and the total entered into
> the
> > > > Visit_Table.
> > > >
> > > > Then we embarked on doing away with the paper forms. hence a popup for
> each
> > > > paper form, that automatically scored, and the score still placed in the
> > > > Visit_table.
> > > >
> > > > If I tried to build a text fuield for each test, it would involve using
> > > > substantial logic , and i would probably have to do something like comma
> > > > seperated variables within each test_text_filed to seperate the metrics.
> > > >
> > > > But it could be done.
> > > >
> > > > I am curious, even though 40 tables IS Bizzare, do you see anything
> > inherently
> > > > wrong with it ?
> > > >
> > > > Do you anticipate speed problems ?, Size problems, all of the above ?
> > > > terence
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > wrote:
> > > > >
> > > > > Terence-
> > > > >
> > > > > Is there any possibility to put all this data in one table? The layout
> > > would
> > > > > look like:
> > > > >
> > > > > PatientID
> > > > > TestID
> > > > > TestDate
> > > > > TestData1
> > > > > TestData2 ...
> > > > >
> > > > > You could have a table to list all the test types, and that table could
> > > > indicate
> > > > > how many of what type of field is needed to record the test data. Maybe
> > you
> > > > can
> > > > > do it all in 2 or 3 text fields. Just trying to throw out some ideas to
> > > make
> > > > > this more manageable. Forty tables is bizarre. To add a new test right
> > > now,
> > > > > you must design a new table and companion data entry form. If you could
> > > > > redesign it as I have suggested, adding a new test would simply involve
> > > adding
> > > > a
> > > > > row to the test types table.
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside Out
> > > > > Microsoft Office Access 2007 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > > > > Sent: Friday, August 26, 2011 8:14 AM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text
> > box
> > > > > created with ALL changes
> > > > >
> > > > > LOL, Thanx John, I needed that...I really did :)
> > > > >
> > > > > This project started as a simple database for a Physical Therapy Clinic.
> > Th=
> > > > > e databse grew because they found out I could build, score, and Store
> the
> > d=
> > > > > ata from an assortment of tests they do. The assortment of test is
> insane,
> > =
> > > > > Things like, SF36, Postural Sway, Fear and beleifs Scale, Latex
> screening,
> > =
> > > > > Timed up and Go, 6 meter walk test, On and On.
> > > > >
> > > > > The forms have minimal calculation in them, simply gave them the
> > oportunity=
> > > > > to Click Mousey, instead of keeping paper sheets. Additionally this is a
> > L=
> > > > > arge Reasearch Project, so they keep HUGE amounts of clinacal data for
> > anal=
> > > > > ytics.
> > > > >
> > > > > I build each form as a popup, each popup was bound to its own table, and
> > ea=
> > > > > ch table is tied via ID to it's Patients/Visit record.
> > > > >
> > > > > We are now about 40 tables with about 400 potential fields/elements
> being
> > s=
> > > > > tored per visit. Although the norm is about half that for a typical
> visit.
> > > > >
> > > > > AND YES , I am scared about the size of this...
> > > > >
> > > > > In your aponion John , am I taxing MS Access tooooo Much with this
> > applciat=
> > > > > ion and should perhaps talk to the customer about scaling back ?
> > > > >
> > > > > We have yet to populate this thing and give it a work out.
> > > > > I am worried about access speed, Space , we got plenty.
> > > > >
> > > > > What the therapist actually are doing is using WIFI Tablets connected to
> > th=
> > > > > e PC, so they can move around, assist the patients, and take
> measurements
> > a=
> > > > > s they go.
> > > > >
> > > > > Your thoughts are always welcome, harsh as they may be at time :)
> > > > > Yours
> > > > > terence
> > > > >
> > > > > Majority of the field are either Number (Integer) or yes/no, scattered
> > text
> > > > and
> > > > > attachments.
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > > wrote:
> > > > > >
> > > > > > Terence-
> > > > > >
> > > > > > Yes, but for how many records? Is there only one record in each of
> the
> > 40
> > > > > > tables? To get all the fields concatenated from a record into one
> > string,
> > > > do:
> > > > > >
> > > > > > Dim strResult As String, rst As DAO.Recordset, intI as Integer
> > > > > >
> > > > > > Set rst = db.OpenRecordset(strTbl, dbOpenDynaset)
> > > > > > If Not rst.EOF Then
> > > > > > For intI = 0 To rst.Fields.Count - 1
> > > > > > strResult = strResult & rst(intI) & ", "
> > > > > > Next intI
> > > > > > End If
> > > > > >
> > > > > >
> > > > > > John Viescas, author
> > > > > > Microsoft Office Access 2010 Inside Out
> > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > Building Microsoft Access Applications
> > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > SQL Queries for Mere Mortals
> > > > > > http://www.viescas.com/
> > > > > > (Paris, France)
> > > > > >
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > > > > > Sent: Friday, August 26, 2011 7:46 AM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a
> text
> > > box
> > > > > > created with ALL changes
> > > > > >
> > > > > > ok, how about this,
> > > > > >
> > > > > > I can get the count of how many Fields are in each table as below
> > > > > >
> > > > > > Dim db As Database
> > > > > > Dim strtbl As String
> > > > > > strtbl = "INFO"
> > > > > > Set db = CurrentDb
> > > > > > MsgBox db.TableDefs(strtbl).Fields.Count
> > > > > >
> > > > > > is there a way I can retrieve the content of each individual field
> like
> > > > > walking
> > > > > > a subscripted array ?
> > > > > >
> > > > > > Terence
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, "Terence"
> > > <buffalome90210@>
> > > > > > wrote:
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > I have have about 40 tables in this database, which may or may be
> > > created
> > > > d=
> > > > > > > uring an "Encounter". My customer wants all the data that is added
> > into
> > > > tho=
> > > > > > > se tables during that "Encounter" dumped into a text box, we are not
> > > > concer=
> > > > > > > ned with format. We dont even care if there are associated label or
> > just
> > > > th=
> > > > > > > e data, I just need the stuff shoved in a box.
> > > > > > >
> > > > > > >
> > > > > > > There are aprox 400 individual elements, so I dont want to write
> code
> > to
> > > > do=
> > > > > > > them all. Any one have a clever idea?
> > > > > > > terence
> > > > > > >
> > > > > > > I am thinking something with a Star (*)...like a me.dumpbox =3D
> > > > me.recordse=
> > > > > > > t
> > > > > > >
> > > > > > > or making some sort of a continuos for, or a datasheet, I just dont
> > know
> > > ?
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > ------------------------------------
> > > > > >
> > > > > > Yahoo! Groups Links
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar