Help with Form Please!!!

[KR]

I cannot figure this out...I am trying to create a form where I can:
-search for a specific doctor (I have 1000)
-once I select the doctor I was searching for I want all of the doctors
implant records to show up
-then I want to be able to select which study I want to view for that
doctor so that just the implant records for that study I can view (their are
8 studies)

Here is my information I am working with:
Table: USImplants
Fields: (each record is of a patient a doc has implanted a device for I have
50889 records and a lot of the doctors have implanted more than one device
hence the wanting to view records by doctors name and then by the specific
study (the study is the type of device))
CardN
FirstName (doctor's first name)
LastName (dontor's last name)
Study (A, B, C, D, E, F, G, H)
Product
Lot
SN
Site

If I can figure this out then I can view a doctor's implanting information
to see how many of each device a doctor has implanted.

Thank you if you can help me!!!

 

[KARL DEWEY]

A couple of questions. Is all of your data in one table or do you have a
table listing doctors and at least one other listing the Implants?
How is the patient identified in the table?
What is CardN ?

 

[John W. Vinson]

I cannot figure this out...I am trying to create a form where I can:
-search for a specific doctor (I have 1000)
-once I select the doctor I was searching for I want all of the doctors
implant records to show up
-then I want to be able to select which study I want to view for that
doctor so that just the implant records for that study I can view (their are
8 studies)

Here is my information I am working with:
Table: USImplants
Fields: (each record is of a patient a doc has implanted a device for I have
50889 records and a lot of the doctors have implanted more than one device
hence the wanting to view records by doctors name and then by the specific
study (the study is the type of device))
CardN
FirstName (doctor's first name)
LastName (dontor's last name)
Study (A, B, C, D, E, F, G, H)
Product
Lot
SN
Site

If I can figure this out then I can view a doctor's implanting information
to see how many of each device a doctor has implanted.

Thank you if you can help me!!!

YOu have only the one table? If so, you're not taking advantage of the real
power of Access as a relational database. As it is, you could have records for
Dr. Fred Brown, Dr. Fredrick Brown, Dr. Fred Browne, and Dr. Gred Brown, all
referring to the same person!

You're using a relational database: use it relationally! You should have at
LEAST these tables:

Doctors
DoctorID <primary key>
LastName
FirstName
<other biographical or contact information as appropriate>

Studies
StudyID
Description

Products
ProductID
ProductName
<other information about the product>

Lots
ProductID
LotNo <two field joint primary key>
<Information about the lot, e.g. expiration data, ...>

Sites
SiteNo <primary key>
SiteName
<other info about the site>

USImplants
ImplantID <primary key>
<patient info if there is any>
DoctorID <link to Doctors>
StudyID <link to Studies>
ProductID <link to Products>
<information about this particular implant>


As it is with your flat-file, spreadsheet design, you can get *some* useful
results, but the queries will be complicated. For instance you could create a
Totals query by adding the "*" pseudofield, and the FirstName, LastName and
Product fields to a Query grid; change it to a Totals query. Change the
"Totals" row to Count under the * field and to Group By under the other
fields. You'll get one record for each combination of the three fields.

But you should really recast this into a proper database to get the full
capabilities of Access!

 

[KR]

One Table has all the information. I have 2 other tables too but cannot link
them to the table USImplants for the life of me.

Table 2: USDoctors
(there are not any duplicates on this table but I can't figure out a way to
link it to the USImplants Table since the doctors names are seperated into
first and last name)
Fields:
LastName
FirstName
ID

Table 3: DeviceImplants
(this table is related to the USImpants table in that the study field has
one of the names of the devices from the devices field listed in it)
Fields:
DeviceID
Devices

I think my biggest problem is linking the tables.

The card number in the USImplants Table is the number of each patient record
(unique numbers)

I thought I would be able to create a mainform using USDoctors Table and
then select a doctor and have a subform using the USImplants Table to pull up
all implant information and then somehow choose the specific device/study to
filter out the rest of the information (not really sure how) but anyway can't
figure it out no matter what I try.

 

[John W. Vinson]

I thought I would be able to create a mainform using USDoctors Table and
then select a doctor and have a subform using the USImplants Table to pull up
all implant information and then somehow choose the specific device/study to
filter out the rest of the information (not really sure how) but anyway can't
figure it out no matter what I try.

You'll need to do some study to find out how relational databases actually
work. Treating Access as a black box into which you can dump unorganized data
and turn a crank to get out nicely formatted reports is overoptimistic!

Here are some resources to get started:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[KARL DEWEY]

As John pointed out you will always have a problem by having doctors names in
the implant table instead of using the ID matching that of the doctors table.

A way to get started on the right track is to run a totals query on the
implant table to check the names against the doctor table.

Create a query in design view using USImplants. Select LastName and
FirstName. Click on the icon that looks like an 'M' on its side ( ∑ ) and
save as DocCkImplant.

Create a second query using DocCkImplant and USDoctors. In the design view
drag DocCkImplant to the left of USDoctors.

Click on DocCkImplant LastName field and drag to LastName of USDoctors.
Click on the connecting line, then double click it. Select the option that
says to 'Include ALL records from DocCkImplant and only those records from
USDoctors where the joined fields are equal.'

Do the same joining for FirstName.

Drag LastName and FirstName from both tables into the FIELD row of the grid.

Below the LastName and FirstName from USDoctors, in the Criteria row type
Is Null for each.

When you run this query it will pull a list of names NOT in USDoctors table.
All the wrong spelling of name will show.

After you have corrected the spelling or added the omitted names to the
USDoctors table, open it in design view and make the ID field the primary key
by clicking on the gold key icon. Save.

Open the USImplants table in design view and add field named ID with
datatype to match the ID field of USDoctors ID field (ID field in USDoctors
is probably an Autonumber so the field in USImplants needs to be Number -
Long Integer.). Save.

Open the Relationship window, add both tables. Click on ID field of
USDoctors and drag to ID field of USImplants. Select options Referential
Integerity and Cascade Update. Save.

Open the query DocCkImplant in design view and click the icon that looks
like an 'M' on its side and note the Totals row disappears. Remove the 'Is
Null' from criteria row. Select Query Type Update. Note now there is a row
named Update To. Drag ID field from the USImplants table to the field row.
In the Update To row under USImplants ID field type [USDoctors].[ID]
and save.

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE

Run the update query.

Post back when done and someone will tell you how to build queries, form
with combo box with autoexpand, and subform.