Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23

    Using Record 'Templates' to add generic data to main table and linked sub tables

    Hello All!

    I have been trying to figure out a way to use record 'templates' (for lack of better term) with various combinations of generic data to create new records and speed data entry.

    I have a main table with PK linked to two other tables. I was thinking I could just copy the three tables and use these to hold my template data. The user would populate the generic data in the template tables that could (hopefully) then be used to create new records with that template data in my main table and linked sub tables. Once the user creates a template I was hoping there could be an easy way for the user to select that record template (probably from a combo box) to push that data to the main table. If it were just the single table I know I could use an append query to add a new record to my main table but I don't know the best way to copy data from one table (and its linked sub tables) into another table (and its linked sub tables).

    I could probably also figure this out with VBA but I am not the best with it and my code would most likely be pretty clunky.

    I hope this make sense what I am trying to do. Let me know if I need to clarify anything!

    Thanks everyone!


    Evan

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what I have done in the past is to have the template record in the table itself - assuming you are using an incremental autonumber to can set the primary key to a negative number to identify it as a template at the time you create the template - also means you can have multiple templates and if you have related tables you can set related templates to those as well. Just need to ensure you exclude then from any reporting.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can you give some data examples of the record templates. Ajax has good suggestion, keeping template records in the tables can save some headaches. Will they change or once they are set they will always be the same static data in the template? When they select a template and it is entered as a new record in the tables, can they edit it or are there other fields not in the template they will need to enter?

    Other ways to speed data entry when user is on a form is to use default values, conditional values so say if they enter Louisiana in state field, using AfterUpdate event, College field could default to LSU, etc. Can also use commands like Me.YourNextField.SetFocus to move to another field after a value is entered.

  4. #4
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23
    Thanks for the replies.

    If I were to hold the templates in the same table with negative values how would I create new records with them?

    The database is for personal fitness tracking. The main tables will hold records of each workout with related tables to hold data on what exercises (both strength training and cardio) were done during the workout. The templates would create new records for predefined workout routines that would populate generic info into the main table and each linked table. I am thinking the templates would populate a generic name, description, and the current date in the main form. And then populate a few exercises done for that workout in the linked tables. The sets and weight for example would be manually entered afterwards.

    My data structure is as follows:

    ID (PK)
    WorkoutName
    Date
    Description

    ID
    Workout(FK)
    StrengthTrainingExercise
    Reps
    Weight
    Comments

    ID
    Workout(FK)
    CardioExercise
    Sets
    Value
    UOM

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If I were to hold the templates in the same table with negative values how would I create new records with them?
    only the autonumber primary key would be negative. you would create new records in the same way you would if the templates were held in a different table, by appending a copy.

    However I am confused by your current data structure and description which does not look complete and perhaps can be organised more simply. How do you relate the exercise tables to the client? what is StrengthTrainingExercise and CardioExercise?- are they effectively the name of the table or have some other meaning like 'Program 1', Program 2' - perhaps some example data would help.

    Either way it looks like you need additional tables - something like

    tblTemplate

    templatePK autonumber
    WorkoutFK number
    ExerciseFK number

    and probably another table

    tblExercises
    ExercisePK autonumber
    ExerciseName text
    TableName text

    so when a workout is selected a query/vba can reference this table and append the relevant new records to each table populated with clientPK (which you don't have at the moment)

    However I suggest you need to review your current data structure. Is there any reason why all exercise profiles could not be in one table with some redundant fields depending on the exercise?

  6. #6
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23
    There is no need for a client because the database is for my own personal use.

    The thought behind having a StrengthTraining table and a Cardio Table is that they will have different data entered into them but you are probably right I could probably use one table and have redundant data if need be.

    I am not familiar with using a table name in a table to reference for queries. Could you explain further? I will also try to post some sample data to show you what I am trying to do.

    Thanks again for your help. I still have a lot to learn with access.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Could it all be in 1 table. Unless you have lots of other fields and processes, seems like all your tables would be 1 to 1 right?) Not sure you need template records, seem like when they add a new record, you just need to default some values? Do they do both Strength and Cardio during a single workout? Maybe something like:

    ID (PK)
    WorkoutName (Free text or Combo box with list of typical names)
    Date (Default to today's date)
    Description (prefilled based on WorkoutName?)
    WorkoutType (Not sure you need this if all in 1 table.)
    Reps
    Weight
    Sets (Default value if needed)
    Value
    UOM
    Comments

    Edit - sorry Ajax, just saw where you suggested this also and just saw your last post dl.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am not familiar with using a table name in a table to reference for queries.
    it is a bit of a cheat

    you would have a mainform with a subform

    With your structure, each type of exercise is in a different table - this can sometimes be valid

    so depending on the exercise chosen would determine what table to show in the subform

    me.subformcontrolnamehere.sourceobject="table." & tablename

  9. #9
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23
    I started over from scratch because it seems like I had some issues. Attached is my data structure and the basic structure of the data entry form that I am looking for.

    An example of what I would want a template to populate (as seen in the form) would be:

    MAIN TABLE
    Description: "Chest workout"

    LINKED TABLE
    Record1: Exercise: "Bench press"
    ExerciseValue: 10
    ExerciseValue: 10
    ExerciseValue: 10

    Record2: Exercise: "DB Fly"
    ExerciseValue: 5
    ExerciseValue: 5
    ExerciseValue: 5
    ExerciseValue: 5
    ExerciseValue: 5

    EDIT: Picture of data structure is wrong please disregard

    Click image for larger version. 

Name:	Capture.PNG 
Views:	10 
Size:	29.0 KB 
ID:	25661

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you'll need to explain what it is you want to do. Tell the story in simple english, no jargon, no reference to databases

    'I want to record my exercise routines - targets and achieved"
    "exercise routines are arranged in sets and undertaken in a specific order"

  11. #11
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23
    Sorry...I will try to be more clear about my goal here.

    I want to record my daily workouts in a DB. The idea is I would come home from the gym and enter in all the data from the workout I just did. Each workout would be made up of multiple exercises which would have multiple sets with varying reps and weight.

    To speed data entry, I want to be able to set up predefined templates. For example if I know I do the same chest workout regularly, I want to be able to create a template that would populate the workout name, the exercises that I do for that workout, and the reps for each exercise. Then I could manually enter the rest of the information such as notes, exercises I did that were not in the template, comments, weight lifted, etc.

    Does this clear things up?

  12. #12
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23
    I cannot put them all in one table because of the need for multiple exercises per workout and multiple sets per exercise. The reason I can't take advantage of default values too much is because the values are not always the same.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    getting there!

    so to define the relationships

    one workout can have many exercises
    one exercise can have many sets
    each set will have varying reps and weight

    so suggest tables would be something like

    tblWorkOuts
    WorkoutPK autonumber
    WorkoutName text


    tblExercises

    ExercisePK autonumber
    ExerciseName text
    WorkoutFK long - links back to WorkoutPK

    tblSets

    SetPK autonumber
    ExerciseFK long links back to ExercisePK
    Reps number
    Weight number
    Notes memo

  14. #14
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23
    Thank you for your help. I think have the data structure figured out now. I don't want the sets and reps to be linked to the exercises themselves as you have shown because they are subject to change on every workout. Here is the data structure I came up with.


    Click image for larger version. 

Name:	Capture2.PNG 
Views:	10 
Size:	19.6 KB 
ID:	25668

  15. #15
    dluga20 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Posts
    23
    My main issue was in using the templates to populate each table. I am slowly but surely figuring this out with VBA and INSERT INTO statements.

    WOTempID = Me.Text46.Value


    myWorkoutSQL = "INSERT INTO tblWorkouts ( Description, Notes )" & _
    " SELECT tblWorkouts.Description, tblWorkouts.Notes" & _
    " FROM tblWorkouts" & _
    " WHERE (((tblWorkouts.ID)=" & WOTempID & "));"


    WOLastRecord = DCount("[ID]", "qryWorkouts")
    WOLastRecordID = DMax("[ID]", "qryWorkouts")
    myExercisesSQL = "INSERT INTO tblWorkoutExercises ( Workout_FK, Exercises_FK, ExerciseData_FK )" & _
    " SELECT " & WOLastRecordID & " AS NewWorkout_FK, tblWorkoutExercises.Exercises_FK, tblWorkoutExercises.ExerciseData_FK" & _
    " FROM tblWorkoutExercises" & _
    " WHERE (((tblWorkoutExercises.Workout_FK)=" & WOTempID & "));"




    DoCmd.SetWarnings False
    DoCmd.RunSQL myWorkoutSQL
    DoCmd.RunSQL myExercisesSQL
    DoCmd.SetWarnings True


    Forms!frmWorkoutDc.Form.Requery
    DoCmd.GoToRecord acDataForm, "frmWorkoutDC", acGoTo, WOLastRecord
    Me.Text46.Value = ""
    Me.Description.SetFocus

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-22-2016, 06:59 AM
  2. Replies: 2
    Last Post: 06-25-2013, 08:35 AM
  3. Replies: 3
    Last Post: 01-03-2012, 12:28 PM
  4. Replies: 3
    Last Post: 06-14-2010, 06:48 PM
  5. generic information for table population
    By TheShabz in forum Access
    Replies: 3
    Last Post: 04-25-2010, 10:40 PM

Tags for this Thread

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