Results 1 to 9 of 9
  1. #1
    lmcm1977 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2018
    Posts
    9

    Importing from excel - structuring of new table in access

    Hi



    I am having some difficulty structuring my access database to minimise data entry for this monthly episode diary that I currently have in excel. I have my database all set up with the one to many relationships with the study ID as the primary key and then this appears in all other tables as the foreign key.

    I am struggling to structure the monthly episode diary in access.

    In excel I type it once at the top but the way I have set it up means I would have to keep entering the date month for each of the 31 days of the month and also keep entering the study id 31 times for each month.

    Is there a way I can auto populate the study id and month field so that they will be automatically entered for each of the day of the month?

    I have a screen shot below of how it looks at the moment in excel – I basically want an easy way to fill down for study id and month as that will be the same for each day of the month.

    The second screen shot is how I would want it to look in access – if possible!

    Thanks in advance!

    Lynn

    Click image for larger version. 

Name:	EXCEL FORMAT.png 
Views:	21 
Size:	170.6 KB 
ID:	36478Click image for larger version. 

Name:	ACCESS FORMAT.png 
Views:	21 
Size:	207.0 KB 
ID:	36479

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    I think you'd need a structure somewhat like:
    Click image for larger version. 

Name:	Structure.JPG 
Views:	22 
Size:	34.2 KB 
ID:	36489

  3. #3
    lmcm1977 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2018
    Posts
    9
    Hi Noella

    If i used a similar structure to above - could i use a form to input the data and this would avoid me having to enter the month for each day of the month and also the study ID for each of the 31 days. How would i "auto-populate" those fields with the same repeating data?

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    you can use a VBA procedure that adds a set of records for all active students.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hi Lynn, welcome to the forum.

    In the Excel example, you have columns for Episode A, Episode B, Episode C, Episode D & Episode E. What do you enter in these columns?


    In Access, the Episode type would be DATA. There would be one column. If there were 3 different episodes (A,B,D) on 12/ 2 for Study 123456, there would be 3 records for that day (in a properly normalized database).

    Data entry would be done using a Main Form/Sub Form design. (Never enter data directly into tables.)

  6. #6
    lmcm1977 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2018
    Posts
    9
    Quote Originally Posted by ssanfu View Post
    Hi Lynn, welcome to the forum.

    In the Excel example, you have columns for Episode A, Episode B, Episode C, Episode D & Episode E. What do you enter in these columns?


    In Access, the Episode type would be DATA. There would be one column. If there were 3 different episodes (A,B,D) on 12/ 2 for Study 123456, there would be 3 records for that day (in a properly normalized database).

    Data entry would be done using a Main Form/Sub Form design. (Never enter data directly into tables.)

    Hi Steve

    thank you for your reply. In each of the episode columns there would be a number for how many of that type happened each day. So this would be repeated for each day of the month and then for the subsequent months.

    I plan to create a form with each of the months in tabbed subforms at the bottom.

    I cant quite get my head around how to structure the table to have the form recognise that its just one name field that relates to all of the 31 records for the month and then each of the months. How would i set it up so that the name field isnt required to be entered 31 times?

    Best wishes
    Lynn

  7. #7
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    If you are entering the data in access, and you are entering the date, you can then use the After Update even to populate the Month and Day into the Month and Day fields automatically. Those fields would not even need to be visible on the form, just in the background table.

    StudyYear: Year(StudyDate)
    StudyMonth: Month(StudyDate)
    StudyDay: Day(StudyDate)

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by lmcm1977 View Post
    I plan to create a form with each of the months in tabbed subforms at the bottom.
    Not the best way to proceed.


    Quote Originally Posted by lmcm1977 View Post
    I cant quite get my head around how to structure the table to have the form recognise that its just one name field that relates to all of the 31 records for the month and then each of the months. How would i set it up so that the name field isnt required to be entered 31 times?
    This is quite common with people coming from an Excel background (myself included ). It is so common it has a name - "Committing Spreadsheet".
    Typically an Excel spreadsheet is short and wide. An Access table is typically narrow and tall.
    In your Excel spreadsheet, there are 5 columns for the episode type. In an Access table, there would be 1 field for episode type, but 5 records.
    Note: an Excel column is kind of like an Access field and an Excel row is kind of like an Access record. But they are two totally different animals!


    Maybe this demo dB will help. Please forgive me if I have made a faux pas with any of the names. I made up some data just for demonstration purposes.

    There are two people entered. When you open the dB, the first person will be selected and the data is in the sub form. There is a unbound field in the header that will allow you to filter the episode data to a single date. Select a date and press the TAB button or ENTER.

    Select the second person to see his data.
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    The best way to add new records automatically is to use Open event of main form (the form which is opened automatically whenever the database is opened). The Open event procedure:
    1. Checks, are needed records added, or not;
    2. When not, then adds new records.

    It is not that something catastrophic happens, when you have different types of episodes in separate fields, but you have to consider pro's and contra's:
    Pro
    1. All data entry for certain user at certain day will be in single row in daily activities subform.
    Contra
    2. As certain, you will have empty fields in daily activities table;
    3. When you need add a new type of activity, you have to redesign whole database (forms, procedures, reports, etc.). When you have activity type as field in table, you add new activity type, and you can use it.

    For data entry, you can consider 2 different approaches:
    1. The main form is a single form based on studies database (Btw, what you mean with 'Study'?). In daily activities database, you have a calculated field for month number (e.g. in format yyyymm). In main form you have an unbound combo to select month. You have a bound (to study ID and to month number) continuous subform in main form, where all daily activities for this student at selected month are displayed (either a record for every day, or a record for every activity type at every day). There is no need for calculated day number in daily activities table (and in subform) - a date field will do. In subform, you can set format of control for date as "dd";
    2. The main form is unbound single form, with unbound control where a date is entered. The main form has bound (to date) continuous subform for daily activities - all activities of all students at this date are displayed. With this setup, you don't have the need to insert records automatically at all - whenever you enter new record into subform, the date field in daily activities database is filled automatically. This means also, there will be a record(s) in daily activities table for certain student at certain date only when there was some activity for this student at this date.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-16-2018, 12:37 AM
  2. Replies: 6
    Last Post: 12-18-2017, 07:05 PM
  3. Replies: 5
    Last Post: 10-28-2016, 08:03 AM
  4. importing multi- rows excel table into access
    By ahmed sami in forum Access
    Replies: 1
    Last Post: 10-28-2014, 01:08 PM
  5. Formatting an Access table after importing from Excel
    By bigern87 in forum Import/Export Data
    Replies: 3
    Last Post: 02-13-2013, 07:08 AM

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