HOWTO: JOIN 2 Tables into 1 Recordset when tables ARE NOT linked?

[Thom]

Hi All! What I'm struggling to do now is combine the 2 tables below into ONE recordset for my division's new employee test program. As of now the 2 tables aren't linked, because I didn't see a need to do so. So can I use a CROSS JOIN to bring all the records from EACH table into the recordset? OR is it easier to establish a (basically useless)link between the 2 tables & use an OUTER join based on needing to get ALL the records from Table 1?

Table 1

Autonumber Primary Key
Question
FirstAnswer
SecondAnswer
ThirdAnswer
FourthAnswer
CorrectAnswer

Table 2

Autonumber PK
TestName
TotalNumberofQuestions
NumberofTestQuestions
NumbertoPass
DateofTestDesign
TimeLengthofTest

As you can see, there's NO correlation between the two, other than the fact that the 2 tables COMBINED make up the stats for the new employee's test. I want to try to keep everything in 1 disconnected recordset which I can update after I finish writing the test questions. JHauseman & others already helped in regards to writing 2 separate Update commands to SAVE the data when done-but I forgot to consider how to start the whole thing! <sg> ANY help would be appreciated! Thanks!

thomas

[Clunietp]

I'm assuming Table 1 is your QUESTIONS table and Table 2 is your TESTS table. I'm also assuming you have multiple tests and multiple tables, and there is a logical 1-to-many relationship between the TESTS (1) and the QUESTIONS (many) tables....

Add a foreign key to Table 1, linking it to Table 2 AutoNumberPK field. This way, you'll be able to find out which questions were on which test (assuming you have multiple tests)

You'll also probably want a Employee table with the employee info, so you can also find out who did the test. Add a foreign key to Table 2, linking it to Employee.AutoNumberPK. This way, you'll be able to find out which employee took which test, and what questions were answered

HTH

Tom

[Thom]

Thanks for the answer! You've made me realize a possible flaw in my design.

What I was working with was a SEPARATE Table2 in the Access database for EACH separate test I was going to have to write/build. Ergo there would also be a SEPARATE Table1 for each test as well.

ex: NewEmployeeTest #1 would have a Table1 for its specifications, and a Table2 for its questions;

Then NewEmployeeTest #2 would have a DIFFERENT Table1 for its specs, and a DIFFERENT Table2 for its questions! These 2 tables would be totally unrelated to the Table1/Table2 in the NewEmployeeTest #1.

Given that set of circumstances(which I can't change, it sounds like you're saying to build a link between the 2 tables for each test. So...If I link every Table1 to the Table2 of each individual test thru the AutoNumberPK field I shouldn't have a problem pulling ALL the records from BOTH tables, right? I would just do a FULL OUTER JOIN based on Table1, correct? Thanks a bunch for your help, I know this sounds crazy, but it's the only way I can figure out to handle all the different requirements for the project!

thomas

[Clunietp]

separate tables for each Employee? That's very bad database design, whoever designed that is totally missing the point of databases

[Thom]

I agree Clunietp! Here's what the DB person who started the whole thing wants to do(as I get more answers from y'all the project grows in scope..surprise, huh!). We MAY end up putting this on the local network(more questions will be asked THEN of course, but later), and he wants to be able to have up to 40 employees tracked as they take the various tests that we will build.

So he's thinking, if we build 10 test(for example) we'll have 10 DB tables of test questions(TestQuestions01 thru TestQuestions10); AND 10 tables of test specifications(ie how many questions, how many to pass, etc) called TestSpecifications01 thru TestSpecifications10.

So...each of the VB application test's recordset will need to pull data from the 2 table(TestQuestions, TestSpecifics)to make 1 'test'. BUT these table don't need to be related(except for, as Tom pointed out, trying to keep the size of the recordset down)!

So that's what I'm working with. IF you know any easier way to do this, I'd LOVE to hear it. Until then, I'm going with disconnected recordsets to allow users to connect, load their test, disconnect while taking the test, and then reconnect to the server to save their results...

[JHausmann]

What a mess.

You need _one_ table for tests, _one_ table for test specifics, and a table that stores the results by employee.

All the tables should contain a test number that can be used as a key to join the tables.

[Thom]

Thank you, Jhausmann, & Clunietp! I took your advice & went back to the database guy & 'convinced' <g> him to do it the way y'all mentioned!

The only thing I'm worried about is figuring out the SQL to combine the tables to get results that make sense; i.e. show every person's grade, who took 1 specific test; or show the top 5 missed questions by all users of a specific test. But that's just learning SQLs syntax(I figure). Again, thanks for your help, it made a BIG difference!

[Clunietp]

here's some more help for ya

http://w3.one.net/~jhoffman/sqltut.htm

best of luck :)

[Gen-X]

If I were you I would actually design the Database properly before even attempting to do anything with it.


Table 1 - Questions
Autonumber Primary Key
Question
FirstAnswer
SecondAnswer
ThirdAnswer
FourthAnswer
CorrectAnswer

Table 2 - Tests
Autonumber PK
TestName
TotalNumberofQuestions
NumberofTestQuestions
NumbertoPass
DateofTestDesign
TimeLengthofTest

Table 3 - TestQuestions
Autonumber PK
TestRef (Foreign Key to Table2.Autonumber PK)
QuestionRef (Foreign Key to Table1.Autonumber PK)

Table 4 - Employees
Autonumber PK
Whatever you want here

Table 5 - TestResults
Autonumber PK
TestRef (as above)
QuestionRef (as above)
EmployeeRef (Foreign Key to Table4.Autonumber PK)
Answer (To be matched to the CorrectAnswer in Table 1)
AnswerCorrect (Boolean when marked)


If you do it this way then you can have questions that appear on different tests without having to repeat them, can store a number of employees, track them across tests and even provide and calculate the marks for them on the fly.