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

[estevan.sanchez]

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.

 

[OBP]

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.

 

[estevan.sanchez]

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).

 

[OBP]

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.

 

[estevan.sanchez]

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.

 

[estevan.sanchez]

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.

 

[OBP]

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?

 

[estevan.sanchez]

I can send you actual reports via e-mail but our system at work does not let you zip files here.

 

[estevan.sanchez]

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.

 

[OBP]

It doesn't appear to have allowed it, perhaps you do not have enough posts yet.
Did it say it had uploaded it?

 

[estevan.sanchez]

Yeah it says it uploaded. Let me upload it again. I named it OMR>

 

[OBP]

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.

 

[estevan.sanchez]

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.

 

[estevan.sanchez]

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.

 

[OBP]

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