please help with a query

Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

Try this ---
UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);
--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

Thanks, Karl.

It's empty anyway.

I have two tables:
TOC$Customer:
No_ is a primary key
C00289
C00291
....

[TOC$Default Dimension]:
[Table ID] [No_] [Dimension Code] [Dimension Value Code]
18 C000289 SALES TERRITORY 170
18 C00291 CORP GROUP 99999
....

[Table ID], [No_],[Dimension Code] fields are the primary key

I cannot create several queries for this. Everything should be in a one
query as it's used for DTS.

Thanks

"KARL DEWEY" wrote:

Try this ---
UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);
--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

You did not say whether what I posted worked or not - did it? If not, was
there error messages?

Everything should be in a one query as it's used for DTS.
What is DTS?

--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks, Karl.

It's empty anyway.

I have two tables:
TOC$Customer:
No_ is a primary key
C00289
C00291
...

[TOC$Default Dimension]:
[Table ID] [No_] [Dimension Code] [Dimension Value Code]
18 C000289 SALES TERRITORY 170
18 C00291 CORP GROUP 99999
...

[Table ID], [No_],[Dimension Code] fields are the primary key

I cannot create several queries for this. Everything should be in a one
query as it's used for DTS.

Thanks

"KARL DEWEY" wrote:

Try this ---
UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);
--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

The error message is as follows:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Dimension Value Code'.

as this field and 'Dimension Code' field are not a part of TOC$Customer table.
The TOC$Customer table has a list of all customers - unique.

The 'TOC$Default Dimension' table has some customers and they're not unique
as for the field 'Table ID'=18 there can be 'SALES TERRITORY' and 'CORP
GROUP' values.



"KARL DEWEY" wrote:

You did not say whether what I posted worked or not - did it? If not, was
there error messages?

Everything should be in a one query as it's used for DTS.
What is DTS?

--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks, Karl.

It's empty anyway.

I have two tables:
TOC$Customer:
No_ is a primary key
C00289
C00291
...

[TOC$Default Dimension]:
[Table ID] [No_] [Dimension Code] [Dimension Value Code]
18 C000289 SALES TERRITORY 170
18 C00291 CORP GROUP 99999
...

[Table ID], [No_],[Dimension Code] fields are the primary key

I cannot create several queries for this. Everything should be in a one
query as it's used for DTS.

Thanks

"KARL DEWEY" wrote:

Try this ---
UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);
--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

I am sorry, I did not clean up other stuff in your SQL.

UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer Left JOIN [TOC$Default Dimension] ON TOC$Customer.No_ =
[TOC$Default Dimension].No_
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);

--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

The error message is as follows:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Dimension Value Code'.

as this field and 'Dimension Code' field are not a part of TOC$Customer table.
The TOC$Customer table has a list of all customers - unique.

The 'TOC$Default Dimension' table has some customers and they're not unique
as for the field 'Table ID'=18 there can be 'SALES TERRITORY' and 'CORP
GROUP' values.



"KARL DEWEY" wrote:

You did not say whether what I posted worked or not - did it? If not, was
there error messages?

Everything should be in a one query as it's used for DTS.
What is DTS?

--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks, Karl.

It's empty anyway.

I have two tables:
TOC$Customer:
No_ is a primary key
C00289
C00291
...

[TOC$Default Dimension]:
[Table ID] [No_] [Dimension Code] [Dimension Value Code]
18 C000289 SALES TERRITORY 170
18 C00291 CORP GROUP 99999
...

[Table ID], [No_],[Dimension Code] fields are the primary key

I cannot create several queries for this. Everything should be in a one
query as it's used for DTS.

Thanks

"KARL DEWEY" wrote:

Try this ---
UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);
--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

Thanks, Karl but it's empty.
(no error message)

I'm trying this query:
SELECT [TOC$Customer].No_, [TOC$Default Dimension].[Dimension Code],
[TOC$Default Dimension].[Dimension Value Code]
FROM [TOC$Default Dimension] RIGHT JOIN [TOC$Customer] ON [TOC$Default
Dimension].No_ = [TOC$Customer].No_
GROUP BY [TOC$Customer].No_, [TOC$Default Dimension].[Dimension Code],
[TOC$Default Dimension].[Dimension Value Code]
HAVING ((([TOC$Default Dimension].[Dimension Code])="SALES TERRITORY" Or
([TOC$Default Dimension].[Dimension Code]) Is Null));

