Show results to all records even when some empty.

[mseeker22]

I have created four seperate queries which show the number of covers in a restaurant booking database. Lunchtime Bookings, Lunchtime Walkins (None bookings), Evening Bookings and Evening Walkins.

This will be shown on a form so the user can enter the date as the main criteria and be shown the covers summary for that day.

The individual queries ('Q_BookingsEvening' as an example) asks for 'Date' and sums the number of covers which match the serving period (Lunchtime-'2' or Evening-'3').

Then in 'Q_CoversSummary' it brings them all together. The query does work providing there is data in all four fields for that particular day. If any one of the fields has no data in it then they all return no records. This is what I would like solving.

I've done some searching but can't seem to find quite the right answer so help would be appreciated as always.



Thanks

Luke

[rpeare]

I think there's an easier way to do this.

In your query Q_BookingEvenings you are only summing one type

If you have a field that is something like:

EveningCount: iif((service period) = 3, 1, 0) in the totals row put sum
DayCount: iif((service period) = 1, 1, 0) sum the column (assuming your daytime count is item 1 on your restaurant booking times table)
copy this same type of formula for each time period you want to show.

Then you don't have to have that secondary query that is basically pulling one line from each query and has no linkage to one another.

[mseeker22]

Thanks for your reply . . . I think thats almost it but that shows the number of tables booked (Sum of BookingID so to speak) as opposed to the number of actual 'Diners'. So there may be 6 tables booked but '24' diners in total if that makes sense.

[mseeker22]

Ok, I've played around with this a bit by putting [Number of covers] as the 'True' part of the IIF. The query is returning multiple records though . . one for each Day booking and one for each Evening booking which looks like the attached table. Using the totals option on the query datasheet view (Circled) these are the numbers I need to use in a form.

[rpeare]

in your date field your totals row should read WHERE not GROUP BY