Combining 2 Queries

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I have a Query that works, however I want to replace Query 1 with what
Query 1 actually is in this SQL statement. How would I do this?
This Query 1 in SQL

SELECT MenuDetails.TerminalID, MenuDetails.StartDay, MenuDetails.MenuID,
Days.DayID, Days.DayName
FROM Days LEFT JOIN MenuDetails ON Days.DayID = MenuDetails.StartDay
GROUP BY MenuDetails.TerminalID, MenuDetails.StartDay,
MenuDetails.MenuID, Days.DayID, Days.DayName
HAVING (((MenuDetails.TerminalID)=[Forms]![MenuCreatorPaste2]![TxtID])
AND ((MenuDetails.MenuID)=[Forms]![MenuCreatorPaste2]![TxtMenu]))
ORDER BY Days.DayID;

And this is the working SQL statement.

With Me.ListDay
..RowSource = "SELECT Days.DayID, Days.DayName " & _
"FROM Days LEFT JOIN Query1 ON Days.DayID = Query1.StartDay " & _
"WHERE (((Query1.StartDay) Is Null));"
..ColumnCount = 2
..ColumnWidths = "0 in;2 in"
..Requery
End With

Any help appreciated!
Thanks
DS
 
I would use a subquery to select all distinct days that would be selected
from MenuDetails and then get all days that are not in that subresult:

SELECT Days.DayID, Days.DayName FROM Days WHERE Days.DayID NOT IN ( SELECT
DISTINCT MenuDetails.StartDay FROM MenuDetails WHERE
(((MenuDetails.TerminalID)=[Forms]![MenuCreatorPaste2]![TxtID]) AND
((MenuDetails.MenuID)=[Forms]![MenuCreatorPaste2]![TxtMenu])) );

I hope this helps.

Niels Verkaart
 
Niels said:
I would use a subquery to select all distinct days that would be selected
from MenuDetails and then get all days that are not in that subresult:

SELECT Days.DayID, Days.DayName FROM Days WHERE Days.DayID NOT IN ( SELECT
DISTINCT MenuDetails.StartDay FROM MenuDetails WHERE
(((MenuDetails.TerminalID)=[Forms]![MenuCreatorPaste2]![TxtID]) AND
((MenuDetails.MenuID)=[Forms]![MenuCreatorPaste2]![TxtMenu])) );

I hope this helps.

Niels Verkaart


I have a Query that works, however I want to replace Query 1 with what
Query 1 actually is in this SQL statement. How would I do this?
This Query 1 in SQL

SELECT MenuDetails.TerminalID, MenuDetails.StartDay, MenuDetails.MenuID,
Days.DayID, Days.DayName
FROM Days LEFT JOIN MenuDetails ON Days.DayID = MenuDetails.StartDay
GROUP BY MenuDetails.TerminalID, MenuDetails.StartDay, MenuDetails.MenuID,
Days.DayID, Days.DayName
HAVING (((MenuDetails.TerminalID)=[Forms]![MenuCreatorPaste2]![TxtID]) AND
((MenuDetails.MenuID)=[Forms]![MenuCreatorPaste2]![TxtMenu]))
ORDER BY Days.DayID;

And this is the working SQL statement.

With Me.ListDay
.RowSource = "SELECT Days.DayID, Days.DayName " & _
"FROM Days LEFT JOIN Query1 ON Days.DayID = Query1.StartDay " & _
"WHERE (((Query1.StartDay) Is Null));"
.ColumnCount = 2
.ColumnWidths = "0 in;2 in"
.Requery
End With

Any help appreciated!
Thanks
DS
Click to expand...
Click to expand...
Niel Thank You! Your solution worked great! Sorry for the slow
response but I was called away on and I didn't travel with my computer.
Thank you once again.
DS
 
Back
Top