Page with lots of fields

[apple17]

I have encountered this issue on a couple of recent projects and it's time to figure out what a good solution might be.

I have a page with 100 fields that can be updated (some via checkbox, some via radio buttons, a couple with actual values). Once the user inputs the appropriate data, it needs to be sotred in an access database. So I dutifully created a database table and associated query last time listing the 100 fields. This time I'm wondering if there is a more elegant solution. If I have 100 fields and each indicates a true / false for an entity (patient), is there an elegant way to do this?

Surely others have encountered this issue before...

 

[QatQat]

name the fields dynamically and loop through them executing insert queries.

This prevents you from writing a massive single insert query but I think it would put pressure on Access, having to perform consecutive queries. If you were using a real RDBMS then I would definitely recommend this way.

CHeers
QatQat

Life is what happens when you are making other plans.

 

[Tarwn]

While it is possible to do something dynamic, I would stick to writing out my query. Working with a string that long is going to be bad enough, efficiency-wise, when you start concatenating in 100 values. If you generate it comepletely dynamically it's only going to get worse. Granted it may not result in a human measureable delay, but I always aim to minimize load and time delays because what isn't a delay for 1 person could be a delay for 10 or 100 people hitting it in concert. Or 10 people hit another site on the same box just as my one person hits the submit button.

Honestly I doubt I would work with a 100 field table to begin with. I would find some way of breaking it down, determining if it needed to be normalized or if some of the data needed to be in an extension table. Butthats just me,

-T

 

[apple17]

Well, in response to 'breaking it down', I started doing that. I took half the fields and stored them individually, making a table for 'field/value'. But that is slow. I could break them into groups as well, but that seems like just a compromise.

Researchers like to collect a lot of data about their projects. These aren't even my 'biggest' datasets. One customer has a 15-page data collection form to put online. I thought that perhaps others had encourtered this challenge before.