Solved

Using Dlookup in Update Query criteria

Posted on 2002-04-15
17
490 Views
Last Modified: 2011-10-03
In Access 97, I have a source table that list 10 different factors for each month of the year.

After the application's user identifies the month they want to process, I want to populate another working table's fields with just that  month's factors.  The application will then use this table's fields as a source for performing calculations and creating headings in various reports.

I can accomplish my task by creating a working table recordset and then using a Dlookup function to assign a value from the source table into each field.

rstWorkingTable![NM].Value =  DLookup("[NM]", "SourceTable", "[AB] = '" & strMonthID  & "'")
rstWorkingTable![Name].Value = DLookup("[NAME]", "SourceTable", "[AB] = '" & strMonthID & "'")
rstWorkingTable![OVER30].Value = DLookup("[OVER30]", "SourceTable", "[AB] = '" & strMonthID & "'")
...
...

Instead of changing the value of each field individually, is it posible to use an Update query to change all the fields at once in the working table's record?  If so, how is the SQL written to accommodate all the fields?
0
Comment
Question by:rlpotty
  • 10
  • 7
17 Comments
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
For this I normally use a crosstable report.
The only problem is the fact that the different month values will also deliver a different fieldname.

I do have a piece of coding to fill a report generically by the name of the queries fields.
To use a crosstable report you'll need to have the 10 factors in separate rows. Is that the case?

Nic;o)
0
 

Author Comment

by:rlpotty
Comment Utility
The source table is set up:

Month
[AB]   [ Fctr01] [ Fctr02] [ Fctr03] [Fctr04] [Fctr05] .....

01        AAA       AAB       AAC      AAD    AAE ....
02        ABA       ABB        ABC      ABD    ABE ....
03        ACA       ACB        ACC      ACD    ACE ....
....
....
....

Is that what you mean in your question?
0
 

Author Comment

by:rlpotty
Comment Utility
The source table is set up:

Month
[AB]   [ Fctr01] [ Fctr02] [ Fctr03] [Fctr04] [Fctr05] .....

01        AAA       AAB       AAC      AAD    AAE ....
02        ABA       ABB        ABC      ABD    ABE ....
03        ACA       ACB        ACC      ACD    ACE ....
....
....
....

Is that what you mean in your question?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
No, for a crosstable query (and querying in general) the table should look like:
Month Factor
01 AAA
01 AAB
01 AAC
01 ...
..
02 ABA
02 ABB
02 ABC
...

Thus a crosstable query will deliver the table as described by you above and make AAA AAB AAC ... the column headers for january, ABA ABB ABC ... for february, etc.

Can you change the design ?

Nic;o)
0
 

Author Comment

by:rlpotty
Comment Utility
Yes, I could change the design of the source table.

 I glanced at Crosstab queries in Access help and am not sure that is what I want to end up with.  I guess I don't understand where you're going yet.  Could you continue to explain?
0
 

Author Comment

by:rlpotty
Comment Utility
Yes, I could change the design of the source table.

 I glanced at Crosstab queries in Access help and am not sure that is what I want to end up with.  I guess I don't understand where you're going yet.  Could you continue to explain?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I anticipated on:
rstWorkingTable![NM].Value =  DLookup("[NM]", "SourceTable", "[AB] = '" & strMonthID  & "'")
rstWorkingTable![Name].Value = DLookup("[NAME]", "SourceTable", "[AB] = '" & strMonthID & "'")
rstWorkingTable![OVER30].Value = DLookup("[OVER30]", "SourceTable", "[AB] = '" & strMonthID & "'")

Creating a table with:
AB Field  Value
01 NM     12345
01 Name   67890
01 OVER30 09876
etc
Will enable you to get from a crosstablequery one row with the fields named after the second column and as values the third column.
Assuming the above columns are the fieldnames then create a crosstablequery with AB as rowheader, Field as Columnheader and Value as value (e.g. MAX).

So far clear ?

Nic;o)
0
 

Author Comment

by:rlpotty
Comment Utility
That table redesign makes sense so far.

I'm about to leave at the end of the workday, so I'll have to look at it  tomorrow.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Just post a comment, I'll be back around the same time tomorrow ;-)

Nic;o)
0
 

Author Comment

by:rlpotty
Comment Utility
I'm back, nico5038.  Been working elsewhere and have now been able to work on this again.

Being an English speaker for all my life, Left to Right and Top to Bottom is how my mind normally sees things.   Modifying the Source table into this other format made my brain twist.

The source table is now set up:

NM       FACTOR        DEFINITION
01          Fctr01            AAA
01          Fctr02            AAB
01          Fctr03            AAC
...
...
02          Fctr01            ABA
02          Fctr02            ABB
02          Fctr03            ABC
...
...

 I then tested the following query in the query designer and ended up with results similar to what I've accomplished updating each recordset table field value with the Dlookup function.

strMonthNM = "01"

  DoCmd.RunSQL "TRANSFORM Max(CrossTabTest.DEFINITION) AS MaxOfDEFINITION" _
  & "SELECT CrossTabTest.NM" _
  & "FROM CrossTabTest" _
  & "WHERE (((CrossTabTest.NM) = '" & strMonthNM & "'))" _
  & "GROUP BY CrossTabTest.NM, CrossTabTest.NM" _
  & "PIVOT CrossTabTest.FACTOR;"

Using the results of  this cross tab query, how do I now refer to specific factor values in other code?    I guess I'm actually asking where the results of this query reside since I'm not updating an existing table or creating a new one?
0
 

Author Comment

by:rlpotty
Comment Utility
I'm back, nico5038.  Been working elsewhere and have now been able to work on this again.

Being an English speaker for all my life, Left to Right and Top to Bottom is how my mind normally sees things.   Modifying the Source table into this other format made my brain twist.

The source table is now set up:

NM       FACTOR        DEFINITION
01          Fctr01            AAA
01          Fctr02            AAB
01          Fctr03            AAC
...
...
02          Fctr01            ABA
02          Fctr02            ABB
02          Fctr03            ABC
...
...

 I then tested the following query in the query designer and ended up with results similar to what I've accomplished updating each recordset table field value with the Dlookup function.

strMonthNM = "01"

  DoCmd.RunSQL "TRANSFORM Max(CrossTabTest.DEFINITION) AS MaxOfDEFINITION" _
  & "SELECT CrossTabTest.NM" _
  & "FROM CrossTabTest" _
  & "WHERE (((CrossTabTest.NM) = '" & strMonthNM & "'))" _
  & "GROUP BY CrossTabTest.NM, CrossTabTest.NM" _
  & "PIVOT CrossTabTest.FACTOR;"

Using the results of  this cross tab query, how do I now refer to specific factor values in other code?    I guess I'm actually asking where the results of this query reside since I'm not updating an existing table or creating a new one?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
You have now a "crosstable" with one row for the month.
Just base a form (or other query) on this query and you're able to use e.g.:
=[Fctr01] * [Fctr02]
To fill a field on the form with that months product of the two fields.
Getting the idea ?
Is this what you were looking for or did I misinterpret the Q ?

Nic;o)
0
 

Author Comment

by:rlpotty
Comment Utility
I don't think this is what I want to use, because I don't seem to be able to make it work out with the result I want, which is a table with a record that I can mine.  

My original question was based on the statement in the DLookup Function description in Access Help, "You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query."

 I thought that using an update query might speed up my process which currently updates each field separately.  However, I couldn't find a specific example of a DLookup function being successfully  used in any kind of query in either Access help or on line tech sites, including MSDN.  I couldn't get a DLookup to work within a query in the query designer, and I can't identify what I'm doing wrong.  I just keep getting a message that Access can't update all the records in the update query.

So I'm stuck with using the method I got to work as described in my original question.

0
 

Author Comment

by:rlpotty
Comment Utility
I don't think this is what I want to use, because I don't seem to be able to make it work out with the result I want, which is a table with a record that I can mine.  

My original question was based on the statement in the DLookup Function description in Access Help, "You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query."

 I thought that using an update query might speed up my process which currently updates each field separately.  However, I couldn't find a specific example of a DLookup function being successfully  used in any kind of query in either Access help or on line tech sites, including MSDN.  I couldn't get a DLookup to work within a query in the query designer, and I can't identify what I'm doing wrong.  I just keep getting a message that Access can't update all the records in the update query.

So I'm stuck with using the method I got to work as described in my original question.

0
 
LVL 54

Accepted Solution

by:
nico5038 earned 200 total points
Comment Utility
You can use DLOOKUP in any form as it will return all results, including specified calculations like:
ResultOfFct1 =  DLookup("[fct1]*[fct2]", "SourceTable", "[AB] = '" & strMonthID  & "'")

In a query you can add a "field" for this result like:
ResultOfFct1:DLookup("[fct1]*[fct2]", "SourceTable", "[AB] = '" & strMonthID  & "'")

However it will slow the query down as the DLOOKUP is executed as a "query" for each row....

In an update query you can use the DLOOKUP in the value to be placed field and in the criteria section, but not as the field that needs to be set as that will always need to be just the "target" of the result.

If you drop a sample table and the needed report in my nico5038 mailbox "at" yahoo, I can try to make a sample.
When it's really complicated, then sometimes it's however easier to write a recordset processing function that arranges the needed data for the report in a temp table.

Nic;o)  
0
 

Author Comment

by:rlpotty
Comment Utility
I'll give up on this question.  It appears that the method I'm currently using is the easiest solution for getting the results I'm after.  It works, and,  best of all, I understand it.  The Update query/DLookup combination just doesn't appear to be the proper tool for my needs, and it is just too complicated and confusing.

Based on this experience, I'm also not too enamored with using the Cross Tab query within VBA code nor with building the associated tables. That query may have value in interactive analysis and data mining of some data base contents, so, if end users really want them, l'll make them available through menu options or as underlying data sources for reports.

Thanks for trying to help, nico5038.  I'll award you 100 points for your time and efforts.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Feel free (if allowed) to drop the zipped (part of) the database with your solution and I'll try to comment on that.

Success with your application!

Nic;o)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now