A query from two queries

[Guest]

Hello
I have the two following queries:

SELECT Items.ItemCode, Count(Items.ItemCode) AS [Count of Multiples],
Items.Cost
FROM Items
GROUP BY Items.ItemCode, Items.Cost
HAVING (((Items.ItemCode) In (SELECT [ItemCode] FROM [Items] As Tmp GROUP BY
[ItemCode] HAVING Count(*)>1 )))
ORDER BY Items.ItemCode;

And

SELECT Items.ItemCode, Items.LocationID
FROM Items
WHERE (((Items. LocationID)="Level3"));

I want to have a third query that gives the records (ItemCodes) from the
first query for only those ItemCodes I take by the second query.
Do you know how?

Thank you

GL

 

[John Spencer]

If I understand what you want, then why not just add the WHERE clause

SELECT Items.ItemCode, Count(Items.ItemCode) AS [Count of Multiples],
Items.Cost
FROM Items
WHERE Items. LocationID="Level3"
GROUP BY Items.ItemCode, Items.Cost
HAVING (((Items.ItemCode) In (SELECT [ItemCode] FROM [Items] As Tmp GROUP BY
[ItemCode] HAVING Count(*)>1 )))
ORDER BY Items.ItemCode;


You could join the two queries in a third query on the Items.ItemCode in the
two queries, but that seems the long way around.

 

[Guest]

I am sorry it doesn't work

First query gives the number of different Location some items Items are
found let's say duplicate Items
Second query find the Items of a location.
Third query should find the duplicates of that location and the number of
location each Item is found.

GL

If I understand what you want, then why not just add the WHERE clause

SELECT Items.ItemCode, Count(Items.ItemCode) AS [Count of Multiples],
Items.Cost
FROM Items
WHERE Items. LocationID="Level3"
GROUP BY Items.ItemCode, Items.Cost
HAVING (((Items.ItemCode) In (SELECT [ItemCode] FROM [Items] As Tmp GROUP BY
[ItemCode] HAVING Count(*)>1 )))
ORDER BY Items.ItemCode;


You could join the two queries in a third query on the Items.ItemCode in the
two queries, but that seems the long way around.

Hello
I have the two following queries:

SELECT Items.ItemCode, Count(Items.ItemCode) AS [Count of Multiples],
Items.Cost
FROM Items
GROUP BY Items.ItemCode, Items.Cost
HAVING (((Items.ItemCode) In (SELECT [ItemCode] FROM [Items] As Tmp GROUP
BY
[ItemCode] HAVING Count(*)>1 )))
ORDER BY Items.ItemCode;

And

SELECT Items.ItemCode, Items.LocationID
FROM Items
WHERE (((Items. LocationID)="Level3"));

I want to have a third query that gives the records (ItemCodes) from the
first query for only those ItemCodes I take by the second query.
Do you know how?

Thank you

GL

 

[John Spencer]

I am really having trouble understanding what results you want back.
Perhaps you could post a short list of your data and the results you expect
to get back.

Given
Code : Location : Cost
A X 10
A X 20
A Y 10
A Y 5
B X 3
B X 3
C Y 5

What result do you want assuming that you specified location Y?

Wild Guess follows on a possible solution.
SELECT Items.ItemCode
, Count(Items.ItemCode) AS CountofMultiples
, Items.Cost
FROM Items
WHERE Items.ItemCode IN
(SELECT ItemCode
FROM Items As Tmp
GROUP BY ItemCode
HAVING Count(*)>1 )
GROUP BY Items.ItemCode, Items.Cost

SELECT Items.ItemCode, Items.LocationID
FROM Items
WHERE Items. LocationID="Level3"

Do you want all items found at one location (Level3) that are duplicated
anywhere else including in Level3?

SELECT *
FROM QDuplicates as D INNER JOIN QLocations As L
On D.ItemCode = L.ItemCode

Or doing it all in one.

SELECT *
FROM (
SELECT Items.ItemCode
, Count(Items.ItemCode) AS CountofMultiples
, Items.Cost
FROM Items
WHERE Items.ItemCode IN
(SELECT ItemCode
FROM Items As Tmp
GROUP BY ItemCode
HAVING Count(*)>1 )
GROUP BY Items.ItemCode, Items.Cost) as D INNER JOIN
(SELECT Items.ItemCode, Items.LocationID
FROM Items
WHERE Items. LocationID="Level3") As L
On D.ItemCode = L.ItemCode

I am sorry it doesn't work

First query gives the number of different Location some items Items are
found let's say duplicate Items
Second query find the Items of a location.
Third query should find the duplicates of that location and the number of
location each Item is found.

GL

If I understand what you want, then why not just add the WHERE clause

SELECT Items.ItemCode, Count(Items.ItemCode) AS [Count of Multiples],
Items.Cost
FROM Items
WHERE Items. LocationID="Level3"
GROUP BY Items.ItemCode, Items.Cost
HAVING (((Items.ItemCode) In (SELECT [ItemCode] FROM [Items] As Tmp GROUP
BY
[ItemCode] HAVING Count(*)>1 )))
ORDER BY Items.ItemCode;


You could join the two queries in a third query on the Items.ItemCode in
the
two queries, but that seems the long way around.

Hello
I have the two following queries:

SELECT Items.ItemCode, Count(Items.ItemCode) AS [Count of Multiples],
Items.Cost
FROM Items
GROUP BY Items.ItemCode, Items.Cost
HAVING (((Items.ItemCode) In (SELECT [ItemCode] FROM [Items] As Tmp
GROUP
BY
[ItemCode] HAVING Count(*)>1 )))
ORDER BY Items.ItemCode;

And

SELECT Items.ItemCode, Items.LocationID
FROM Items
WHERE (((Items. LocationID)="Level3"));

I want to have a third query that gives the records (ItemCodes) from
the
first query for only those ItemCodes I take by the second query.
Do you know how?

Thank you

GL