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].Val ue = DLookup("[NAME]", "SourceTable", "[AB] = '" & strMonthID & "'")
rstWorkingTable![OVER30].V alue = 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?
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
rstWorkingTable![Name].Val
rstWorkingTable![OVER30].V
...
...
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?
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?
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?
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?
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)
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)
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 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?
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 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].Val ue = DLookup("[NAME]", "SourceTable", "[AB] = '" & strMonthID & "'")
rstWorkingTable![OVER30].V alue = 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)
rstWorkingTable![NM].Value
rstWorkingTable![Name].Val
rstWorkingTable![OVER30].V
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)
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.
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)
Nic;o)
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.DEFINITIO N) 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?
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.DEFINITIO
& "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?
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.DEFINITIO N) 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?
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.DEFINITIO
& "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)
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)
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.
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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)
Success with your application!
Nic;o)
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)