[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

Using Dlookup in Update Query criteria

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
rlpotty
Asked:
rlpotty
  • 10
  • 7
1 Solution
 
nico5038Commented:
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
 
rlpottyAuthor Commented:
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
 
rlpottyAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nico5038Commented:
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
 
rlpottyAuthor Commented:
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
 
rlpottyAuthor Commented:
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
 
nico5038Commented:
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
 
rlpottyAuthor Commented:
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
 
nico5038Commented:
Just post a comment, I'll be back around the same time tomorrow ;-)

Nic;o)
0
 
rlpottyAuthor Commented:
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
 
rlpottyAuthor Commented:
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
 
nico5038Commented:
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
 
rlpottyAuthor Commented:
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
 
rlpottyAuthor Commented:
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
 
nico5038Commented:
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
 
rlpottyAuthor Commented:
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
 
nico5038Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now