Link null rows to a form-create row on edit

[S.Scott]

I have a database form where my team processes tickets reported by the
customer. I want to conduct a survey going forward when someone processes a
ticket, so I created a new table. Ideally I don't want to create a new row
in the new survey data table unless and until someone fills out the survery.

The problem I have is I've not been able to get the form to access the new
survey data unless the row already exists. Is there any way to create a new
row aautomatically (or at least easily) when someone enters data? I'd rather
not have to create row upon row of null data.

Right now I have a large table where the primary key is ticket number, and
the survey data is in another table joined by ticket number.

 

[Jeff Boyce]

"How" depends on "what" ... until we know what data structure you're using,
we'll be hard pressed to offer more than generic suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[S.Scott]

The main table structure has the ticket number as primary key. There are
hundreds of columns holding various bits of data related to the all the
tickets for the past several years. Most of the columns are linked to a
field on the form somewhere; some are updated by batch jobs. (My team does
their work by enterring data via the form.)

I'm adding a survey to collect data some feedback when people finish working
on a ticket. The survey structure is just a bunch of checkboxes, with the
data stored as "bits". The primary key is also the ticket number.

I don't want to add additional columns to the main table structure to hold
the survey data because the main table is already huge and I only need the
survey data for a small percentage of the tickets. (I think sparsely
populated is the term?)

I'm trying to join the survey table to the main table via the ticket number
(the primary key for both), but I can't figure out how to update the survey
checkbox data from the form unless the record for that ticket exists ahead
of time. But if I have to create a record for every ticket already ahead of
time, it defeats the whole point of a seperate table. I only want records in
the survey table if there is some non-null survey data to store.

I'm just not sure how to lay this all out so it works the way I want.

Thanks in advance for you help!
S. Scott

 

[Jeff Boyce]

If your table has "hundreds of columns", you're probably not making the best
use of the (Access) tool.

If you were working in a spreadsheet, you'd pretty much have to add columns
to add "survey data". But as you are finding, doing that in a relational
database like Access causes both you and Access considerable extra work.

Please consider the possibility that you need to reorganize how your data is
stored, to take better advantage of Access' relationally-oriented
features/functions.

And since you are describing survey-related data, please take a look at
something Duane H put together to help illustrate (*and build*) a
well-normalized relational data model of surveys (see...)

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[S.Scott]

If your table has "hundreds of columns", you're probably not making the best

use of the (Access) tool.

Duh. But I inherited this mess, I didn't design it. This database is used
daily and I only have a few hours each month in which to try to work in some
gradual improvements. I don't have the luxury of ripping it apart and
starting over, and whatever I do must be backwards compatible so I have to
take baby steps. What I'm attempting to do is not make the problem worse by
adding more columns to an already overblown table.

So back to my original question - how do I handle adding a new record via a
form when the record may not exist until the user starts to enter data? If
this can't be setup to happen automatically by some sort of join setting or
field property, then some advice as to how to do it programmatically via the
form would be helpful

 

[Jeff Boyce]

Don't know ... haven't done it. Perhaps one of the other newsgroup readers
can offer some insight...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

P.S. I wasn't suggesting 'ripping it apart'. I was pointing out that the
tools Access offers expect data organized in certain ways. If you don't
have the time or resources to organize your data that way, you pay for it in
how hard you (and Access) have to work.