It's because "[All Calculations.Dealer]" should have been written "[All
Calculations].[Dealer]". "[All Calculations]" is the name of a Table or
Query, and "[Dealer]" is the name of a field. Actually, you probably
don't even need the brackets around "Dealer", but they don't hurt.
Same comment applies to "[STAT04503 Query.Dealer]"; it should be
"[STAT04503 Query].[Dealer]" .
Incidentally, the second SELECT in your Union Query doesn't need to
specify any aliases, such as "AMT", since only the names in the first
SELECT are used.
I'm kind of puzzled as to what you're trying to do with that WHERE
clause. In this Union Query, the dataset you're trying to filter with
the WHERE includes records from each of the datasets in the SELECT
clauses. It doesn't make sense to refer to "[All
Calculations].[Dealer]" -- it's just the "[Dealer]" field of your Union
Query, and any given record could have come from either dataset. Are
you trying to include all records for which there exists a record with a
matching [Dealer] field in the other Table? If so, you could define a
Query linking the two Tables, something like what I do in the following
example.
Suppose you have two Tables containing the following information:
[All Calculations] Table Datasheet View:
All Calculations_ID Dealer Calculation
------------------- ------ -----------
-704774862 DEF ($18.00)
-254264939 GHI $29.00
779338456 ABC $150.00
1916375140 ABC $23.00
[STAT04503 Query] Table Datasheet View:
STAT04503 Query_ID Dealer Amount
------------------ ------ --------
-1583321653 PQR ($18.50)
1309047503 ABC $17.95
2051196102 DEF $20.00
I'm omitting many of your fields to keep the example simple, but it
should be easy for you to imitate what I've done here, and to include
all of your fields.
Now I define a Query that finds all of the records in [All Calculations]
with a [Dealer] field that matches some [Dealer] field in [STAT04503
Query]. In your version of this, you'd have about 8 fields here instead
of 4.
[Q_010FirstTable] SQL:
SELECT DISTINCT AC.Dealer,
AC.[All Calculations_ID] AS [Promo Code],
AC.Calculation AS Amt, "Calc" AS Working
FROM [All Calculations] AS AC
INNER JOIN [STAT04503 Query] AS SQ
ON AC.Dealer = SQ.Dealer
ORDER BY AC.Dealer, AC.Calculation;
[Q_010FirstTable] Query Datasheet View:
Dealer Promo Code Amt Working
------ ---------- -------- -------
ABC 1916375140 $23.00 Calc
ABC 779338456 $150.00 Calc
DEF -704774862 ($18.00) Calc
The next Query is similar, but it lists matching records from [STAT04503
Query]. Notice that I don't bother defining aliases for some of its
fields, since the aliases would be ignored anyway.
[Q_020SecondTable] SQL:
SELECT DISTINCT SQ.Dealer,
SQ.[STAT04503 Query_ID],
SQ.Amount, "STAT04503" AS Working
FROM [All Calculations] AS AC
INNER JOIN [STAT04503 Query] AS SQ
ON AC.Dealer = SQ.Dealer
ORDER BY SQ.Dealer, SQ.Amount;
[Q_020SecondTable] Query Datasheet View:
Dealer STAT04503 Query_ID Amount Working
------ ------------------ ------ ---------
ABC 1309047503 $17.95 STAT04503
DEF 2051196102 $20.00 STAT04503
Having found the fields from the matching records, we can now combine
them using a simplified Union Query. I like to base a Union Query on
named Queries that I can edit in Query Design View, keeping the Union
Query itself short and simple.
[Q_030Union] SQL:
SELECT * FROM Q_010FirstTable
UNION SELECT * FROM Q_020SecondTable
ORDER BY Dealer, Amt;
[Q_030Union] Query Datasheet View:
Dealer Promo Code Amt Working
------ ---------- -------- ---------
ABC 1309047503 $17.95 STAT04503
ABC 1916375140 $23.00 Calc
ABC 779338456 $150.00 Calc
DEF -704774862 ($18.00) Calc
DEF 2051196102 $20.00 STAT04503
HTH.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Hi all,
please see my query below. I am fine until I enter the where Criteria
then I get the following message:
Invalid Bracketing of Name '[All Calculations.Dealer]'
SQL Is:
SELECT [Dealer], [PL], [Promo Code], [Brand Description], [Product
Line], [Calculation] as "AMT", [Type], "Calc" as [Working]
UNION SELECT [Dealer], [PL], [Promo Code], [Brand] as [Brand
Description], [Product Line], [Amount] as "AMT", [Type], "STAT04503" as
[Working]
WHERE (([All Calculations.Dealer])=([STAT04503 Query.Dealer]));
Any advice would be really helpfull, I am looking through the access
help but its limited.
Thanks!
Catherine
