1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2003 - Updating multiple records from a form to table simutaneously

Discussion in 'Business Applications' started by estevan.sanchez, Oct 2, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    Semi beginner in Access 2003. My problem is I think a little complicated so here goes. On a specific week, our department will get a list mail renewal packets with 5 different expiration months. So I have to find a way to create a form (which I have done) with a week ending date that will allow the user to enter multiple expiration month dates. I figured how to do one at a time and get the form to update but can't figure out how to update all of them at the same time. The problem I think has to do with the control source but not sure. so here's a sample of the info:

    For week ending 9/20/2008,
    August September October November December
    Opening Balance 1000 1200 1300 1500 1600
    Received in Unit 200 100 50
    Deferred
    Deferred Closed
    Deferred Pilot
    Deferrals Return
    Processed
    Closing Balance
    Mail Room Count

    So I set up a form similar to the look above but can't figure out how to update it to a single table that has all the left handed information on it. Please help!!!!!

    Thanks a million... You know bosses they want it particular way.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you explain your Table designs please?
    I know what you are saying about Bosses, but what they see and what the Database developer does can be 2 completely different things. :D
     
  3. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    Two tables that are:

    1. Teams, which has a Team ID and a Team Name.
    2. The other is called Input Info which contains the following:
    - Date
    - Team Name
    - Expiration Month
    - Opening Balance
    - Received in Unit
    - Deferred
    - Deferred Closed
    - Deferred Pilot
    - Deferrals Return
    - Processed
    - Closing Balance
    - Mail Room Count

    The team table I put up just to run reports by groups of teams (A, B, C, D, E).
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    So the only thing that the Form is for is to Add the "Expiration Month Dates".
    Can you explain what they are and how they are derived?
    It is quite possible that you do not need a form but a Query instead, but I am not sure how you arrive at an Expiration date.
     
  5. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    I was also thinking of making a Input info month #1, input info month #2, input info month #3, etc... so that I could actually assign them to different tables and would be updated simutaneously to different tables instead of only to one table. Could easily put a control source but not sure this would work.
     
  6. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    Well I work for Medicaid and they get these renewals every week via mail. So the secretary gets the final numbers of what each department has received in the mail. She is now data entering about 5 different reports and my boss wants her to enter the info only once. Let me be a little more specific. She gets the input info information from each department every week in spreadsheet format. The mail that we receive though on one week might have expiration months of September, october, november, december, january and february (max of six months). So what I had done was basically done a form so she can enter the info and it updates the table. But when I presented it to my boss, he wanted a specific format -- for the table input info to be put on the form along with 6 blank spots to deliniate what the expiration month is. I hope this is making sense.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you post a zipped copy of your database with some dummy data in it, so that I can see what the data looks like and how you are trying to get it to work?
    Are the Secretary's Excel Spreadsheets all of the same Format?
     
  8. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    I can send you actual reports via e-mail but our system at work does not let you zip files here.
     
  9. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    Think I uploaded the files. Database is raw but you know just started.

    The OMR Summary Report is what she uses to input and the other reports are reports that I will need to print out.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    It doesn't appear to have allowed it, perhaps you do not have enough posts yet.
    Did it say it had uploaded it?
     
  11. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    Yeah it says it uploaded. Let me upload it again. I named it OMR>
     

    Attached Files:

    • OMR.zip
      File size:
      218.9 KB
      Views:
      56
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Estevan, I think that I am beginning to understand what you are trying to do.
    Am I correct in thinking that you are reproducing the Excel Worksheet "Workload Report 9-06-08".
    I can now understand your confusion and "PROBLEM".
    What I don't understand is your Boss!
    I can't see any advantage to putting the data into Access when it is already in Excel unless you are going to take advantage of Access Relational Database features.
    Is he just looking for somewhere to "Store" the data longterm?
    Access doesn't work like Excel and therefore it is very hard to reproduce Excel type layouts without losing those Access advantages.
     
  13. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    Thank you for not giving up easily! I was going to fight to keep Excel but the thing is all those reports, the secretary types in so he wants to cut down on the amount of data entry required. I had originally set up the form to only have one month expiration date, which worked great but wasn't the format that he wanted. The problem with Excel is that when the next week changes, you have to in put the information 3 times and can't really do anything with formulas because adding new columns every week and would be bothersome to change every week. Access was supposed to give us the ability to enter this info more easily and run the same reports cutting down on 2/3 time of data entering. Is there any way to make my boss happy? The other thing I had thought of doing was copying structure of all the table and making it 6 tables that each hold a month of information. Then when running reports get info from each of those tables but not sure.
     
  14. estevan.sanchez

    estevan.sanchez Thread Starter

    Joined:
    Oct 2, 2008
    Messages:
    14
    The workload report would be one of the reports that would be generated but the information would come from OMR Weekly Report. She enters all this information 3 times into the other reports including the workload report.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Estevan, that is not the way to do it, you will probably need more than 2 Tables, but the Raw data should go in to one Main Table.
    Can I ask some more questions, will the Other Offices/departments still be using Excel, or is the intention to have a Central Access Database?
    The first thing to try and explain to your Boss is that efficient Data Input is the most Important aspect, not how the input forms look, the Output (output Forms and Reports) is where the "Look of the Data" comes in to play.
    I don't know if he is aware of the fact that you are using this Forum, but if he is then please quote me and show him these posts
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/755423

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice