Access db and crosstab in crystal reports (1 Viewer)

I have an access db that has three tables, in two of the tables all my cities are listed that I need to reflect in my crosstab in crystal. However the remaining table only has one of the four cities listed in it.

Everything works correctly except when I want a grand total for all cities. It excludes the total for the table that only has one city and prints the rest.

If I go to the table where only one city is listed and add dummy records for the other three cities in a date range, all pulls correctly.

There must be a work around. Any ideas

I have added this to my query:
Select Location: Nz([RigDailySales.Location];0),
Sum(RigDailySales.Rig1DailyTotal) AS [Sum Of Rig1DailyTotal], Sum(RigDailySales.Rig2DailyTotal) AS [Sum Of Rig2DailyTotal], Sum(RigDailySales.Rig3DailyTotal) AS [Sum Of Rig3DailyTotal], Sum(RigDailySales.Rig4DailyTotal) AS [Sum Of Rig4DailyTotal], Sum(RigDailySales.Rig5DailyTotal) AS [Sum Of Rig5DailyTotal], Sum(RigDailySales.CRig1DailyTotal) AS [Sum Of CRig1DailyTotal], Sum(RigDailySales.Rig1DailyTotal)+Sum(RigDailySales.Rig2DailyTotal)+Sum(RigDailySales.Rig3DailyTotal)+Sum(RigDailySales.Rig4DailyTotal)+Sum(RigDailySales.Rig5DailyTotal)+Sum(RigDailySales.CRig1DailyTotal) AS TotalRigs, ReportStartDate AS Expr2, ReportEndDate AS Expr3, Day(ReportEndDate) AS Multiplier, Day(DateAdd('d',-Day(DateAdd('m',1,ReportEndDate)),DateAdd('m',1,ReportEndDate))) AS NumberOfDays
FROM RigDailySales
WHERE (((RigDailySales.ReportDate) Between [ReportStartDate] And [ReportEndDate]))
GROUP BY RigDailySales.Location;

and get an syntac error, missing operator.

I have tried this as well
Select Location: Nz([RigDailySales.Location]," "), and get the same error message

Any help would be appreciated

solution found

solution found thanks