Need help setting a database up...

[Rhett_Y]

Hi all...

I am trying to create a database for testing. Here is what it needs to
include and this is the part I am stuck on...

tblApplicant
ApplicantNumber (PK)
TestNumber
Lname
Fname
MInitial
Address
Street
City
State
ZipCode

tblLocation
TestNumber (PK)
TestLocation

tblDate
DateID (PK)
TestNumber
ApplicantNumber
Date

I have also attached a copy the relationship.

Now... This is why I did it this way.. hopefully this makes sense.

Test location 1
50 testers
date 1/1/2006

test location 1
65 testers
date 1/2/2006

Test location 1
120 testers
date 1/3/2006

Test Location 2
55 testers
date 1/1/2006

Test Location 2
24 testers
date 1/12/2006

Test Location 3
55 testers
date 1/3/2006

Hope this makes sense. Did I setup the database correctly to get this type
of information from it?

If you can see any changes that I need to make please let me know,,

Thank you
Rhett

 

[Jeff L]

A few suggestions:
1. Eliminate the TestNumber from tblApplicant. By leaving it in there,
you will be capturing the same data multiple times if an applicant
takes multiple tests.

2. Create a table (tblLink) with ApplicantNumber and TestNumber that
you use to link Applicants to Tests

3. I would combine tblLocation and tblDate into one table. Your table
would be:
tblTests
TestNumber (PK)
LocationID
DateOfTest

4. Create a Location table (LocationID, Location)


To count the number of people taking a given test, your query would be:
Select a.TestNumber, Location, DateOfTest, Count(*)

From (tblLink a Inner Join tblTests b On a.TestNumber = b.TestNumber)

Inner Join tblLocation c On b.LocationID = c.LocationID
Group by a.TestNumber, Location, DateOfTest

Hope that helps!

 

[Rhett_Y]

Jeff..

Thank you very much..........!! I am in the process of doing what you
said!! Thanks again...

Rhett

 

[Rhett_Y]

Jeff..

Here is what I have now......

applicantnumber (pk)
lastname
firstname
minitial
address
street
city
state
testtype
note

tblLinks
applicantnumber (set to number)
testnumber (set to number)

tblLocation
LocationID (pk)
location

testnumber (pk)
locationid (number)
dateoftest

tblLUTestType (this is just a look up)
locationid (pk)
location

I have the following setup in the relationships

(tblApplicant)Applicant number 1 to many (tbllinks)applicantnumber

(tbllinks)testnumber many to 1 (tbltest)testnumber

(tbltest)locationid many to 1 (tbllocation)locationid

Now when I setup my form I am trying to create a drop down on the location
and it is only showing me locationid not location..... I don't know if I
have setup the relationship correctly..

R~

 

[Rhett_Y]

have a feeling this may not be correct.. I am having a heck of time setting
up the form....

I have tried using subforms etc... What I would like to see.. is this..

The top part would be the test location, test date, test time.. and the
subform is the applicants information.. I can have say 50 applicants
associated to one test to one location....but that location can have multipal
test dates and multipal test times...

I can't get the form down...I have tried setting the form off a query and I
have also tried setting it up off a table..... No luck....

R~

 

[Jeff L]

Ok. You're definately getting there.

In the Applicant table, you don't have a field for zip in the address.
Also, the testtype field should go in tblTests. In the Notes field,
are you storing Notes about the Applicant or notes about the Test? If
the Notes are about the Test, then the Notes field should go in the
Test table as well.

The tblLocation and tblLUTestType have the same field names.
tblLUTestType should be TypeID and Type

Now when I setup my form I am trying to create a drop down on the location
and it is only showing me locationid not location..... I don't know if I
have setup the relationship correctly..

Your dropdown list needs to have its settings changed a little bit. I
am assuming that the source for the drop down is tblLocation, which it
should be. The bound column should be se to 1, the number of column
should be 2, and the column widths should be 0";1"
Now when you choose a value from the list, you will see the Location,
but it's the LocationID that will be stored, which is what you want.

 

[Rhett_Y]

Jeff...

Ok I did what you did.... But I am still not getting the results.... Here
is how the form is setup...

Top part has the test location and test info and the I am using a sub form
for tha applicant...

But when I look at the form none of the info in the tables is showing up...
I am hoping my relationship is setup correctly...... This is the first time
I am setting up a database with multipal tables and it is really confusing
me... as you can see..lol..

R~



Ok. You're definately getting there.

