Results 1 to 5 of 5
  1. #1
    TracyRL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    2

    Replicating Data from One Table to Another Based Upon Combobox Response

    I am a research analyst with some Access experience. While I do some programming, I am definitely not a professional; I know enough to get by but am stuck on a particular aspect of a database that I have created for a research project. I would greatly appreciate any insight into how to resolve this issue.

    The database I've created is somewhat unique in its structure in that it will be used for four different, but related programs. The database has multiple tables/forms - one table stores intake/exit data related to all four programs (main table with an intake and an exit form), while the other tables/forms are program specific (program a table/form, program b table/form, program c table/form, program d table/form). Basically, all clients will have data in the main table but the program specific tables will only include data for clients enrolled in that program (e.g. we could have data for 500 total clients in the main table but only data for 100 clients in program a table, 200 clients in program b table, 50 clients in program c table and 150 clients in program d table).

    Identifying information for the client, including name and case number is entered into the main table using the intake form. A combo box on the intake form is used to indicate the program the client is enrolled in. What I would like to figure out is how to get the name and case number to populate in the correct program table without having to re-enter the data. I tried to do this using subforms, but couldn't get it to operate the way I wanted it to using the response in the combo box. Is there a way to do this using a query? The case number must be used to join the tables in the Access database; it also needed when merging data from external sources. The database will easily contain thousands of records so I'd like to figure this out before we start entering data.

    Thanks for your assistance!



    Tracy

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Sounds like you need these tables:

    tClients
    -------
    [clientID]
    [name]
    [address]
    etc

    tPrograms
    ---------
    [progID]
    [ProgramName]
    etc


    tClientProgs (clients enrolled in program)
    --------
    [clientID]
    [progID]
    [StartDate]

    and yes, a subform would be the way to go , if you have the parent/child ids set correctly.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum..


    Identifying information for the client, including name and case number is entered into the main table using the intake form
    Will a client have only ONE case number?

    Why 4 separate tables? What is the table structure of tables a, b, c and d?
    Also, what is the structure of the main table?


    how to get the name and case number to populate in the correct program table without having to re-enter the data
    In a relational database, you don't store the data in two different tables.You set up a relationship between the two tables using primary/foreign keys.

    So the main table PK field is stored in a field in table a, the FK field. Then you can create a query and view the data from the main table and the related table (table a).

  4. #4
    TracyRL is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    2
    Thank you for such prompt responses. I'm a little stuck on how to create the sub-forms. How do I get the correct form to display, based upon the response to the combobox? I found some vb code to do this but I couldn't get it to work. I'm not sure if it was user error, or the code just wasn't appropriate for what I was trying to accomplish.

    In the database, a client could have multiple case numbers. When this occurs, it's because the client has gone through the program(s) more than once. For each engagement, however, the client will have one case number.

    I created four separate tables because the program components vary. We are collecting different information for each program - there's very little overlap- and I thought that using just one table would get too unwiedly. As they are currently structured, each program table has 20-30 unique variables. I suppose we could create a separate database for each program but we really want to consolidate this and have everything in one place - we want to be able to quickly pull info on all program participants and compare progress across programs. The database is for an on-going project; we will update it regularly. A temp/intern will be doing the data entry but program coordinators and research staff will be accessing the data in real time. I want to ensure that the temp will be entering the right data in the right form - for this reason, I thought it would be helpful to have the name and case number display on the program form.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ok, 4 tables... got it.

    This is how I would start off with the table structure:
    Code:
    tClients
    -----------
    ClientID_PK Autonumber
    LName       Text
    FName       Text
    Address     Text
    City        Text
    State       Text
    Zip         Text
    
    
    tPrograms
    ---------
    ProgID_PK Autonumber
    ProgName  Text
    ProgDesc  Text
    
    
    tClientsProgs  (junction table)
    -----------
    ClientProgID_PK    Autonumber
    ClientID_FK        Long    Foreign key to Clients table
    ProgramID_FK       Long    Foreign key to Programs table
    CaseNumber         Text
    StartDate ??       Date
    Other fields
    
    
    tProgram_A
    -----------
    Prog_A_PK  Autonumber
    ClientProgID_FK  Long    Foreign key to ClientProgs table
    other fields for Program_A
    
    
    tProgram_B
    -----------
    Prog_B_PK  Autonumber
    ClientProgID_FK  Long    Foreign key to ClientProgs table
    other fields for Program_B
    
    
    tProgram_C
    -----------
    Prog_C_PK  Autonumber
    ClientProgID_FK  Long    Foreign key to ClientProgs table
    other fields Program_C
    
    
    tProgram_D
    -----------
    Prog_D_PK  Autonumber
    ClientProgID_FK  Long    Foreign key to ClientProgs table
    other fields Program_D

    Client info entered once. Client can be in multiple "programs" with different case numbers.

    As to forms, obviously I don't know anything about the process, but I wold think the data would be in batches by Program.
    One way would be to select the program (combo box?), select the client and open a form to enter the data. You would need some way to open the correct form for the correct program table.

    --------------
    Only use letters, numbers and underscore in names. No punctuation or special characters.

    Use a query for the form record source. It is easier to sort and filter than if the record source is a table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-24-2014, 09:08 AM
  2. Cascading combobox based on related table
    By rhewitt in forum Programming
    Replies: 3
    Last Post: 06-06-2013, 02:24 PM
  3. Replies: 4
    Last Post: 02-09-2012, 03:03 AM
  4. Replies: 3
    Last Post: 07-12-2011, 02:02 PM
  5. Replicated New Tables Not Replicating Data
    By smyers in forum Access
    Replies: 0
    Last Post: 04-30-2010, 01:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums