Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Relationships in access (Access 2000)

    I am working with Access for the first time, so bear with an obvious rookie question. How can I set up a table so that when I enter a specific piece of data (ex. medical record number) that it will cause another piece of data (ex. patient name) to appear in the appropriate spot? I tried following the Help files but I got a drop down list, which is great but really what I wanted. Any advice for a fresh young rookie (other than avoid Redmond like the plague <img src=/S/flee.gif border=0 alt=flee width=25 height=25> )

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships in access (Access 2000)

    You don't do this in a table. If you already have the medical record number in the table, the patient's name, date of birth etc. would be redundant information, because it is already available elsewhere. The proper place to look up the information is in a query. Start creating a new query in design view. Add the table you are working on (patient visits or examination results or whatever), and also the patients table. If you set up a relationship between these tables on the appropriate fields, that relationship shoud be shown as a line between the tables. If not, you can create a join now by dragging the patient id field from the patients table to the corresponding field in the other table. Now, you can add the fields you need from the "data" table, plus patient information fields from the patients table.

    See attached screenshot
    Attached Images Attached Images
    • File Type: png y.PNG (6.1 KB, 0 views)

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Relationships in access (Access 2000)

    The proper place to look up the information is in a query. Start creating a new query in design view. Add the table you are working on (patient visits or examination results or whatever), and also the patients table.

    So, if I do it as a query, it will "query" the patient table and add the patient name every time I enter a matching medicla record number, right? I haven't done databased since using old Buttonfile 3.0 (Dbase clone) over 10 years ago, so I'm a bit rusty with all this... <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships in access (Access 2000)

    That's correct. If you enter a medical record number into the vistis (or whatever) table, the query will get the corresponding patient name (if it exists) from the patients table.

  5. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Re: Relationships in access (Access 2000)

    I'm having problems. Maybe I don't understand this as well as I thought. I'm diong as you said, but not getting anywhere. Do I need to make a new table for inputing my data, with on of the fields being the query? I've tried to do as you said, but I'm still either getting a whole new screen or the same drop box as before. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Also, what query type should I be using?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships in access (Access 2000)

    Each of the objects in Access has its own role. A deliberately simplified overview:
    <UL><LI>Tables are for storing data in the most efficient way. See for instance <!mskb=288947>Microsoft Knowledge Base Article 288947<!/mskb> for some links about organizing your data.
    <LI>Queries are for gathering information from multiple tables and for calculations.
    <LI>Forms are for entering, viewing and editing data.
    <LI>Reports are for summarizing and printing data.
    <LI>Macros and modules are for automating repetitive tasks[/list]There is a lot more, but this is the basic idea.

    I have put together a small demo database illustrating a setup with two tables (patients and visits), one query (visits together with patient info), and two different approaches for entering data:
    1. A patients form with a visits subform.
    2. A visits form ordered by date. If you select a patient from the dropdown list, the name and birthdate will be entered automatically.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationships in access (Access 2000)

    Look over Hans's sample, but I would also urge you to get yourself some books on mastering Access to avoid getting even more confused in the short term. One good "quick" reference is Access Database Design & Programming, by Steven Roman. It won't teach you how to work with Access the way other books do, but it has a fairly concise review of relational concepts and a reference of properties and methods.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Relationships in access (Access 2000)

    Is this the same way I would do it if I were joining an Access2000 database to an Excel2000 spreadsheet?
    Kris

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Relationships in access (Access 2000)

    I'm sorry, but I don't understand the question. Is *what* the same way you would do it joining to an Excel spreadsheet?
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Relationships in access (Access 2000)

    Sorry for the delay. This is a work thing and I'm home for the holiday weekend. At the beginning of this subject, HansV gave instructions on joining two database tables with a query. I asked if that is the same way (with a query) that a database table and an Excel spreadsheet would be joined. I've been asked to add a field to my database which contains exactly the same data that is available in a spreadsheet. The data in the spreadsheet changes often, and is changed by several people (including me). I'd rather link the field to the spreadsheet cell than enter the data twice (once in the database and once in the spreadsheet) and have to monitor for other people's changes. I believe I need a field in the database and a cell in the spreadsheet which both hold identical data in order to link the two.
    Kris

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Relationships in access (Access 2000)

    Charlottte is on holiday at the moment, so I'll pinch hit. You do need to have a key item that appears in both the Access table and the Excel spreadsheet so that the Access record and the Excel row (Excel rows are treated like records when you link to them in Access)can be linked. Once that's done you can use a query to display the data from the spreadsheet on a form or whatever. Bear in mind that Excel is a single-user program, so if someone opens it from Excel, you will get a message from Access that indicates someone has the record open, and it gives you the saved version of the data. In addition, you probably won't be able to update values in the query, as Excel doesn't have a primary key. (You may be able to fake it by giving it a column name in Excel, but there's no guarantee that it will be a unique value.)
    All of which raises the question <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> why not do the whole thing in Access, which is multi-user <img src=/S/question.gif border=0 alt=question width=15 height=15>
    Wendell

  12. #12
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Relationships in access (Access 2000)

    Oh, <img src=/S/scream.gif border=0 alt=scream width=15 height=15> , I may end up doing it all in Access. Primarily, I am the one who updates the train that controls the Excel spreadsheet data. Others get their fingers into it once in awhile. If the relationship link works, I'd have to set up an update procedure to keep me in the loop of their changes.

    But since you asked <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15> the Excel spreadsheet is the ham in a sandwich. It starts with an Access2000 database written and maintained (thank goodness) by the systems folks. It feeds a real time update to software on the screens of telephone sales agents who need to see the data corresponding to each incoming phone call. But some of the agents are at remote locations and can't use that software. So, the Access file is exported to the Excel file which is saved to a tab-delimited .txt file which is downloaded to. . . . .I get lost right about there and really don't want to know.

    I don't want to link to their Access database since they can, and will, change it, move it, rename it, all those things which would kill the link. So I thought it might work better to link to the spreadsheet. If someone else does have it open and Access gives me the warning, as you described, that is a good thing. I would be down the hall to find out who's in there and what they're doing! There are column headings in the spreadsheet. I guess I will just have to try it and see if it will work. Thanks for the tips, hints, and warnings.

    My database records are stuffed with everything else they need to know, except for this one little piece of information. If linking isn't a nightmare to set up and maintain, it would save me from duplicate entry. But if it is a nightmare, I'll probably end up saving more time in the long run just doing the darn entry.

    I may be back. I have to create a field in my database to match one in the spreadsheet. I want to try to make the new field take the data from another field (the primary key) and put a "2" in front of it. The primary key field says 1234, the new field needs to say 21234. The 21234 is what's in the spreadsheet. Sure, I could enter it, but what fun is that!
    Kris

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships in access (Access 2000)

    If you have created the new field, you can use an update query to populate it. If the primary key is numeric, you can set the Change To value to [KeyField]+20000 where KeyField is the name of the primary key field.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Relationships in access (Access 2000)

    As Hans has suggested, the way to create a link value is to create a calculated expression in a query, and then link the Excel data to that.

    But I'm a bit <img src=/S/confused.gif border=0 alt=confused width=15 height=20> - what is the relationship between your Access database and the one maintained by system folks? As well, do the users have to look in your database for some information, and in the Excel workbook for other information? Or is it just you that do that? BTW, there is such a thing as a replicated database that can be use to keep people at remote locations up to date, and there are some other tricks as well if you have a decent speed comms link. Mostly my idle curiosity, but your link to Excel should work.
    Wendell

  15. #15
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Relationships in access (Access 2000)

    Hi WendellB,

    Actually, there is little relationship between my database and the system folks' database. Users (being the other 3 people on my team, not the phone agents) refer to their database when they need to know that one little piece of information. And they would like not to have to do that, thus the request that I put that information in my database where everything else resides.

    It is my job to update the data in the systems database, but not to maintain its structure. As I understand it, systems created an Access "front end" for me (and my team) to update. However, it connects to an Oracle8 database which connects to other software to create the real time screen information. Real time, in this case, meaning that the instant I enter the data in the Access "front end" it is visible on the phone agents' screens.

    The spreadsheet is created because, as we were told, Access is not capable of exporting into a tab-deliminted .txt file. The tab-delimited file is used by a program loaded on the computers of phone agents who work at locations outside the building and can't connect to the LAN and use the software that Oracle8 feeds. These agents don't look in either database. The information pops up on their screens. Stay with me, here. <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>.

    MY database takes item 1234 and tells everything you need to know about it, except. .well, you know. THEIR database takes item 21234, adds one field, and goes off to do it's thing. It took them nearly 3 years to get it working and it has to continue to do what it's doing. I think my group would just like to be able to see that extra information together with everything else we know about 1234. Once the data is in one place, it can be reported on, sorted, and otherwise played with.

    Although I looked briefly at replicated databases and security features, I opted for a plain old copy. At the end of each week, or after a major upgrade, I do a simple DOS copy from my protected user area to a shared drive. Good old ZTree.

    Wow, thanks HansV, for the direction in creating that field that copies the primary key data with a "2" in front of it. I never would have known that. I thought I could do it with a mask. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> You saved me hours. If I can't get it, I'll be back under a new subject. I've been writing so long I've put Lex to sleep.
    Kris

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •