Link to home
Start Free TrialLog in
Avatar of rlpotty
rlpotty

asked on

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?
Avatar of nico5038
nico5038
Flag of Netherlands image

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)
Avatar of rlpotty
rlpotty

ASKER

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?
Avatar of rlpotty

ASKER

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?
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)
Avatar of rlpotty

ASKER

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?
Avatar of rlpotty

ASKER

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?
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)
Avatar of rlpotty

ASKER

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

Nic;o)
Avatar of rlpotty

ASKER

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?
Avatar of rlpotty

ASKER

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?
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)
Avatar of rlpotty

ASKER

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.

Avatar of rlpotty

ASKER

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.

ASKER CERTIFIED SOLUTION
Avatar of nico5038
nico5038
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rlpotty

ASKER

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.
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)