Append Query With Parameters

[OasisUnknown]

Hello All,

I have an issue that I have read somewhat about through google searches but I cant seem to find the fix that I need.

So I have several tables that are all feeding into a master query (basically building a master view) for a report.

I have a personnel roster
A gains roster
and a loss roster

these three main tables all feed a query

The problem that I am having is load times on the reports it takes about 30ish seconds for the report to load which is not all that bad but I did not like it.

What I have tried to do that works well for the report is take that master query and I choose to run a make table query with it.

So now I have a table with all the fields that I need and all the data that I need which is awesome. I based my report on this table instead of the query and the load time for the report is now basically instant because it is not running the query each time it opens.

now the problem comes when I try to refresh the data on that table.

in my gains and loss queries I have a parameter

<=Now()+[Forms]![frmMainMenu]![MasterProjectionsLimitCBox] 'This grabs a numeric value from the main menu form ie 120 and it limits a date projection based off todays date plus 120 days.

The issue comes on the append query (I basically manually ran a make table query to get the table and then changed that exact query to an append query.

now when I refresh the database I run these two lines of code.

CurrentDb.Execute "delete * from tblMasterMOSI_Enlisted", dbFailOnError 'runs fine no issues with this code.  Just delete everything off the table.

CurrentDb.Execute "qryMOSIConsolidatedSummaryEnlisted", dbFailOnError 'produces runtime error 3061 too few parameters expected 1

Now through my testing this error occurs because of the parameter listed above.

Does anyone know why this is happening.
If I take out the parameter above then the append runs fine. With the parameter on the gains query it fails with the runtime error.


The only other thing I can think of to try is just do a manual insert sql command through vba but if I already have the query built I dont get why I cant just pass the data on the query to the table. I can do it manually through the toolbar just fine with no errors but when I run it through vba I get the error.

 

[OasisUnknown]

UPDATE:

I just attempted to run a docmd.openquery

DoCmd.OpenQuery "qryMOSIConsolidatedSummaryEnlisted"

And this actually works I was figuring the error of the parameter comes from the query not actually being loaded when it tries to execute.

I would rather avoid the .openquery command because of the warnings but for a work around for now I will just set the warnings to false and then turn them back on.

 

[Joe4]

Personally, I don't really like the process of have to use Append Query to create Temporary Tables to report off of. It is clunky, and will lead to database bloating (so you will need to Compact & Repiar regularly).

I would like to take a step back and take a look at why the original query is so slow.
How much data is in each of these tables, and how much data is being returned in the query?
Does each table have a primary key?
Are all the fields you are joining on or used in the criteria portion of your query indexed?

 

[OasisUnknown]

Thanks for the reply Joe4,

First let me note that I am probably using access for a purpose that is unintended but it is working for the result that I want which is my main theory about why the query is slow in the first place. I have been unsuccessful before about uploading pictures but if I can this time I will. Anyways to get to your questions.

"How much data is in each of these tables, and how much data is being returned in the query?"

I have about 6 main tables that hold the bulk of my data +/- 1 or two tables. The total number of records across all of these tables is going to be somewhere in the range of 1000 - 2000 records. typically on the lower end of that estimate.
What is being returned by my query is mainly just a view that is combining all of this data together.
1 table holds all of the current personnel data that is currently with my organization.
1 table is holding all the personnel data for people in coming to my organization
1 table is holding all the data for the outgoing personnel.

In essence my query is returning several things that is all displayed on one report.
it is returning how many people we are supposed to have, how many we currently have, what that percent is for how many vs how many we are supposed to have, How many are gaining, and how many are losing and then displays a projected number based on the number of days out I want to look (typically 4 months)

"Does each table have a primary key"
Yes but not an auto number. Each primary key is the person SSN number, I have attempted indexing but it makes no difference in the speed of my query.

The reason I am 99% sure my query is slow is calculated fields.

I have about 18-24 calculated fields in the query that is doing some over the top calculations to either make a number null on the report or display a number based on given criteria.
I also have calculated fields for the percentages so I dont divide by 0. Basically if I have 0 authorized but 1 assigned then I just put that as 100% rather than 0/1 which result in a div/0 error.

Lastly the calculated fields also check to see if auth+assigned+gain+loss = 0
and if all those fields sum to 0 then there is no reason to display that information so I just make that section of the report null so it displays nothing.

I did this to make the report easier to read because before I did the calculated fields I had about roughly 150 zero's displaying on the report which was overly distracting.

If it is 0 then there is no reason to display the information so I just made it null.

I tried to do these calculated fields on the report directly when building it but ran into limitations that were un avoidable.



Last note.

I only started looking into this transfering the query to a table because I am trying to merge this main report with a second report so I can see related information on the same report.

basically in simple terms I have a job title that displays all the information list above.
I have a second report that is also about that same job title with secondary information that is important but is not directly linked to what we currently have on hand.

so for the report that displays both I want it to basically state.

Jobtitle information
xyz about currently assigned personnel and gains/loss
jobtitle information extra detail
zyx about extra detail

jobtitle2 information
xyz about jobtitle 2
jobtitle2 information
zyx about extra detail.


(again I was having trouble with an image do I have to save the image somewhere online and then upload that? why cant I just choose an image from my computer?)

 

[xenou]

I would rather avoid the .openquery command because of the warnings but for a work around for now I will just set the warnings to false and then turn them back on.

This is standard operating procedure - not really something to avoid (necessarily).