Combining queries

[jamo]

I need to have monthly, yearly and quarterly reports, the reports should also depict are also by quantity and revenue and amount paid My problem id the quantity is date imported and revenue is date paid. how do I create a query to dhow all the relevant data and date

[June7]

Create queries that calculate the various periods from the dates. Have no idea what your data structure is so here is rough example.

SELECT *, Month(Datefield) As TransMonth, Year(Datefield) AS TransYear, Switch([TransMonth] BETWEEN 1 AND 3,1, [TransMonth] BETWEEN 4 AND 6,2, [TransMonth] BETWEEN 7 AND 9,3, [TransMonth] BETWEEN 10 AND 12,4) As TransQtr
FROM tablename;

Use the calculated periods to perform grouping and to join queries.

[jamo]

I have made 2 queries a quantity and revenue,Should I create 1 query from those 2?
I am also sending a sample database with the information. This is the same database I used for my previous question.

[June7]

Can't get your file to download, just errors.

[jamo]

SELECT Importer.[Importer Name], Purchasing.vessel, Purchasing.[Arrival Date], Purchasing.[Fuel Type], Purchasing.quantity, Year([Arrival Date]) AS [Year], Month([Arrival Date]) AS [Month], DatePart("q",[Arrival Date]) AS Quarter, Format([Arrival Date],"mmmm") AS [Month Name], (Month([Arrival Date]) Mod 3) AS MonthOfQuarter
FROM Importer INNER JOIN Purchasing ON Importer.ImporterID = Purchasing.ImporterID;

SELECT [Payment Extended].PaymentID, Importer.[Importer Name], [Payment Extended].[Receipt NO], [Payment Extended].[Date Paid], [Payment Extended].Revenue, Year([Date Paid]) AS [Year], Month([Date Paid]) AS [Month], DatePart("q",[Date Paid]) AS Quarter, (Month([Date Paid]) Mod 3) AS MonthOfQuarter, Format([Date Paid],"mmmm") AS [Month Name]
FROM [Payment Extended] LEFT JOIN Importer ON [Payment Extended].[ImporterID] = Importer.[ImporterID];

These the 2 queries the first one is the quantity and the second is revenue the quantity is on arrival date and the revenue is on payment date.

Thanks

[June7]

Darn! I forgot that DatePart could calculate quarter of year. Glad you found it. Does the calc for MonthOfQuarter give the result you expect?

Do you want to show detail records or just summary info for each period?

If you want details I expect will need to do a report with subreports.

If you want just summary info and want the Quantity and Revenue data side-by-side on one line, make the queries GROUP BY (totals) queries then join. Example for monthly:

SELECT Year([Date Paid]) AS [Year], Month([Date Paid]) AS [Month], Format([Date Paid],"mmmm") AS [Month Name], Sum([Payment Extended].Revenue) AS SumOfRevenue
FROM [Payment Extended]
GROUP BY Year([Date Paid]), Month([Date Paid]), Format([Date Paid],"mmmm");

SELECT Year([Arrival Date]) AS [Year], Month([Arrival Date]) AS [Month], Sum(Purchasing.quantity) AS SumOfquantity
FROM Purchasing
GROUP BY Year([Arrival Date]), Month([Arrival Date]);

SELECT SumRevenue.Year, SumRevenue.Month, SumRevenue.[Month Name], SumRevenue.SumOfRevenue, SumQuantity.SumOfquantity
FROM SumRevenue INNER JOIN SumQuantity ON (SumRevenue.Month = SumQuantity.Month) AND (SumRevenue.Year = SumQuantity.Year);

[jamo]

Thanks so much. I get the results I expect for the quarter and I dont need details the summary for the report will work just fine.

[jamo]

Thanks the queries work fine. The reports on the other hand is giving an error. "there are no imports for the period" This is the code I am using I got this online and tried to customize it

Private Sub Report_Open(Cancel As Integer)
    On Error GoTo ErrorHandler
    Dim strSQL As String


    If IsNull(TempVars![Display]) Or IsNull(TempVars![Year]) Or IsNull(TempVars![Month]) Or IsNull(TempVars![Group By]) Then
        DoCmd.OpenForm "Sales Reports Dialog"
        Cancel = True
        Exit Sub
    End If
    
    strSQL = "SELECT [Year]"
    strSQL = strSQL & ", [Month]"
    strSQL = strSQL & ", First([" & TempVars![Display] & "]) AS SalesGroupingField"
    strSQL = strSQL & ", Sum([Revenue]) AS [Total Revenue]"
    strSQL = strSQL & ", Sum([quantity]) AS [Total quantity]"
    strSQL = strSQL & ", First([Summary].[Month Name]) AS [Month Name]"
    strSQL = strSQL & " FROM [Summary] "
    strSQL = strSQL & " Where [Month]=" & TempVars![Month] & " AND [Year]=" & TempVars![Year]
    strSQL = strSQL & " GROUP BY [Year], [Month], [" & TempVars![Group By] & "];"
    
    Me.RecordSource = strSQL
    Me.SalesGroupingField_Label.Caption = TempVars![Display]
    
Done:
    Exit Sub
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("Monthly Sales Report_Open", "strSQL = " & strSQL) Then
        Resume
    Else
        Cancel = True
    End If
End Sub

[June7]

What do you want to happen? Why are you trying to set the report RecordSource? Why would a form open? Where would that custom error message come from? I don't think can set label caption property for report. I use a textbox to display variable info to act as a label or title.

[jamo]

I want to be able to view or print my information by year, month or quarter by fuel type or by company. This is why I have created the form. The error comes from the form.

[June7]

The error comes from the form opened by report event?

I still don't understand what behavior you want.

You want to make one report serve as output for any period?

Want to provide latest version of db?

[jamo]

I have attached the updated sample. The sales Dialog box and the reports

Thanks