How would you approach this form? (1 Viewer)

[yepwingtim]

He wants a access database EXACTLY like this
http://img99.imageshack.us/img99/959/wtfxi5.jpg

there are over 400 fields their

Is there a way to normalize this? But making the form appear exactly the way it is?

Any advice =)

Thanks guys
________
Daihatsu materia history

 

[HiTechCoach]

But making the form appear exactly the way it is?

If you mean by "form", a printed form or report, then yes.

If the data is properly normalized, there probably my will be a lot less than 400 fields.

How the data is stored in the tables often does not look anything like is is display on forms and reports.

If is possible to make a data entry form that is not based on a normalized table structure and use it to enter data into a properly normalized structure. The downside it that it usually requires a lot of coding ( time = money).

What I find is hard for people when they first start using a relational database, especially if they are used to spreadsheets, is to change their thinking about how to data entry and not think like a "spreadhseet". With a spreadsheet, you tend to entry the data the way you want it to print. That is not normally true with a relational database.

 

[dreamdelerium]

just a bit of advice, too. i had to do a similar thing but didnt realize that there is a limit to the number of controls per form (i think its 250 but could be wrong and remember this counts lables,lines, as well as textboxes, etc)

 

[boblarson]

just a bit of advice, too. i had to do a similar thing but didnt realize that there is a limit to the number of controls per form (i think its 250 but could be wrong and remember this counts lables,lines, as well as textboxes, etc)

That would actually be:

Number of controls and sections you can add over the lifetime of the form or report - 754

And that is for 2003 and 2007.

 

[yepwingtim]

any advice guys or i have to make 1200 fields =(
________
Tiger Blood

 

[tranchemontaigne]

The URL link apprears to be broken right now
________
Of Indiana Automotive, Inc.

 

[HiTechCoach]

I would use a temp table where each row is a record and load all the data into the temp from the actually properly normalized tables. This way you can use a sub form in continuous mode.

Once the data entry is done, I would have a a save button to run code to place the data back into the normalized tables.

 

[yepwingtim]

u mean make
e1 e2 e3 e4 e5 e6 e7 e8 e9... etc in a table?
a1 a2 a3 a4 a5 a6 a7 a8 a9... etc in another table?
________
Extreme Q Vaporizer

 

[HiTechCoach]

One table.


Field 0: Sort Order
Field 1: Purpose Assessment Desc (a value from the first column in your example)
Field 2: CDay1
Field 3: CDay2
Field 4: CDay3
Field 5: CDay4
Field 6: CDay5
Field 7: CDay6

...

Field 34: CDay33


You would have a separate record for item (row in the example) that is identified by the value in the first field.

 

[yepwingtim]

Hi HiTechCoach,

http://img370.imageshack.us/my.php?image=picspt7.jpg
I don't quite understand the sort field ...
I made the form like this now.
It has a combo box now (the test field)
It is great but i need all them to be displayed like that (not selecting the combo box over and over again each time I want a new record)

http://75.127.124.193/database5.mdb the database i made~
________
Codeine Rehab Dicussion

 

[jwhite]

HiTech was suggesting that, in your Temp Table, include a field [SortOrder] which would be numbered in the Order that your rows are currently displayed. I'm thinking you would have a table named "tblCycleRecordOrder" with fields CROrderID (PK AutoNumber), SortOrder, Identifier. Identifier would be the names in Column 1. Then join this table to your output query and have ORDER BY SortOrder.

Your sample database illustrates that you are treating MS Access (A Relational Database) as an Excel Spreadsheet. This is not how you would do it, and you would not have 'combo boxes' built directly in tables -- just raw data in tables.

As a start, I can imagine the following possible tables (not complete):

tblPeople
tblAddresses
tblPatients
tblPatientContacts
tblDiagnosisTypes
tblPatientDiagnoses
tblPatientVisits
tblVisitTypes
tblHospitals
tblDoctors
tblVisitPurposes
tblVisitAssessments

Just off the top of my head... Not in the Medical field, so more tables probably necessary for the rows after the Assessments. If this is your first attempt (?), I would suggest reading up on Database Normalization (search here or on Google) for tips on creating a Normlized Database Design. With the proper design, you can manipulate the data any way you wish. I would recommend trying to put something together, then when you have questions, make a post in the Tables/Relationships/Design Forum stating what your purpose is, any special requirements, and then provide your tables as you have them, such as:

tblDiagnosisTypes
----------------
DiagnosisTypeID, AutoNumber, PK
DiagnosisCode, Text
Description, Text (or Memo if needed)
...

tblPatientDiagnoses
---------------------
PatientDiagnosisID, AutoNumber, PK
PatientVisitID, Number, FK
DiagnosisTypeID, Number, FK
...

 

[gemma-the-husky]

assuming you dont want to interact with this, and its there for information, then i would open a pre-formatted excel template, and populate the excel template with the appropriate data.

if you have a query with all the data, then it only takes a few secs to populate the spreadsheet

 

[yepwingtim]

http://img373.imageshack.us/img373/7184/piccw0.jpg


I have to make this look like a spreadsheet - with all the tests in their
Maybe I can use a update query?
________
Ford motor company of canada specifications