Best method to read calculated information from one access query to another.

Ralph Benoy
Ralph Benoy used Ask the Experts™
on
Hi Guys,
Back again. In a bit of a steep learning curve.
At the moment I am using plain Access with no Visual Basic.
I have one query that calculates certain values, approximately 400 values in one column.
I then used DLookUp to read these values into a second query.
I then use these values to to calculate other values.
when viewing the second query it takes a very long time to display the results. (I take a walk while it is working)
I have searched Google and it seems that they do not recommend DLookUp for more than one value.
one of the ways around this is to
write a macro to make a temporary table, then update the values into a main table which is linked to the second query.
The difficulty I have is that it is not instant, it is more complicated especially when compiling the second query.
Does anyone have any advice for me.
Thanks a Lot.
Regards
Ralph
(Was taught on basic then Turbo Basic a long time ago, now must learn Visual Basic soon)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It appears to me that you are severely overcomplicating matters, so my best advice would be to look up a beginners tutorial in Access and/or SQL to get a better understanding of how to create and use queries.

/gustav
Ralph BenoyProfessional Civil Engineering Technologists

Author

Commented:
Hi Gustav,
Thanks for your reply.
I am a civil engineer and am busy automating my excel spreadsheets converting them into access. So they are going to be complicated.
Regards
Ralph
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
if you are trying to replicate your Excel formulas in a query, I would advise that you start creating Access user defined functions.

When you create a complex combination of IIF() or SWITCH( ) or even just long math equations in a query field, you have no way of expressing to the next person who takes on maintenance of you application what you were trying to accomplish.  with a user defined function, you can add comments into the code and it is much easier to handle errors (especially where values fall outside of the expected ranges) and return unexpected results.

I would concur that DLOOKUP is not the way to go, especially within a query.  Maybe you could provide a sample of the spreadsheet functionality you are trying to replicate and we could provide some useful recommendations.
Ralph BenoyProfessional Civil Engineering Technologists

Author

Commented:
Afternoon Dale,
Thanks for your good advice,
I know that I am been idealistic but for me the most ideal program would be a program that encompasses Word, Excel, Access, etc.
I have written extensive excel spreadsheets that include all aspects of my business including among other contact lists, quotes, letter, designs reports for earthworks, roads, storm water, water, sewer etc.
I later combined them into one so that you only have to insert information once.
This worked well but became very complicated when adding new projects.
From my brothers advice I have started to recreate this in Access.
I must admit that to add projects, other information and simple calculations it is miles better but
it is a bit more difficult to do complex calculations with iterations, multiple live reference tables (queries) etc.
At the moment I have gone for the make table and update table options as I am also chasing design deadlines.
When I have the time I will compile a sample database / spread sheet for you to comment on.
Thanks again.
Regards
Ralph

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