but it returns 621 records when TOC$Customer table consists of 691 records.

Thanks

"KARL DEWEY" wrote:

I am sorry, I did not clean up other stuff in your SQL.

UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer Left JOIN [TOC$Default Dimension] ON TOC$Customer.No_ =
[TOC$Default Dimension].No_
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);

--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

The error message is as follows:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Dimension Value Code'.

as this field and 'Dimension Code' field are not a part of TOC$Customer table.
The TOC$Customer table has a list of all customers - unique.

The 'TOC$Default Dimension' table has some customers and they're not unique
as for the field 'Table ID'=18 there can be 'SALES TERRITORY' and 'CORP
GROUP' values.



"KARL DEWEY" wrote:

You did not say whether what I posted worked or not - did it? If not, was
there error messages?

Everything should be in a one query as it's used for DTS.
What is DTS?

--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks, Karl.

It's empty anyway.

I have two tables:
TOC$Customer:
No_ is a primary key
C00289
C00291
...

[TOC$Default Dimension]:
[Table ID] [No_] [Dimension Code] [Dimension Value Code]
18 C000289 SALES TERRITORY 170
18 C00291 CORP GROUP 99999
...

[Table ID], [No_],[Dimension Code] fields are the primary key

I cannot create several queries for this. Everything should be in a one
query as it's used for DTS.

Thanks

"KARL DEWEY" wrote:

Try this ---
UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);
--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)

We were missing link in the exists subquery to the customer number. The
entire SELECT statement for the EMPTY/NA string should read as follows:

SELECT
TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE NOT EXISTS (
SELECT
No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
WHERE [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY'
AND [TOC$Default Dimension].[No_]=[TOC$Customer].No_
)

Now, it's working.

Thanks, all

"Alex" wrote:

The error message is as follows:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Dimension Value Code'.

as this field and 'Dimension Code' field are not a part of TOC$Customer table.
The TOC$Customer table has a list of all customers - unique.

The 'TOC$Default Dimension' table has some customers and they're not unique
as for the field 'Table ID'=18 there can be 'SALES TERRITORY' and 'CORP
GROUP' values.



"KARL DEWEY" wrote:

You did not say whether what I posted worked or not - did it? If not, was
there error messages?

Everything should be in a one query as it's used for DTS.
What is DTS?

--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks, Karl.

It's empty anyway.

I have two tables:
TOC$Customer:
No_ is a primary key
C00289
C00291
...

[TOC$Default Dimension]:
[Table ID] [No_] [Dimension Code] [Dimension Value Code]
18 C000289 SALES TERRITORY 170
18 C00291 CORP GROUP 99999
...

[Table ID], [No_],[Dimension Code] fields are the primary key

I cannot create several queries for this. Everything should be in a one
query as it's used for DTS.

Thanks

"KARL DEWEY" wrote:

Try this ---
UNION Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]18 AND [TOC$Default
Dimension].[Dimension Code]'SALES TERRITORY') AND ([Dimension Value Code]
= 0 OR [Dimension Value Code] IS NULL);
--
KARL DEWEY
Build a little - Test a little


"Alex" wrote:

Thanks Jerry,

I need all cutomers that are missing in the first part. It gives only 621
out of 691.

The query
Select TOC$Customer.No_ FROM TOC$Customer returns all 691 ones.

Any idea how i could get it?

Thanks



"Jerry Whittle" wrote:

Simplify and build from there. Does the following return the expected results?

Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY' ;

If so does the following work as expected:

Select TOC$Customer.No_,
'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer
WHERE not exists
(Select No_,
[Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18
AND [TOC$Default Dimension].[Dimension Code]='SALES TERRITORY') ;



--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Alex" wrote:


Select DISTINCT No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY'
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL
UNION
--** This part is not working giving empty result**
Select TOC$Customer.No_, 'N/A' AS [Dimension Code],
'N/A' AS [Dimension Value Code]
FROM TOC$Customer where not exists (Select No_, [Dimension Code],
[Dimension Value Code]
FROM [TOC$Default Dimension]
Where [TOC$Default Dimension].[Table ID]=18 AND [TOC$Default
Dimension].[Dimension Code]='SALES TERRITORY')
--AND [Dimension Value Code] 0 AND [Dimension Value Code] IS NOT NULL)