Link to home
Start Free TrialLog in
Avatar of Blackbeltrrf
BlackbeltrrfFlag for United States of America

asked on

Access query from multiple areas with multiple inquireies with one button

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:

My database:
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.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

you can create a query with a join on Maintbl.AR and Tbl1.AR


select Tbl1.* from
Tbl1 Inner join Maintbl On Maintbl.AR = Tbl1.AR


give more info on how you are getting the information from MAPICS.
do you have link tables from MAPICS in your database?
Avatar of Blackbeltrrf

ASKER

Yes I have linked tables from my MAPICS
try the query with a join i suggested above.
I have stripped all but what is needed from the database to conserve space and attached it for your review.  I may have missed deleting some of the elements so please disregard those.
Capital-and-ARs-for-Expert.accdb
i don't have a2010 at the moment.
if you can, create a new .mdb database
import the relevant tables (no link tables, i can't see them) to the new .mdb

upload the .mdb
@Blackbeltrrf

can you post the SQL statement of the query you mentioned in your OP, where you have to key in the AR number for each time for each query
OK I have taken all of the tables that I normally are linking to in the MAPICS and made them local.  I cut the number of records down due to space.  I also saved it as an MDB for your review.

A quick overview from the initial Switchboard:
"Enter New Capital Item"...This is where we enter a capital expenditure Idea (The top section of the form) and these items become our initial plan.

"View Capital Items"...From here we would add the details and add the AR number when it is approved to spend the capital.

"AR Status report"...This is the area where you have to keep typing in your AR number.  The first input box that comes up is the AR amount which is optional only if you want to see if you overspent the AR or not. Note(If you try this use any of these AR numbers, 45656...46203...46062)...It would be nice to not have to keep entering the AR number after each query runs from the macro.  The final results of the report and details do not need changed in the report.

"Total Capital in Plan"...This is a report that shows all the expected expenditures on capital.  You can see that the last column on the report is blank (Current Spend).  This is what I need to be summarized in this report for any records that have an AR number assigned to it (ie: the first column in the report)
 

In summary I need the Detailed spend info from the "AR Status Report" Spend summarized and include all AR's in the "Total Capital Plan Report".

I had to zip the file because it was still over 50 meg.

I hope I did not totally confuse you but I would be very grateful if you can show me how to accomplish this.
Sample-for-expert1.zip
Problems in your tables regarding the "AR number"

in table "ARMaintbl" the values are in the format "H1001", 45656

in tables "AMFLIBA_POITEM" and "AMFLIBA_POHISTI", Field REFNO the values are in format  "AR # 45656"


there is no way you can create a query using a join to the tables.

* you need to make the entry of AR numbers uniform in all your tables.
Yes I know.  That is the way that it has been entered into the MAPICS over time. They sometimes put for example AR#45656 or AR 45656 or 45656 or any other way they entered it with no consistancy.  That is why I have in my queries *[ARnumber]* so that they will find all entries.  The problem is I have to as you can see type in the AR number after each query is ran for the detailed report of a single AR.
AS a followup to my above post.  There are upwards of 250,000 records in the tables so to get them to change them in the MAPICS is not going to be something I can convince them of doing. :(
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry for the delay in response but got tied up on another project.  That works GREAT!  I have incorporated the changes and with a few queries have it all summarized in the report with a one click effort.  Thank you so very much for the help, you are truly the expert!
Thank you so very much for the help.  This works great!