Create new fields on the fly?

[whninrome81]

Dear all,

I am a basic user. I have a database set up to gather information daily. Fields for the first day are something like:
studyday1


studytime1
sodium1
seizure1...
etc.

I can create fields for days 2,3,4...which will work for most entries, which will have maybe 3-6 days worth of these data. However, some may have more.

I want to create a more responsive database - is there a way to AUTOMATICALLY create sequential fields at the click of a button? In other words, I enter day 1's data then the next day I execute a function that generates a set of fields ready for day 2's data. The point would be not having to copy and paste 50 sets of daily fields when I might only need that once or twice.

Finally, is it something that I can do on a form? So that when Access creates those fields, it automatically copies the form I have built for day 1 into a new tab labelled sequentially (day 2, day 3, etc)?

I have no programming experience, but it seems VB might be able to help. Thank you for your help in advance!

[jzwp11]

Welcome to the forum!

Your approach is wrong. You would not create fields but rather records.

It sounds like you are using Access as if it were an Excel spreadsheet which it is not.

Can you provide some detail of what business process you are trying to model with your database?

[whninrome81]

Thanks - I thought about using records but ultimately, I'll be using the data to export into a stats program (SPSS) and wanted that to be easy as possible. In terms of organization, I also can't use relational databases for the same reasons. Maybe you can help clarify what is possible though...here's the data structure:

For each patient (right now, each patient is a unique record), the is baseline information (fields). Then, as patients have daily EEGs performed, there are fields for data regarding the EEG for each day - as many days as they need it (variable).

[jzwp11]

Following normalization rules the structure would be like this

tblPatient
-pkPatientID primary key autonumber
-txtFname
-txtLname

tblPatientEEG
-pkPatientEEGID primary key, autonumber
-fkPatientID foreign key to tblPatient
-dteEEG (date field)
-fieldtoholdEEGresult (not sure what type you need)


You can add as many records to tblPatientEEG as necessary.


In terms of output, you would need VBA code to format the output the way you want. I would typically use Access to Excel automation to do that.

[whninrome81]

Thanks - that was very helpful. Having not done any Access in the past, this will be really useful in designing db in the future.

[jzwp11]

You're welcome. Please post back if you have any further question on table design for your project.

[whninrome81]

In the interest of keeping the thread separately, I did just post another question re: adding new records to the new table from a single form...https://www.accessforums.net/forms/a...ble-21342.html.

Any help would be much appreciated!

[jzwp11]

Before jumping into forms, the table structure has to set up properly using the rules of normalization an overview of which can be found on this site.


As to your other post, if you have "variables" and "EEG variables", the rules of normalization say that like data (i.e. all variables) should be in 1 table not 2.

So I ask again, what business process are your trying to model?

Once we have an understanding of your process, we can better help work out the table structure. Once that is done, then we can work on forms.

[whninrome81]

I should explain further - basically, this is for research as opposed to business. The tblvariables includes patient information (name, age, sex, history, etc.). That will be a one-time entry (each patient will be unique). Then, there is a separate table (tblEEGvariables) that includes daily EEG data (seizures, sleep, etc.) - no overlap in the daily EEG data vs. the patient information variables. The EEG data will need new records each day but related to the primary key (which is the medical record number originating in tblvariables).

I had already made the form - sorry, that's why I asked the question

[jzwp11]

In your patient table (I'll call it that since that the data it contains) what is the purpose of the history field?

there is a separate table (tblEEGvariables) that includes daily EEG data (seizures, sleep, etc.)

It looks like you have many attributes or parameters (seizures, sleep etc.) for which you capture data for each patient on each day. I assume an attribute applies to many patients. That describes a many-to-many relationship. Instead of having fields called seizures, sleep etc. they should be records in a table that are then related back to the patient

tblEEGVariables
-pkEEGVarID primary, key autonumber
-fkPatientID foreign key to tblPatient (your tblVariables)
-dteParameter (date field)
-fkParameterID foreign key to tblParameters
-Field to capture value of parameter for the patient on the given date

tblParameters
-pkParameterID primary key, autonumber
-txtParameter

With your current design what would happen if you added another parameter to your list of sleep, seizures etc? You would have to redesign your table and EVERY associated form, query and report! I would not want to do that.

[whninrome81]

I'm not sure it will be that complicated; or perhaps I'm being a bit simple about it. For instance the field "seizure" is a yes/no field - I won't need to capture anything subdata about the seizures. While many patients will have seizures, in the end I just want to know how many people had a seizure while on EEG, so won't need to refer a single attribute to multiple patients (I don't think).

That said, I haven't designed much so changing things now isn't a huge deal. I'm not sure how adding a table of parameters helps but I think I don't quite understand how it might be used to create a better workflow using the form (which is the objective - smooth workflow for easy data entry and eventual export into a stats program as a flat file).

[jzwp11]

For a relational database to work properly, it must follow a normalized structure. It is the job of the designer to make the user interface work smoothly for the user but keep the data normalized.

What other "variables" are in your tblEEGvariables? Could you supply a list of the fields?

[whninrome81]

I've attached here a list of the variables in tblEEGvariables specifically; some are "memo" variables - like the "history" variable in the tblvariables table - and therefore represent narrative data. You can ignore those since they won't contain discrete variables

[jzwp11]

The pdf was blank.

[whninrome81]

ARGH! Sorry...I'm going between a mac and a pc and things keep getting lost in translation...