Structure for using input forms to main database

[ptaylor-west]

Note: I put this in General original but it is rmore likely to be a table structure problem

I have a single table for storing the main data, this is fed by input from 3 forms. I have been asked if the forms can be used independantly and remotely as input forms.

My solution was to simply send out a database with just one form and created an append query so that when it is returned with completed information it would simply append the database to the main database. In itself this works fine however, what is happening, of course, is that when I get the other forms back I end up with triplicate records, that is instead of the information from the 3 forms being stored as one record it is now split over 3 records.

I have to be careful in what I do as the original database has been running for over 6 months now and has a lot of stored data and because of established queries/calculations/reports the last thing I want to do is change or split the main data table.

Is there anyway I can get the three records to concantenate on one line. The three forms all have separate fields as they were taken from the original database.

 

[ptaylor-west]

The forms are surveys for salespeople, mystery shops etc. So we use each form to carry out the survey which stores the info on one table, this allows ratios etc to be calculated and produce reports that give an overall picture.

The surveys are carried out at different times.

Up to now it has been inhouse so no problems, now it is going to be outsourced hence the dilema.

 

[ptaylor-west]

That is it exactly Uncle Gizmo

 

[WayneRyan]

Paul,

From what you're saying, I would still store each survey as a seperate
related record. You just want to retrieve the individual records and
store them collectively with each having their own unique ID.

By seperating them, you'll make the later job of traversing them a lot
easier. If they all live in one record expansion (increase to 4 sections)
is a lot tougher. Also, comparing/contrasting them is much easier when
they are seperated.

Have a table to hold a definition of each "section" of the survey. In
your example, its three, but support any number.

tblSections
===========
SectionID - AutoNumber
SectionTopic - (Part1, Part2, Part3 ... Partn)


Then have a table to track each survey.

tblSurveys
==========
SurveyID - AutoNumber
SurveyDate
SurveyStatus

Each survey really consists of some number of records. One for each
Section.


tblSurveyDetail
===============
SurveyDetailID - AutoNumber
SurveyID - FK
SectionID - FK

Then you can use SQL to find completed surveys, which sections are
missing from all of the surveys, etc.

Wayne

 

[ptaylor-west]

Your rigfht about setting up seperate tables for each survey, that way I can just send out seperate databases that just contain one survey in which case there would be no confusion at the recopeint end. Then I can simply append the results when I get them back into the main database.

The problem is the existing data which is all linked to the salespersons name and where they work (location). I can split them up but how do I get them to link with each other? Currently the data is all in one table and the forms I use just add this one table so I don't have any problems, you open the summary form, selecg the person and then through a command button it opens one of the surveys using the current salespersone name showing on the sumarry form as the filter.

It's probably so simple but I can't see it.

I ahve spent some time creating sperate tables but I'm not getting very far.