Windows XP 
Access 2007
This is my first post to the forum, and I want to preface by saying that I am a career mainframe programmer (using COBOL/DB2) who recently started a temp to perm job which, among other things, uses Access 2007. So, pardon any ignorance on my part. I'm trying to get up to speed on Access quickly.
I have a report that is sent to us in Word or .pdf format. It is a bill of daily charges for storage. Basically, the first 24 items are stored for "free" (price included in original contract negotiation). Any additional are billed at the rate of $X per day. The items can belong to one of two divisions, but this is not specified on the report (as the company billing really doesn't care about breaking it down by our divisions, as long as our company pays)
Currently, someone is having to review that report each month, and for each item, go to an application program and determine the owning division based on a control office listed on the item. This takes between 5-8 hours, depending on the length of the report.
What I have done so far is this. I took the report and dumped it into Excel. I also ran a report that lists a summary of the control office for the items and saved as Excel. Then, I created a conversion table, matching the control office to the owning division in Excel. I imported these 3 spreadsheets into Access. I created queries to do the joins and created queries to basically add the owning division to the report.
There's one catch... one of the two divisions never gets a free item. In other words, if one of theirs falls into the first 24, they still need to pay for it (and add a freebie and take away a pay from the other division).
That's where I'm currently stuck. What I need is an update query or queries that will go through the report that now contains the owning division, and makes sure those adjustments occur automatically. I was reading up on how to update one row at a time in Access, but what I need, bascially, is.... if, for example, division A never gets the freebie, and division B always gets them, and on 5/1/2010 there were 3 freebies listed for division A, I need to change those 3 to paying $X and take 3 of division B's where there is a charge of $X and make it free. How do I accomplish that with queries so that only the amount of rows that need to be updated are updated? I want to be able to run the query or queries against the entire monthly report to correct the amounts, and then summarize it with queries summarizing the totals for the month, so that they can get that report in the matter of minutes and have the final, actual total, vs. spending 5 to 8 hours to work it.
Sorry for such a long post. Again, I'm fairly new to Access, and know there is probably an easy way to do what I want to do. But, just something I don't know yet.
Reply With Quote
