If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query on a Query
I have a query that has presented the following result: Name, Competition
Name and Playing Option. I also have a separate table that contains the Competition Name, the various Playing Options for the Competition and the Cost for each of the options. This table is not linked to anything. I would like to find the Cost based on the Competition Name and Playing Option. I tried a DLookUp but got an error and wondered if I got the DLookUp wrong or whether I was barking completely up the wrong tree! Actual Cost: DLookUp("[curActualCost]","[tblCompSetup].[chrActualCost] ='" & tblCompName.chrCompName & "'" & tblOption.chrOption & "'") -- Glenn |
#2
|
|||
|
|||
Query on a Query
Your DLookup is definitely in error.
DLookup("curActualCost","tblCompSetUp","[Competition Name]= " & chr(34) & [Competition Name] & chr(34) & " AND [Playing Option]= " & CHr(34) & [PLaying Option] & Chr(34)) If Playing option is a number field then remove the Chr(34) that surround it. A better way would be to include the separate table into the query in the first place and join competition name to competition name and playing option to playing option. The SQL statement would look something like the following - QueryA is your current query. SELECT [QueryA].*, C.Cost FROM [QueryA] LEFT JOIN CompetitionCostsTable as C ON [QueryA].[Competition Name] = C.[Competition Name] AND [QueryA].[Playing Option] = C.[Competition Name] John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Brampton76 wrote: I have a query that has presented the following result: Name, Competition Name and Playing Option. I also have a separate table that contains the Competition Name, the various Playing Options for the Competition and the Cost for each of the options. This table is not linked to anything. I would like to find the Cost based on the Competition Name and Playing Option. I tried a DLookUp but got an error and wondered if I got the DLookUp wrong or whether I was barking completely up the wrong tree! Actual Cost: DLookUp("[curActualCost]","[tblCompSetup].[chrActualCost] ='" & tblCompName.chrCompName & "'" & tblOption.chrOption & "'") |
#3
|
|||
|
|||
Query on a Query
John,
Many thanks. I will try both options tomorrow. The Playing Options are text - so I will leave the Chr(34) in the lookup - but what does the Chr(34) represent? -- Glenn "John Spencer" wrote: Your DLookup is definitely in error. DLookup("curActualCost","tblCompSetUp","[Competition Name]= " & chr(34) & [Competition Name] & chr(34) & " AND [Playing Option]= " & CHr(34) & [PLaying Option] & Chr(34)) If Playing option is a number field then remove the Chr(34) that surround it. A better way would be to include the separate table into the query in the first place and join competition name to competition name and playing option to playing option. The SQL statement would look something like the following - QueryA is your current query. SELECT [QueryA].*, C.Cost FROM [QueryA] LEFT JOIN CompetitionCostsTable as C ON [QueryA].[Competition Name] = C.[Competition Name] AND [QueryA].[Playing Option] = C.[Competition Name] John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Brampton76 wrote: I have a query that has presented the following result: Name, Competition Name and Playing Option. I also have a separate table that contains the Competition Name, the various Playing Options for the Competition and the Cost for each of the options. This table is not linked to anything. I would like to find the Cost based on the Competition Name and Playing Option. I tried a DLookUp but got an error and wondered if I got the DLookUp wrong or whether I was barking completely up the wrong tree! Actual Cost: DLookUp("[curActualCost]","[tblCompSetup].[chrActualCost] ='" & tblCompName.chrCompName & "'" & tblOption.chrOption & "'") |
#4
|
|||
|
|||
Query on a Query
Chr(34) is a quote mark. You need to end up with quote marks around
text values. So if your fields value was Solo, the string needs to end up having a value that looks like [Playing Options] = "Solo" '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === Brampton76 wrote: John, Many thanks. I will try both options tomorrow. The Playing Options are text - so I will leave the Chr(34) in the lookup - but what does the Chr(34) represent? |
#5
|
|||
|
|||
Query on a Query
John, many thanks for all your help. Linking the two tables in that way
worked a treat and I can move on! The SQL statement now looks like this: SELECT Query1.chrName, Query1.chrCompName, Query1.chrOption, Query1.curCost, tblCompSetup.curActualCost FROM Query1 INNER JOIN tblCompSetup ON (Query1.chrOption = tblCompSetup.chrOption) AND (Query1.chrCompName = tblCompSetup.chrCompName); Again many thanks for all your time. -- Glenn "John Spencer" wrote: Chr(34) is a quote mark. You need to end up with quote marks around text values. So if your fields value was Solo, the string needs to end up having a value that looks like [Playing Options] = "Solo" '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === Brampton76 wrote: John, Many thanks. I will try both options tomorrow. The Playing Options are text - so I will leave the Chr(34) in the lookup - but what does the Chr(34) represent? |
Thread Tools | |
Display Modes | |
|
|