Avatar of Blackbeltrrf
Blackbeltrrf
Flag 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.
Microsoft Access

Avatar of undefined
Last Comment
Blackbeltrrf

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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?
Blackbeltrrf

ASKER
Yes I have linked tables from my MAPICS
Rey Obrero (Capricorn1)

try the query with a join i suggested above.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Blackbeltrrf

ASKER
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
Rey Obrero (Capricorn1)

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
Rey Obrero (Capricorn1)

@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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Blackbeltrrf

ASKER
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
Rey Obrero (Capricorn1)

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

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Blackbeltrrf

ASKER
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
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Blackbeltrrf

ASKER
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!
Blackbeltrrf

ASKER
Thank you so very much for the help.  This works great!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.