Solved

Using Dlookup in Update Query criteria

Posted on 2002-04-15
17
536 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
ID: 6942587
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
ID: 6942675
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
ID: 6942724
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 54

Expert Comment

by:nico5038
ID: 6942803
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
ID: 6942853
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
ID: 6942854
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
ID: 6942996
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
ID: 6943036
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
 
LVL 54

Expert Comment

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

Nic;o)
0
 

Author Comment

by:rlpotty
ID: 6945421
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
ID: 6945427
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
ID: 6945542
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
ID: 6945792
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
ID: 6945857
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
ID: 6946210
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
ID: 6947656
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
ID: 6948043
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 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

16 Experts available now in Live!

Get 1:1 Help Now