I have a problem that is probably easy but I hope I can explain.
I have a database to track Appropriation requests (AR's) spending. My database needs to go out to our MAPICS system and gather information of what has been spent against a particular AR and compile the results in a report. This information is in 4 different areas of the Mapics system so I have written the queries to go to each area and get that information and bring it in. All is working fine with the following problems: 1. I have to key in the AR number for each time for each query. 2. I can only get the information for only one AR number at a time. What I have been tasked to do is to hit a button and my database look at all the AR numbers that are in my database and get all the information for each one of them in the mapics system and bring them into my database so I can have a summary report with all AR's in one report.
A simplified example of what I mean:
Maintbl Fields: AR number(Inputted)....Description(inputted)...Anticipated spend(inputted)...Actual spend(Get from MAPICS)
from Mapics area one:
Tbl1: AR number...Actual spend(this is in the mapics with all the Active PO's tied to the AR number)
from MAPICS area two:
tbl2: AR number...Actual Spend(This is in the MAPICS with all the Closed PO's tied to the AR number)
There are other areas that this spend information resides like Archived PO's, Toolroom costs, etc but for simplistic reasons if I figure out the examples above I can incorporate the rest.
I hope this is clear and someone can help me with this.
Again to sumarize, I have this working with quiries running back to back but only if you type the AR number after each query plus I can only do one AR number at a time.