Database Structure Question ... Serious Help Only Pls

[DrewKull]

Ok ... so i have a database im building to track user feedback. The
problem im running into is we need to track "Problems" detected in the
feedback, but we have multiple inputs for feedback. So right now, we
get feedback from Surveys and Email. The problem im running into is a
neeeed to link these two input to a common "Input ID" in order to
easily track a "Problem" (each "Problem" will have an "Input ID"
showing the source of the Problem whether it was from a survey or
email). But my problem comes up with large imports that i do into the
survey table. So when i add a bunch of data (by import or copy/paste)
into the table, the Input ID is not updated because that MUST be kept
in a separate table in order to link both inputs. Does that make
sense? So how do i do this?!?! Here's a "diagram" of my tables:

Survey:
- Input ID (FK from Input)
- Date, etc.

Email
- Input ID (FK from Input)
- Date, etc.

Input
- Input ID (PK,AutoNumber)
- Type

Problem
- Input ID (FK from Input)

So the problem is when i add data to Survey or email, I dont want to
manual add data to Input then go back to Survey and input the next
AutoNumber generated ...

Does this make sense? Any ideas???

[ManningFan]

It makes about as much sense as having InputID be a field in every one
of your tables...

Dr******@gmail.com wrote:

Ok ... so i have a database im building to track user feedback. The
problem im running into is we need to track "Problems" detected in the
feedback, but we have multiple inputs for feedback. So right now, we
get feedback from Surveys and Email. The problem im running into is a
neeeed to link these two input to a common "Input ID" in order to
easily track a "Problem" (each "Problem" will have an "Input ID"
showing the source of the Problem whether it was from a survey or
email). But my problem comes up with large imports that i do into the
survey table. So when i add a bunch of data (by import or copy/paste)
into the table, the Input ID is not updated because that MUST be kept
in a separate table in order to link both inputs. Does that make
sense? So how do i do this?!?! Here's a "diagram" of my tables:

Survey:
- Input ID (FK from Input)
- Date, etc.

Email
- Input ID (FK from Input)
- Date, etc.

Input
- Input ID (PK,AutoNumber)
- Type

Problem
- Input ID (FK from Input)

So the problem is when i add data to Survey or email, I dont want to
manual add data to Input then go back to Survey and input the next
AutoNumber generated ...

Does this make sense? Any ideas???

[pietlinden]

You need to normalize a little. What seems to be missing is a single
source of an identifier for the source of the feedback - responding to
a call/interaction with support. Once you have that, you can subclass
the other stuff, if you want.

if I were you, I'd get out a pen and paper and take a step backward.
Describe how the paper-based system works (or how your proposed one
will). Determine what inputs you can gather and what reports you want
or information you want out of your database. Sounds rudimentary, but
it's a lot cheaper to scrap paper drawings than hours of work on
something that will never work.

The key question is how to link the call/contact with the followup
survey. Why not just create a foreign key in Survey that refers back
to the initial contact? If you have to, you can subclass that out so
you can gather information specific to each contact type and go from
there. There's an example at www.mvps.org/access by Rebecca Riordan.

[Tim Marshall]

Dr******@gmail.com wrote:

Survey:
- Input ID (FK from Input)
- Date, etc.

Email
- Input ID (FK from Input)
- Date, etc.

Input
- Input ID (PK,AutoNumber)
- Type

Problem
- Input ID (FK from Input)

You want "Serious help only", well here it is. 8) There's no if, and,
or but about it, the design you've shown is incorrect and basically
useless. No offence meant.

These should be combined into one table. What's the entity you're
trying to model? The answer is input information. One entity = 1 table.

Try something like this, instead:

Input_Information
- Input_PK
- Date, etc
- Input_Type_FK
- Problem_Type_FK
- Problem description

Input_Type
- Input_Type_PK
- Input_Type (Survey, email, etc)

Problem_Type
- Problem_Type_PK
- Problem_Type (headers of different types of problems)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Mike Gramelspacher]

