Query on a Query

[Brampton76]

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 & "'")

 

[John Spencer]

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]

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

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

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 & "'")

 

[John Spencer]

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]

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

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
'====================================================

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?