Access query from multiple areas with multiple inquireies with one button

Blackbeltrrf
Blackbeltrrf used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
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?

Author

Commented:
Yes I have linked tables from my MAPICS
Top Expert 2016

Commented:
try the query with a join i suggested above.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Top Expert 2016

Commented:
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
Top Expert 2016

Commented:
@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

Author

Commented:
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
Top Expert 2016

Commented:
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.

Author

Commented:
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.

Author

Commented:
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. :(
Top Expert 2016
Commented:
try this with the query POqry

1. remove the criteria under REFNO  
2. add a column as shown

Field :   Expr1: InStr([REFNO],[CapitalMainTbl].[AR Number])
Table
Sort
Show                  [unchecked]
Criteria     > 0


x
.

Author

Commented:
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!

Author

Commented:
Thank you so very much for the help.  This works great!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial