July 4, 2008 01:51pm pdt

1. boag2000 92,512
2. LSMConsu... 82,054
3. capricorn1 64,100
4. DatabaseMX 55,739
5. peter57r 40,741
6. harfang 29,154
7. matthews... 28,872
8. jimhorn 18,990
9. hnasr 15,150
10. jefftwilley 14,700
11. frankytee 13,650
12. nexusnation 10,400
13. GRayL 10,347
14. eghtebas 10,000
15. RgGray3 9,596

1. LSMConsu... 147,169
2. DatabaseMX 131,097
3. capricorn1 107,108
4. boag2000 100,487
5. peter57r 83,195
6. jefftwilley 60,468
7. matthews... 57,206
8. harfang 48,134
9. jimhorn 43,008
10. GRayL 30,779
11. frankytee 30,542
12. JDettman 28,775
13. stevbe 21,852
14. dqmq 21,760
15. MikeToole 21,750

05.16.2008 at 12:42PM PDT, ID: 23409565

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.8

Access Table Relationship Question

Zones: Access Architecture/Design, Microsoft Access Database

Trying to create a DB and need Help with Normilization, (I think that's what I need).

Why am I getting a one to one relationship in a table if I have the (Diagnosis Id) from the Diagnosis Table placed in tth Patient Table. Shouldn't that create the relationship in a one to many state?

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: Ange1ia

Solution Provided By: JDettman

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

05.16.2008 at 12:46PM PDT, ID: 21585838

Rank: Genius

DatabaseMX:

If the Foreign Key in the table you want to be 'Many' is set for No Duplicates, that will cause this. Set it to Indexed(Allow Duplicates)

mx

05.16.2008 at 12:49PM PDT, ID: 21585863

Rank: Genius

DatabaseMX:

By Foreign Key I mean Diagnosis Id in this case - in the 'many' table.

mx

05.16.2008 at 12:52PM PDT, ID: 21585885

Rank: Guru

hnasr:

That is right! Because if no duplicates then only one record is allowed, hence 1:1 :)

05.16.2008 at 12:59PM PDT, ID: 21585937

Rank: Genius

JDettman:

<<Why am I getting a one to one relationship in a table if I have the (Diagnosis Id) from the Diagnosis Table placed in tth Patient Table. Shouldn't that create the relationship in a one to many state?>>

Actually, if I understand you correctly, you've got it backwards (I'm assuming one patient can have many Diagnosis records). The setup you want is:

tblPatients - One record per patient
PatientID - PK
Name
Address
etc.

tblDiagnosis - One record per diagnosis
DiagnosisID - PK (Autonumber)
PatientID - FK (Foreign key to Patient Table).
....

This will give you a one to many. If the reverse, then I would hope that one diagnosis could only have one patient associated with it.

JimD.

05.19.2008 at 07:00AM PDT, ID: 21597824

Ange1ia:

To JDettman or anyone: I don't understand what you mean by the initials PK or FK. And I did try changing the Forign Key in the DX table to (yes allow duplicates) it gave me a message (Removing or changing the index for this field would require removal of the Primary Key)

05.19.2008 at 07:16AM PDT, ID: 21597963

Rank: Genius

JDettman:

PK = Primary key - A unique key within this table that identifies each record uniquely.
FK = Foreign key - A key that is a primary in another table.

Note that keys can be made up of one or more fields. There are also what are refered to as "Natural Keys" and "Surrogate Keys". Natural keys stem from the data itself, Surrogate Keys are assigned (ie. an Autonumber).

The little sample I posted was along surrogate lines. You have patients, each with a unique id. One record per patient.

Then you have a diagnosis. I believe that what you intended is that each diagnosis can only belong to one patient, but a patient might have many diagnosises.

so if tblPatient looks like this:
Patient ID / Patient Name
1 Jim Dettman
2 John Smith

tblDiagnosis might look like this
DiagID / Patient ID / Diagnosis / Exam Date
1 1 Cold 03/01/07
2 1 Flu 04/01/08
3 2 Cold 05/01/07
4 1 fever 05/02/08

JimD

05.19.2008 at 09:46AM PDT, ID: 21599413

Ange1ia:

Thank you JD for explaining that to me. I've put the Pt ID into the Dx table, and now I do get a 1 to Many relationship (Dx many / Pt one) is that correct.

