access queries

G

Guest

I have two access queries, there is one criteria "GB or ML" in both of these
queries.
In the first query, it still gives me back the GB and in the second query it
doesn't give me back the GB. The only difference is that there is one more
table involved in the query. Does this have anything to do with it? Or is
it the joins?

See the below queries -

TRANSFORM Sum(Alldocs.Amt_LclCurrency2) AS SumOfAmt_LclCurrency2
SELECT Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation,
Alldocs.Relation FROM (((Alldocs INNER JOIN Accounts ON Alldocs.Account =
Accounts.Account) INNER JOIN Object_CCode ON Alldocs.Object_Key =
Object_CCode.Object_Key) INNER JOIN Company_Code_Region ON
Object_CCode.Company_Code = Company_Code_Region.Company_Code) LEFT JOIN
[Relation Code] ON Alldocs.Relation = [Relation Code].Code WHERE
(((Alldocs.Doc_Type) Not Like "GB*" Or (Alldocs.Doc_Type) Not Like "ML*") AND
((Company_Code_Region.Company_Code)="0031"
Or(Company_Code_Region.Company_Code)="0040") AND ((Accounts.Item) Not Like
"detention") AND ((Alldocs.Posting_Date) Between #1/1/2007# And
#12/31/2007#)) GROUP BY Accounts.Item, Alldocs.Posting_Key, [Relation
Code].Relation, Alldocs.Relation, Company_Code_Region.Company_Code
PIVOT Alldocs.Posting_Period;

TRANSFORM Sum(Alldocs.Amt_LclCurrency2) AS SumOfAmt_LclCurrency2
SELECT Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation, [New
Office-Location-Area].AREA FROM ((((Alldocs INNER JOIN Accounts ON
Alldocs.Account = Accounts.Account) INNER JOIN Object_CCode ON
Alldocs.Object_Key = Object_CCode.Object_Key) INNER JOIN Company_Code_Region
ON Object_CCode.Company_Code = Company_Code_Region.Company_Code) INNER JOIN
[New Office-Location-Area] ON Object_CCode.Office = [New
Office-Location-Area].OFFICE) LEFT JOIN [Relation Code] ON Alldocs.Relation =
[Relation Code].Code
WHERE ((([New Office-Location-Area].AREA)<>"USFLAG") AND ((Accounts.Item)
Not Like "detention") AND ((Alldocs.Posting_Date) Between #1/1/2007# And
#12/31/2007#) AND ((Alldocs.Doc_Type)<>"GB" Or (Alldocs.Doc_Type)<>"ML"))
GROUP BY Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation, [New
Office-Location-Area].AREA, Company_Code_Region.Company_Code ORDER BY [New
Office-Location-Area].AREA PIVOT Alldocs.Posting_Period;
 
P

PatdeLux

I have two access queries, there is one criteria "GB or ML" in both of these
queries.
In the first query, it still gives me back the GB and in the second query it
doesn't give me back the GB. The only difference is that there is one more
table involved in the query. Does this have anything to do with it? Or is
it the joins?

See the below queries -

TRANSFORM Sum(Alldocs.Amt_LclCurrency2) AS SumOfAmt_LclCurrency2
SELECT Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation,
Alldocs.Relation FROM (((Alldocs INNER JOIN Accounts ON Alldocs.Account =
Accounts.Account) INNER JOIN Object_CCode ON Alldocs.Object_Key =
Object_CCode.Object_Key) INNER JOIN Company_Code_Region ON
Object_CCode.Company_Code = Company_Code_Region.Company_Code) LEFT JOIN
[Relation Code] ON Alldocs.Relation = [Relation Code].Code WHERE
(((Alldocs.Doc_Type) Not Like "GB*" Or (Alldocs.Doc_Type) Not Like "ML*") AND
((Company_Code_Region.Company_Code)="0031"
Or(Company_Code_Region.Company_Code)="0040") AND ((Accounts.Item) Not Like
"detention") AND ((Alldocs.Posting_Date) Between #1/1/2007# And
#12/31/2007#)) GROUP BY Accounts.Item, Alldocs.Posting_Key, [Relation
Code].Relation, Alldocs.Relation, Company_Code_Region.Company_Code
PIVOT Alldocs.Posting_Period;

TRANSFORM Sum(Alldocs.Amt_LclCurrency2) AS SumOfAmt_LclCurrency2
SELECT Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation, [New
Office-Location-Area].AREA FROM ((((Alldocs INNER JOIN Accounts ON
Alldocs.Account = Accounts.Account) INNER JOIN Object_CCode ON
Alldocs.Object_Key = Object_CCode.Object_Key) INNER JOIN Company_Code_Region
ON Object_CCode.Company_Code = Company_Code_Region.Company_Code) INNER JOIN
[New Office-Location-Area] ON Object_CCode.Office = [New
Office-Location-Area].OFFICE) LEFT JOIN [Relation Code] ON Alldocs.Relation =
[Relation Code].Code
WHERE ((([New Office-Location-Area].AREA)<>"USFLAG") AND ((Accounts.Item)
Not Like "detention") AND ((Alldocs.Posting_Date) Between #1/1/2007# And
#12/31/2007#) AND ((Alldocs.Doc_Type)<>"GB" Or (Alldocs.Doc_Type)<>"ML"))
GROUP BY Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation, [New
Office-Location-Area].AREA, Company_Code_Region.Company_Code ORDER BY [New
Office-Location-Area].AREA PIVOT Alldocs.Posting_Period;
Click to expand...

In one query you use
not like "GB*"
in the other one, you use
<> "GB"
Maybe you should first check which is the exact expression you're
looking for ?

Patrick
www.idevlop.com
 
G

Guest

In the first one I also used <> GB and it still gave me GB.
In the second one I have also used not like GB* and it did not give me GB.
--
Tina


PatdeLux said:
I have two access queries, there is one criteria "GB or ML" in both of these
queries.
In the first query, it still gives me back the GB and in the second query it
doesn't give me back the GB. The only difference is that there is one more
table involved in the query. Does this have anything to do with it? Or is
it the joins?

See the below queries -

TRANSFORM Sum(Alldocs.Amt_LclCurrency2) AS SumOfAmt_LclCurrency2
SELECT Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation,
Alldocs.Relation FROM (((Alldocs INNER JOIN Accounts ON Alldocs.Account =
Accounts.Account) INNER JOIN Object_CCode ON Alldocs.Object_Key =
Object_CCode.Object_Key) INNER JOIN Company_Code_Region ON
Object_CCode.Company_Code = Company_Code_Region.Company_Code) LEFT JOIN
[Relation Code] ON Alldocs.Relation = [Relation Code].Code WHERE
(((Alldocs.Doc_Type) Not Like "GB*" Or (Alldocs.Doc_Type) Not Like "ML*") AND
((Company_Code_Region.Company_Code)="0031"
Or(Company_Code_Region.Company_Code)="0040") AND ((Accounts.Item) Not Like
"detention") AND ((Alldocs.Posting_Date) Between #1/1/2007# And
#12/31/2007#)) GROUP BY Accounts.Item, Alldocs.Posting_Key, [Relation
Code].Relation, Alldocs.Relation, Company_Code_Region.Company_Code
PIVOT Alldocs.Posting_Period;

TRANSFORM Sum(Alldocs.Amt_LclCurrency2) AS SumOfAmt_LclCurrency2
SELECT Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation, [New
Office-Location-Area].AREA FROM ((((Alldocs INNER JOIN Accounts ON
Alldocs.Account = Accounts.Account) INNER JOIN Object_CCode ON
Alldocs.Object_Key = Object_CCode.Object_Key) INNER JOIN Company_Code_Region
ON Object_CCode.Company_Code = Company_Code_Region.Company_Code) INNER JOIN
[New Office-Location-Area] ON Object_CCode.Office = [New
Office-Location-Area].OFFICE) LEFT JOIN [Relation Code] ON Alldocs.Relation =
[Relation Code].Code
WHERE ((([New Office-Location-Area].AREA)<>"USFLAG") AND ((Accounts.Item)
Not Like "detention") AND ((Alldocs.Posting_Date) Between #1/1/2007# And
#12/31/2007#) AND ((Alldocs.Doc_Type)<>"GB" Or (Alldocs.Doc_Type)<>"ML"))
GROUP BY Accounts.Item, Alldocs.Posting_Key, [Relation Code].Relation, [New
Office-Location-Area].AREA, Company_Code_Region.Company_Code ORDER BY [New
Office-Location-Area].AREA PIVOT Alldocs.Posting_Period;
Click to expand...

In one query you use
not like "GB*"
in the other one, you use
<> "GB"
Maybe you should first check which is the exact expression you're
looking for ?

Patrick
www.idevlop.com
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FWT Newsletter - Weekly - September 6, 2004 1

Top