In the Applicant table, you don't have a field for zip in the address.
Also, the testtype field should go in tblTests. In the Notes field,
are you storing Notes about the Applicant or notes about the Test? If
the Notes are about the Test, then the Notes field should go in the
Test table as well.

The tblLocation and tblLUTestType have the same field names.
tblLUTestType should be TypeID and Type

Now when I setup my form I am trying to create a drop down on the location
and it is only showing me locationid not location..... I don't know if I
have setup the relationship correctly..

Your dropdown list needs to have its settings changed a little bit. I
am assuming that the source for the drop down is tblLocation, which it
should be. The bound column should be se to 1, the number of column
should be 2, and the column widths should be 0";1"
Now when you choose a value from the list, you will see the Location,
but it's the LocationID that will be stored, which is what you want.

 

[Jeff L]

What is the Record Source for the Subform? It should be the tblLink.
You will need a drop down list on the subform that has the Applicant's
name and ID Number. Use this for the Row Source:
Select ApplicantID, LastName & ", " & FirstName & " " &
Left(MInitial,1)

From tblApplicants

Again make the bound column 1, set the number of columns to 2, and the
column widths to 0;1

The field linking the Main Form and Subform should be TestID. When you
select your test, all the applicants that have been assigned to the
test should show in the subform. When you assign someone to the test,
the TestID field in tblLinks is automatically updated to the proper
value.

Your main form should be have a record source that has all your test
information in it, probably need a query for that.

 

[Rhett_Y]

Jeff...

Thanks for your help.... I was wondering if we could do it this way:

Have the test location, time, date etc on the top.. The test locations will
be in a drop down.. they don't change in terms of adding or deleting... the
only thing that changes is the dates and times...

then in the subform, have it so the end user can enter the LName, FName
Etc.....so when they are looking at that test date and time they can see a
real time total of who is in that class... I am trying to cut down on how
many entry pages the end user has to see....

So basically.. The end user can do this:

pull up a blank form.. enter the test location, enter time, enter date...
then enter the first person for that test.... When the next person comes
along,, they can do a search on the date..pull up the above test that has
been entered,,get a total of how many students are in the test...and if the
have room can add another person to the list....and so on.... We can have
say 30plus tests going on in a month etc... Does this make sense? I can see
the way you have been setting up, I don't think that would work because they
would have to do dual data entry.. ie..put in the student info, then go to
the test page and use the drop down to find the student they just entered and
put them in that test..

R~

 

[Jeff L]

Forms and subforms should relate to each other in some fashion. It
doesn't sound like you would have that doing it the way you are
suggesting. You assign applicants to a test. For that to happen, you
need to have an applicant in your database to assign to the test. I
don't think that trying to do all your data entry on one handy dandy
form is the way to go about this. Obviously, this is your project and
you can do it how ever you want. I'm just offering suggestions. If
you have more questions, feel free to ask and I'll try to assist you as
best I can.

 

[Rhett_Y]

Hi Jeff...

I was wondering if the way I suggested can be done? You have the test in
the upper portion...then you add students as the sign up,, I wish I could
attach a file to this...so I could show you.......

Something like this:

test location
test date
test time

then student info in datasheet view
student 1
student 2
student 3
etc...

R~

 

[Jeff L]

Yes, I know what you are talking about. What I was trying to say was
that if you are entering student data (Name, address) into the subform,
then you won't be associating the student with the test. You will
simply be adding a record to the Applicant table and it also won't show
you what students are already enrolled to take the test when you select
the test on the main form. That's why I said I didn't think that
trying to accomplish all your data entry via one form is the best way
to accomplish your task. I think it will become frustrating to you. I
believe a better way to do it would be to have your subform attached to
tblLink and you plug in your students here via the drop down.

 

[Rhett_Y]

Ahhhh I see.......

The only thing I could as a short coming to this would be... we have say
30 tests going with 100-200 students per test.....looking through the names
would be very hard yes/no? They also want to keep the info for future
mailing.....hence grabbing all the info for the students... But at the same
time trying to make it user friendly... I will try it out and report back
when I run into trouble..lol... I know I will...lol..

Thanks again for your help!
R~

 

[Jeff L]

If you have a combobox set up like I suggested earlier, with LastName,
FirstName, then you won't need to search through all your names to find
the correct one. You can simply type the name, Last Name first, into
the box and it will match the names as you type. The only thing you
need to make sure of is that if you have more than one person with the
same name, like John Smith, that you pick the correct one. I would
suggest that you include the address fields as part of your combo box
listing.