Merging Queries (1 Viewer)

[Schillers]

Hey, I need to merge the two queries below into one to make it run on one listbox:

SELECT TblCaravan.[Caravan No]
FROM TblCaravan
WHERE (((TblCaravan.[No Of Berth])=Forms!Booking![Berth Combo]) And ((TblCaravan.[Caravan Type])=Forms!Booking!Type));


SELECT TblCaravan.[Caravan No]
FROM TblCaravan LEFT JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((nz([When Required]>[Forms]![Booking].[Required_Until],True) Or nz([Required Until]<[Forms]![Booking].[When_Required],True))=True));




Is this possible?

 

[MSAccessRookie]

Hey, I need to merge the two queries below into one to make it run on one listbox:

SELECT TblCaravan.[Caravan No]
FROM TblCaravan
WHERE (((TblCaravan.[No Of Berth])=Forms!Booking![Berth Combo]) And ((TblCaravan.[Caravan Type])=Forms!Booking!Type));


SELECT TblCaravan.[Caravan No]
FROM TblCaravan LEFT JOIN TblCaravanBooking ON TblCaravan.[Caravan No] = TblCaravanBooking.[Caravan No]
WHERE (((nz([When Required]>[Forms]![Booking].[Required_Until],True) Or nz([Required Until]<[Forms]![Booking].[When_Required],True))=True));




Is this possible?

It appears that a UNION Query could be helpful here. Look that up and get back with any questions.

 

[Schillers]

I combined them through an 'And' in the end. Seems to work?

 

[MSAccessRookie]

I combined them through an 'And' in the end. Seems to work?

An AND statement will provide the intersection of the two datasets, and (depending on the type of JOIN), may or may not provide any records that are in one set and not in the other. You might want to also look at the results of an OR statement in this type of Query.

A UNION statement will provide all records from both sets, and eliminate any duplicates (unless the duplicates are requested via UNION ALL).

 

[Schillers]

But the data needs to fit both criteria, if I use the union query it shows all of them?

 

[MSAccessRookie]

But the data needs to fit both criteria, if I use the union query it shows all of them?

You appear to require both, so you should use the AND because it will give you only the subset that applies to both. An OR or UNION will give you a subset that applies to either one (as well as both).

 

[Schillers]

Thanks! I did that and now its all good