please help with a query

G

Guest

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)
 
G

Guest

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 said:
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 said:
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 said:
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)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.


:


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)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

--
KARL DEWEY
Build a little - Test a little


Alex said:
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 said:
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 said:
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



:

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.


:


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)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
You did not say whether what I posted worked or not - did it? If not, was
there error messages?
What is DTS?

--
KARL DEWEY
Build a little - Test a little


Alex said:
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 said:
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


:

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



:

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.


:


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)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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.
Click to expand...
What is DTS?

--
KARL DEWEY
Build a little - Test a little


Alex said:
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

:

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


:

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



:

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.


:


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)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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


:

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

:

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


:

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



:

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.


:


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)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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.
Click to expand...
What is DTS?

--
KARL DEWEY
Build a little - Test a little


Alex said:
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

:

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


:

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



:

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.


:


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)
Click to expand...
Click to expand...
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

Why is my union query asking me to enter a parameter value? 4
TESTING FOR EMPTY ARRAY 4
What woudl be the better design (migth be double post) 1
Database Loading problem 1
maxoflist 9
complex join issue - access 2003 2
Counting in three dimentions 2
What's wrong with my xpath? 3

Top