Forum Discussion

LEA63's avatar
LEA63
Copper Contributor
Jun 14, 2022

Query Displayes Blanks

Hi,

I am not a developer, but I have a great new job and have managed to successfully repair a Microsoft Access Database created by my predecessor. There is an existing issue with a query:

 

1. The query gets the Sales (start and end date from a form) and the Cost (start and end date from a form the same form)

 

2. When the start date is 05/30/2022 with zero (0.00) sales as the start date, the query returns blanks; it should add the zero sales for the start date to the remaining dates in the range between the start date and the end date.

 

3. Here is the sql query code:

---SELECT [Query Sales3].[Profit Center], [Query Sales3].SumOfSales, [Query Food Cost3].Cost AS [Food ---Cost], Sum([Query Other Cost3].OCost) AS [Other Cost]
---FROM [Query Sales3], [Query Food Cost3], [Query Other Cost3]
---GROUP BY [Query Sales3].[Profit Center], [Query Sales3].SumOfSales, [Query Food Cost3].Cost
---HAVING ((([Query Sales3].[Profit Center])=[Forms]![Form3]![Profit Center]));

 

Please help and thank you.

access
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Jun 15, 2022
    there are 3 queries involved (Cartesian/Cross joined) in your query.
    you also need to show the SQL for these 3.
    • Deleted's avatar
      Deleted
      Jun 16, 2022

      arnel_gp 

      Thank you for your help; here you go:

      1. Sales Query is successful:

      ---SELECT [Sales Broad].[Profit Center], Sum([Sales Broad].Sales) AS SumOfSales
      --FROM [Sales Broad]
      --WHERE ((([Sales Broad].Date) Between [Forms]![Form3]![StartDate] And [Forms]![Form3]![EndDate]))
      ---GROUP BY [Sales Broad].[Profit Center];

       

      2. Food Cost Query is successful:

      ---SELECT Sum([Invoice by Expensiture Type3].SumOfreceive_total) AS Cost
      ---FROM [Invoice by Expensiture Type3]
      ---WHERE ((([Invoice by Expensiture Type3].pg_credit)="COGS-No Perpetual Inv" Or ([Invoice by ---Expensiture Type3].pg_credit) Is Null));

       

      3. Other Cost Query is failing:

      ---SELECT [Invoice by Expensiture Type3].pg_credit, Sum([Invoice by Expensiture ---Type3].SumOfreceive_total) AS OCost
      ---FROM [Invoice by Expensiture Type3]
      ---GROUP BY [Invoice by Expensiture Type3].pg_credit
      ---HAVING ((([Invoice by Expensiture Type3].pg_credit)<>"COGS-No Perpetual Inv" And ([Invoice by ---Expensiture Type3].pg_credit) Is Not Null));

       

       

Share

Resources