I am giving up: My form never worked

[Sylvie]

Hi,
I'm still struggling to organize my tables and fields and I still confused
regarding the relationship between tables. I'm a newbie to Access. This is my
first database and I fluctuate between feeling hopeful I can do this one
minute and despairing that I can't the next because I've thought of yet
another complication that I don't know how to handle. I have scoured the
Internet and this disscussion group for weeks searching for the answers that
I need. I found some but I am still at a loss as to what to do about others.
I was wondering if someone would be kind enough to review my table structure
and respond to some questions at the end. I am truly grateful.
Here are the tables

Tble PAtient: PAtientID; PatName,PatInitial, Birthdate, GroupID

Tble Group: GroupID, GroupName, Group

TbleSponsor: SponsorID, SponsorName, StudyID

TbleLabTest: LabTestID, LAbTestDescription, LabTestResult

My Question is,: when the patient came in for the tests labs (it can be
3ipstick, pregnancy test and Papsmear) . Also for each test can be up to 15
results with its ranges.
I created a table VISIT: VISITID, SubjectID, GroupID, SponsorID
however it is not working.

Should I create a tbel for each test?(Dipstick, pregnancy test and Papsmear)
Please help, I am giving up on this. I thouhght was easy.

 

[Beetle]

Should I create a tbel for each test?(Dipstick, pregnancy test and Papsmear)

No.

I don't completely understand your requirements, but based on what you
posted I am going to assume the following;

A Group can have many Patients but a Patient can only be part of one Group.
(1:m relationship)

A Patient can take one or many Tests, and any given type of Test can be
administered to more than one Patient. (m:m relationship - so you need
a junction table to define it)

An individual Test can have one or many results (another 1:m relationship)

I don't know how Sponsors fit into the picture, so I will ignore that for now.

One thing to keep in mind here is that "Dipstick", "Pregnancy Test" and
"Papsmear" are only *types* of tests. An actual test is a certain type of test
given to a certain Patient on a certain date, so you should also have a table
for Test Types. An example table structure;

tblGroups
*******
GroupID (Primary Key)
GroupDescription
other fields related to specifically to a group

tblPatients
********
PatientID (PK)
GroupID (Foreign Key to tblGroups)
FirstName
LastName
MI
BirthDate
other fields related specifically to a patient

tblTestTypes
**********
TestTypeID (PK)
TypeDescription

tblLabTests (this would be the junction table for Patients/Test Types)
********
LabTestID (PK)
PatientID (FK to tblPatients)
TestTypeID (FK to tblTestTypes)
TestDate

tblTestResults
**********
ResultID (PK)
LabTestID (FK to tblLabTests)
Description

Then when you create your form you would, for example, have a main form
based on tblPatients (or a query thereof) with two subforms.The first subform
would show all the tests that patient has taken and the second subform
would show the results of each of those tests.

 

[Clif McIrvin]

Hi Sylvie--

You wrote: I'm still struggling to organize my tables and fields and I
still confused
regarding the relationship between tables.

I'll chime in and add a bit to what Beetle suggested. By adding (PK -
primary Key) and (FK - foreign key) Beetle gave you the necessary
details to define your relationships, but Access doesn't do the
relationships for you. From the menu bar select Tools | Relationships to
get the relationships window.

Add your tables to the window and drag each foreign key onto it's
related primary key to tell Access what the relationships are.

Before you define the relationships, the subforms Beetle suggests will
not work.

HTH

 

[Author]

I struggled with my first database and spent most of my time on newsgroups
like this. Being a newbie myself, I learned that you *cannot* give these
people too much information. Re-read your post, and make sure you didn't
leave any crucial words out. On that note, make sure you communicate
effectively what your desired result is. I used to get frustrated when I
would post a question, and people would respond with questions about my
database. Now, upon reading your post, I understand why they asked. I will
check back to see if I could help; I would love to be able to assist you
-Pete.

 

[Aria]

Sylvie,
I'm happy that you are receiving the help that you need. The help I have
received in this newsgroup has been exceptional. But I have to question why
you are using my words and not your own? It's a little disconcerting to see
my words staring back at me in your post. No harm done; I just find it
strange.

I know there are days when it's tough but don't give up. Listen to sound
advice. You can do it. Good luck with your db.

 

[Sylvie]

Thank you'll for the reply. I am working on it. I'll definitelly have
additional questions. To Aria, sorry about the "plagiarism"; when I saw your
post I felt like your words were coming out of my mouth. I was so desperate
and confused that even my thoughts would block the attention from the time
(as I posted before and no one reply). Also it was very well written. Txs,
Sylvie

 

[Aria]

I was so desperate and confused that even my thoughts would block the
attention >from the time (as I posted before and no one reply). Also it was
very well written.

Thank you, Sylvie. As I stated before, there was no harm done. I well
understand desperation and confusion; not being able to think it through
clearly or even knowing where to turn. I wish I could help you but I am a new
user too, still working on my first database. I have found, as I'm sure you
have too, that it is the answers we receive here in the newsgroup that keep
us going. Please do not lose hope. One day at a time, one problem at a time,
gets us both closer to our goal. I wish you the best of luck with your db.

 

[Author]

My Question is,: when the patient came in for the tests labs (it can beWhat's your question?
What's not working?
You should create one table to contain all test types. Call it TblTestTypes
and give it one column, and three records (Dipstick, Pregnancy Test,
Papsmear). Then make three tables (TblDipstickResults, TblPregnancyResults,
TblPapsmearResults) For each table, make as many records as there are test
results within the range. Once you make the tables, you can use them to make
combo boxes on a form. Then use the following link:
http://www.techonthenet.com/access/subforms/multi_subforms.php

It is read-only, but open the database and look at the VBA and form/subform
structure. It isn't *exactly* matched up with your problem, but has some
stuff in common and should give some insight. Also, my solution of creating
tables may violate normalization rules, but I believe it will work. Let
someone with more experience come up with a more refined approach.
Hope This Helps,
Pete