However when I try to hook all of these tables up in a qry... it won't jive. When I run the qry, it's blank (not puling any records, well the db is empty so there are no records to pul but it doens't give me blank spaces to fill as it normally would). I am attatching a doc w examples. Can you tell me what I am doing wrong?

Essentially I need to have a qry which will function like a dry erase board in an OR (Operating Room) does. It will list all of the pts, their MDs, Dx, and the procedures they are having done. That's why I need the qry so I can pull all of this info in to the "Board". Plus I need a form which I will make off of that Board qry so they can just fill it in instead of a table. So of course I need all of this info to cascade update and delete. Therefore don't all of these tables need to have the relationships (one to many)? And why isn't the qry working???

Relationship.doc (68 KB) (File Type Details)

Relationships Window

05.19.2008 at 10:05AM PDT, ID: 21599588

Rank: Genius

JDettman:

<<Therefore don't all of these tables need to have the relationships (one to many)? >>

You don't always need to have defined relationships; you can join tables in a qeury without them. Defining relationships enables certain features in the JET database engine. For example cascading updates.

As for your relationships, the MD and Dx look good, but the boards relationship is backwards.

As for the query, not sure, it should work fine. What may be the problem though is the data itself at the moment. Right now, I'm sure what you have is called an equi-join. That is a key must be present in both tables in order for the record to appear.

In some cases, that will makes sense. For example, you want a list of all patients currently on a board. You'd join the board table to the patient table on BoardID and then for each board, you'd see only the patients currently assigned to it.

But let's say you wanted a patient report listing all patients even if they did not have a diagnosis. For that you need what's called an outer join. You want all records returned from one table (Patients) even if there is no matching record in the other (Dx). How you do that is just like you normally would, but after joining the tables, double click on the join line between the two and you'll get the join properties dialog.

You'll see that it's curretly set for an equi-join (matching records only). You need to select the second or third option, depending on what order you added the two tables to the query in.

Play around with that a bit and then bounce back with questions.

JimD.

05.19.2008 at 10:25AM PDT, ID: 21599738

Ange1ia:

Ok I deleted the Board ID out of the PT table and copied the PT ID and put it into the Board tbl. Now instead of a one to many it is giving me a one to one.

05.19.2008 at 10:39AM PDT, ID: 21599880

Rank: Genius

JDettman:

<<Ok I deleted the Board ID out of the PT table and copied the PT ID and put it into the Board tbl. Now instead of a one to many it is giving me a one to one.>>

Sorry, should have been clearer; the relationship itself was backward. You had the correct fields.

The board table should be this:

BoardID - PK - Autonumber
Description - Text

and tblPatient should have BoardID in it:

tblPatients - One record per patient
PatientID - PK
Name
Address
BoardID - FK to tblBoards
etc.

but you need to allow nulls as they may or may not be assigned to a board.

There will be one board and for each board, many patients on it. Now the sticky question; for one patient, can they be on more then one board at the same time? If yes, this is a many to many relationship and we'll need a slightly different design.

I should also mention that some don't like to leave Nulls in a FK field and would handle this by creating a join table, which is what you need for a many to many relationship anyway. Also, I'm not sure what your modeling this for, but I would expect that what board a patient is on would belong more in an Admitance table (with one record per visit) then in the patient table.

But let's keep things simple for the moment. The fact that the BoardID field is NULL would indicate that they are not currently being seen. That however would imply that they must always appear on a board if being seen. If that's not the case, then you'd want a seperate field for whether or not they were currently being seen.

If you think about what your doing step by step and in words, then the database design is pretty much just a lot of common sense.

JimD.

05.19.2008 at 11:43AM PDT, ID: 21600401

Ange1ia:

<<There will be one board and for each board, many patients on it. >>
Correct there is only one board. And a pt will be on it only once. Though there will be several bits of info about that pt going on. What's their Diagnosis. Are they having a CT & when, and so on for MRIs

<< If you think about what your doing step by step and in words, then the database design is pretty much just a lot of common sense.>>
I've tried that... There are many pts who will have one Dr and will be on the board only once with one diagnosis.
It really is like being in an OR (operating room) setting where there is a big dry erase board and it will have the pts name / surgeon / procedure / have they had their CTs or MRIs or whatever (yes/no) / Diagnosis

05.19.2008 at 11:44AM PDT, ID: 21600408

Ange1ia:

I'm increasing the pt value on this because it's more complicated than I first realized.

05.19.2008 at 11:57AM PDT, ID: 21600510

Rank: Genius

JDettman:

<<<<There will be one board and for each board, many patients on it. >>
Correct there is only one board. And a pt will be on it only once. Though there will be several bits of info about that pt going on. What's their Diagnosis. Are they having a CT & when, and so on for MRIs >>

OK. So that relationship was backwards then.

JimD.

05.19.2008 at 12:18PM PDT, ID: 21600683

Ange1ia:

Ugh! I thought I finally got all the info to go into the qry but now after I made a form off the qry and I try to fill that out it just "dings" at me.
I moved the join properties around until I got a pretend patient to show up and I would add stuff in the "form" and go back to the qry and make sure it was cascading but some of the tables won't let me add stuff into them from the form or in the qry it's just dinging at me. I am getting discouraged. I've worked on this all day long. I went back into the qry and tried to change the join properties but then I loose info.

05.20.2008 at 07:34AM PDT, ID: 21606510

Rank: Genius

JDettman:

If the DB does not contain sensitive data, why don't you either upload the DB to the question or send it to me directly. Based on what we've discussed, I'll get the relationships working and one or two of the queries you need.

Maybe once it's working and you can look at it, you'll be able to move forward from there.

e-mail address is jimdettman"at"earthlink.net, but the prefrence would be to attach it to the question if possible so all can look at it.

JimD

05.20.2008 at 07:53AM PDT, ID: 21606696

Ange1ia:

Let me work on that.

05.27.2008 at 06:59AM PDT, ID: 21651917

Ange1ia:

I figured the "read only" out. I have a join table now. Thats Listing Board Id and Description yes? Can you give me an explanation of what is happening now and why I need this and what this will allow me to do? I am increasing the point value since this has been more complicated then first thought.

Angie

05.27.2008 at 07:22AM PDT, ID: 21652108

Ange1ia:

In trying to create a qry and form, and entering info I am getting these error messages (doc attatched)

05.27.2008 at 07:25AM PDT, ID: 21652132

Ange1ia:

Let me try that again. See attatchemtnts

Error-Messages-1.doc (135 KB) (File Type Details)

Error Messages when trying to enter data

05.27.2008 at 11:43AM PDT, ID: 21654462

Rank: Genius

JDettman:

Here is a sample DB. As I said, I believe there should be some type of "visit" table. Appearing on a board, a Diagnosis, Doctors involved all revolve around a specific instance.

JimD.

PS. Sorry this took so long to get uploaded (I'm traveling).

DB-Project-5-22-08.zip (16 KB) (File Type Details)

Sample layout.

Accepted Solution

05.27.2008 at 01:52PM PDT, ID: 21655594

Ange1ia:

I don't know why this is not working. I have tired to create a qry, that would allow me to create a data entry form that would cascade update and delete as needed. Not working getting error messages (see attachment)

I also went into the tblPTand went to the MDid and changed that to include the row source as the tblMD so I could get a drop down of the doctors to choose from. That seems to work out.

Wanted to do the same with tblDx but had to change the data type to text in order for it to give me the drop down text options in the data view and that's not working out like I want it to.

New-error-message.doc (194 KB) (File Type Details)

Error Message

05.28.2008 at 04:47AM PDT, ID: 21659490

Rank: Genius

JDettman:

Your trying to do too much in one form. You can't bring everything together with one giant query.

First, you should a form to create and maintain records in the tblMDs table. This form should only work with that table.

Next, you need a form for entering patient info. Again it should only work with the patient table.

Third, you need a form that allows you to enter the diag info. This form would have a drop down to select the patient.

Last, you need a form for the board info. Again you would have a drop down to select the patient and the form would take care handling the info for the patient to appear on the board.

That's the best I can offer based on my understanding of what your trying to do. I think though that your having problems with this because the process is not in place first. Again I believe their should be some type of "visit" table.

Maybe it would be best before proceeding further that you provide a detailed description of what happens from first point of contact with a patient to when they leave. With a clearer understanding of what is required, we can come up with the correct database design.

And BTW, the error your getting is saying mothing more then the fact that you need a record in the MD table before you can use it in the patient table.

JimD.

20080236-EE-VQP-29 / EE_QW_2_20070628