Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Many Access DBs Using an External DB Table (MSOffice97 SR2)

    I need to retrieve specific rows of data from 12 access DBs (1 for each month) into a single DB Table. As a first step, I designed 2 queries to pull the rows and columns that are needed from each of the 12 DBs Assuming that I can combine the 2 queries into a single query, I would like to run this query, in each of the 12 databases, from another Access db and pull the results from each of the 12 dbs into this database. I need to automate this because I have at least 10 sets of 12 databases for which I need to run this procedure. The tables have 2 key fields user_id and case_id. Once the data are in one database table, I have a master list of User_ids and case_ids that I will use to pull data from the table for individual user_ids accross months.
    I work mostly in EXCEL using MSQuery to retrieve external data and am somewhat of a novice with ACCESS (I know how toquery, append, make table, etc.).
    I would greatly appreciate some advise on the best way to procede.
    Stephen

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

    Re: Linking Many Access DBs Using an External DB Table (MSOffice97 SR2)

    I don't understand what you're trying to do. Why do you have 12 databases and what do you mean by "10 sets"? The normal way to do this is to have a single database with 12 months of data in it. Then there isn't any need for elaborate workarounds to combine the data for reports.

    It sounds like you've taken spreadsheet data and converted it to Access without properly designing a database to hold the information. What you would normally do is design tables to hold the data and include a field to indicate the year and another the month, plus a unique key (preferably and autonumber) that uniquely identifies every record. Then all the data can live in a single database and all you have to do is query for the particular month and year you want to see at any given time.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Many Access DBs Using an External DB Table (MSOffice97 SR2)

    Hi Charlotte:
    Thanks for the quick response. I actually had nothing to do with setting up these monthly databases.
    Each of the databases has data resulting from QC exercises conducted for each of 10 different 'studies' that we are conducting. The 'studies' refer to diagnostic areas (Pneumonia, Stroke, Heart Failure, etc). Each study area has from 150 to 250 variables that we abstract from Medical Records. The monthly QC databases, one for each area, have data generated by comparing reabstractions, of a sub-sample of the records abstracted each month, to the original abstracted values. There are procedural reasons why they went to a system of archiving these databases by study area and month. I need to generate monthly accuracy scores by variable and by abstractor; I have nothing else to work with and I can't change the way they do things now.
    The rows of the QC databases correspond, mostly, to variable names, and the fields contain the case_id, the abstracted and reabstracted values, a variable to indicate match or mismatch, an adjudicated value, and a reason for the mismatches. The month and the abstractors' names appear in particular rows (not as a field or column name). Fortunately, I can put together a table with the record control numbers (case_ids), the month, the study name, and the names of the workers who abstracted and reabstracted each record.
    I need to combine, link, or whatever the 12 databases for each study, use the table with the case_ids to query the combined database and then export to EXCEL to construct monthly trends by variable (for, say the top 10 problem variables) and by abstractor across all variables. I also need to set it up as an application that the folks can run.
    I hope that I did not provide you with too much information.
    Thanks for your help.
    Stephen Stollmack

    <img src=/S/help.gif border=0 alt=help width=23 height=15>

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

    Re: Linking Many Access DBs Using an External DB Table (MSOffice97 SR2)

    Sounds like you have a substantial challenge. If you can't do as Charlotte suggests, then you can actually link to different databases for each month - there isn't any physical limit on the number of linked tables - only the total number of tables. From your description, it sounds as if the databases contain one basic table, so you would be dealing with 12 linked tables and one table to contain the record control numbers, etc.

    If however you are wishing to summarize data across months, then you would need to use union queries to make the data appear to be a single large table. In that case, I would suggest designing a new single table as Charlotte suggests, and append the data from each of the 12 databases into the new table. I suspect you don't want to learn Access SQL in order to complete your task.

    As to setting it up as an application, if your analysis isn't too complex, the Access 97 crosstab functions may solve your problems, and Access applications are much easier to bullet-proof than Excel. Good Luck.
    Wendell

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

    Re: Linking Many Access DBs Using an External DB Table (MSOffice97 SR2)

    You received an answer from WendellB. Was that of no use to you?

    The problem is that it's difficult to tell exactly what you're asking for. How many databases are you drawing from, 10, 12, 120, or what? You said there are 12 months and 10 study areas. How does that divide up into databases, and we are talking about MDB files, here, right--not tables? How many tables are there in each database and what exactly are you trying to do with them? What do you mean by "Each study area has from 150 to 250 variables" and "rows of the QC databases correspond, mostly, to variable names, and the fields contain the case_id"? Are you saying that the table has 150 to 250 *records*, one for each variable name, and all it contains is the case_id? Then is there a separate table for each study area in the database?

    What does this mean in English:
    <hr>The monthly QC databases, one for each area, have data generated by comparing reabstractions, of a sub-sample of the records abstracted each month, to the original abstracted values.<hr>
    I personally am a database developer, not a statistician, so statements like this leave me scratching my head.
    <hr>I need to combine, link, or whatever the 12 databases for each study, use the table with the case_ids to query the combined database and then export to EXCEL to construct monthly trends by variable (for, say the top 10 problem variables) and by abstractor across all variables<hr>
    Export what? Query how?

    We may be able to help you if you can describe specifically what kind of help you are looking for rather than what you need to accomplish overall. If you're looking for someone to help you write code, you have to be able to describe the actual process that you want to convert into code. What do you want code to do and where from? We can't guess at it and give you any effective help.

    As for linking versus importing, linking is always less of a problem in terms of database bloat, but it depends on the circumstances. Which data/tables/queries would you be linking/importing? Only the one(s) you want data out of or others as well? Are you going to use the data for anything else? Is it segmented by time periods? Are the databases on a network? How much memory does your workstation have and what class of machine is it?
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Many Access DBs Using an External DB Table (MSOffice97 SR2)

    <P ID="edit"><FONT SIZE=-1>Edited by Eileen on 07-Aug-01 22:34.</FONT></P>Maybe I sounded like I really knew something about linking or importing databases or tables into one database so that they can be appended to one table that can then be queried. Well, I do know how to do this one table at a time but I would like to know how to write a macro or some VBA code or whatever so I do not have to do it one table at a time for 12 months and 10 different projects. And I do know how to write SQL in ACCESS but I have never written VBA code in ACCESS. I guess I could figure it out but I don't know the most efficient way to bring all these database tables into one table. Could I get some advise re linking vs importing? I tried linking and I could not append the linked tables to a single table.
    Thanks
    Stephen

    (Edited to remove the emotional component. --Eileen)

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Many Access DBs Using an External DB Table (MSOffice97 SR2)

    Charlotte:
    I can now understand your problems in answering my last post. I have attached a WORD file which addresses each of your questions. Please note that my immediate objective is to be able to accomplish the first two of the three objectives noted at the end of this document.
    I clicked on my attachment and it appears to be messed up a bit. It did not open as a word document for some reason. So, I am removing the attachment and entering the text below.
    Let me answer your questions from the broadest down to the most detailed level.

    12 months and 10 study areas: = 12 mdb files (identical in structure) to combine or link to make a single mdb file that can be queried; this has to be done for each of 10 different study areas resulting in 10 different mdb files. These 10 mdbs will not be connected to each other in any way. I mentioned that there were 10 study areas only to illustrate why I needed to automate the procedures needed to combine the 12 mdbs into one;
    How many tables are there in each database:= 1
    Attached Files Attached Files

Posting Permissions

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