In article <11**********************@p79g2000cwp.googlegroups .com>,
Dr******@gmail.com says...

Ok ... so i have a database im building to track user feedback. The
problem im running into is we need to track "Problems" detected in the
feedback, but we have multiple inputs for feedback. So right now, we
get feedback from Surveys and Email. The problem im running into is a
neeeed to link these two input to a common "Input ID" in order to
easily track a "Problem" (each "Problem" will have an "Input ID"
showing the source of the Problem whether it was from a survey or
email). But my problem comes up with large imports that i do into the
survey table. So when i add a bunch of data (by import or copy/paste)
into the table, the Input ID is not updated because that MUST be kept
in a separate table in order to link both inputs. Does that make
sense? So how do i do this?!?! Here's a "diagram" of my tables:

Survey:
- Input ID (FK from Input)
- Date, etc.

Email
- Input ID (FK from Input)
- Date, etc.

Input
- Input ID (PK,AutoNumber)
- Type

Problem
- Input ID (FK from Input)

So the problem is when i add data to Survey or email, I dont want to
manual add data to Input then go back to Survey and input the next
AutoNumber generated ...

Does this make sense? Any ideas???

CREATE TABLE ProblemCodes
(problem_code CHAR (2) NOT NULL PRIMARY KEY,
code_name VARCHAR (250) NOT NULL);

CREATE TABLE Sources
(source_name VARCHAR (20) NOT NULL PRIMARY KEY);

CREATE TABLE Problems
(problem_id AUTOINCREMENT (1,1) NOT NULL PRIMARY KEY,
problem_code CHAR (2) NOT NULL
REFERENCES ProblemCodes (problem_code),
problem_text MEMO NOT NULL,
source_name VARCHAR (20) NOT NULL
REFERENCES Sources (source_name));

[DrewKull]

Try something like this, instead:

>
Input_Information
- Input_PK
- Date, etc
- Input_Type_FK
- Problem_Type_FK
- Problem description

Input_Type
- Input_Type_PK
- Input_Type (Survey, email, etc)

Problem_Type
- Problem_Type_PK
- Problem_Type (headers of different types of problems)

Thanks so much for the responses everyone. But i've tried all of
these options and have problems with them

1) WIth the setup above, the Survey data i'm given back is an EXPORT
Excel sheet from a 3rd party company. The give me a sheet that has MANY
rows (same columns and such) of data. So per user feedback, I have a
row. And i must Import this excel sheet into a table (cuz it would be
ridiculous to ask someone to input this information manually). The
problem with the above solution is ... The email output (the other
Input_Information) which is a COMPLETELY different column setup.
There will not import properly into a table setup like the one above.
And again it would be crazy to have someone manual input this data.

2) Then there is a problem with normalizing. I've had the database
normalized. But because of the exports i get from these two sources, I
could not figure out an easy way to import them into the database.

3) So also, because i am IMPORTING a BUNCH of data rather than just
adding individual cases to the database, there is no way (that i know
of) to correlate input of a bunch of things to a FK in another table.
so based on that ... any ideas? Again, thanks so much guys... I've
been struggling with this and i really need to produce here ...

[pietlinden]

If you have the data in Excel, then that will make your life a lot
easier. You can attach Excel files to your DB and query them directly
from within Access. then all you need to do is determine what
information goes where you your database, design and test your append
queries, and then set it to go. Look up transferspreadsheet and import
specifications in the help file. that' should get you going.

[DrewKull]

pi********@hotmail.com wrote:

If you have the data in Excel, then that will make your life a lot
easier. You can attach Excel files to your DB and query them directly
from within Access. then all you need to do is determine what
information goes where you your database, design and test your append
queries, and then set it to go. Look up transferspreadsheet and import
specifications in the help file. that' should get you going.

Ok this post seems to be getting me somewhere!! I will surely look
into the help file (im assuming the mean the "Access Help File (F1)"
.... so I will check there.

This may make things sooo much easier. thanks! Any other information
you or anyone can get/give would be AMAZING!!

(maybe a link?)

[DrewKull]

Ok ... i have another question...
Say i use the normalized tables above (now that i can link an Excel and
then Append Query data to appropriate tables)

Is there a way to do this:

So when i run the append query, I want to take one row at a time from
the linked spreadsheet, append some data to the Input_Information table
(which has the auto increment number for 'Input ID') and once that data
is in there, grab the Input ID auto-generated and put it in another
table with the rest of the specific information relating to the input
Type?

Because i need to be able to link these up with a common Input ID and
when im importing a lot of data all at once, I dont see a way of doing
this ... Any ideas?

[pietlinden]

Maybe simply appending with a query won't work... (I'm open to other
suggestions!)

The only problem you might have is determining the primary key
information so that you know where to write each record's data. (Well,
so you can relate the data back to a single parent record after
splitting up your Excel data.) I found this in one of Steve Arbaugh's
old posts:
<SNIP>
On your recordset object after you have updated the recordset issue a
command such as:

MyRecordset.Move 0, MyRecordset.LastModified

and then just read the value of the field.

HTH

Steve Arbaugh
ATTAC Consulting Group
</SNIP>

So you could open a static recordset of the Excel data, loop through
it, and append the parent table data, grab the PK as above and store it
in a variable in your code. Then you could do the appends to all the
child tables and pass that same value as the foreign key. Might look
ugly in code, but it's a lot faster than doing the whole thing
manually. Essentially, you would be adding each normalized record to
one or more tables (Parent table first), and then adding the children
and the Parent table's primary key to the child tables. Everything
would turn out nice and normalized, and once you got the code working,
you could run it and forget about it.

Pieter

[Tim Marshall]

Dr******@gmail.com wrote:

problem with the above solution is ... The email output (the other
Input_Information) which is a COMPLETELY different column setup.
There will not import properly into a table setup like the one above.
And again it would be crazy to have someone manual input this data.

It would be even more crazy to have different tables for this.

What you need to do is look carefully at the differnt types of inputs
you are getting. Design ONE table (the relational gods will "Kull" you
dead if you try doing this with more than one table, seriously...8) ).
You will very likely find that even though the outputs appear very
different, the information they are identifying is very similar in at
least some places. You can set up fields in your inputs table so that,
depending on what kind of input is being imported, some of the fields
will be left blank (null). Do the sort of thing Piet has suggested.

2) Then there is a problem with normalizing. I've had the database
normalized.

No you have not, not with the structure you presented.

3) So also, because i am IMPORTING a BUNCH of data rather than just
adding individual cases to the database, there is no way (that i know
of) to correlate input of a bunch of things to a FK in another table.

Does each Excel sheet have its own unique record identifier?

You can do a number of things here. You can create a compound primary
key in your input table that combines, say a Excel Sheet date, a company
name (the submitter) and the reocrd identifier of each record. Probably
easier, you can create a single field that combines this sort of data.

Or, probably best, for cross-referencing, do the following.

Add a new column onto the sheets you receive. Sequentially number the
records with a new number that starts immediately after the highest
number in your database table. Then import the Sheet with the new
numbers. Now you have one simple key that correlates between what the
submitter's sheet has (which you added) and your table.

In this case, it is vital that you keep a copy of the modified sheets,
of course, for any later trouble shootings or goo-ups.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DrewKull]

Tim,

Thanks so much for the detailed repsonse, again. And no, the setup i
showed was not normalized, but i have a visio diagram of a normalized
to 5th form, but just wouldn't work for my application (as you can
see).
So what i've done is like you said, put everything into one table and
have about 9-10 blank columns ... which kind of sucks ... but it works
to get the Input ID to be on a unique ID for inputs database wide and
really simplifies linking it to a "Problem"

But ... it's certainly NOT normalized because there are all those blank
columns and such. Soo ... i guess that worked.
thanks so much everyone!

Any further comments .....?