income expenses query not showing all records

[Nicolette]

Hi!
I have run into a snag that I have not been able to figure out. I have a database for my ebay auctions I have a table for the listings which includes date and fee etc. I want a query that pulls from the table of listings but also from my sales tables so I can create a PivotChart that shows income and expenses but not all of my listings have sales so I need the query to pull both types of information but it is only pulling ones with sales. I tried making two queries then combining them but it still only gives the listings that have corresponding sales.

Thanks in advance for any and all help!
Nicolette

 

[Alansidman]

Post your SQL statement so we can analyze what you have.

Alan

 

[SOS]

Good to do as Alan says, but my bet is that you need an outer join from your listings to your sales table.

 

[Nicolette]

This is a set of queries to perform calculations. I realize I probably did it the long way around but other than showing all records all calucations are coming out right and the set does exactly what I want it to do. I'm thinking that I'm just not quering the table of listings but i'm not sure how to incorporate it without messing something up

First query qryCountOfSKU:

SELECT DISTINCT Count(tblListings.SKU) AS NumberOfTimesListed, tblListings.SKU
FROM tblListings
GROUP BY tblListings.SKU;

Second query qrySumOfListingFees:

SELECT qryCountOfSKU.NumberOfTimesListed, qryCountOfSKU.SKU, Sum(tblListings.ListingFee) AS SumOfListingFee
FROM qryCountOfSKU, tblListings
GROUP BY qryCountOfSKU.NumberOfTimesListed, qryCountOfSKU.SKU, tblListings.SKU
HAVING ((([tblListings]![SKU]) Like [qryCountOfSKU]![SKU]));

Third query qryCalculations2:

SELECT DISTINCT qrySumOfListingFees.NumberOfTimesListed, qrySumOfListingFees.SKU, qrySumOfListingFees.SumOfListingFee
FROM qrySumOfListingFees;

Fourth query qryCalculations3:

SELECT tblOrderDetails.SKU, tblOrderHeader.OrderID, tblOrderHeader.CustomerID, tblOrderHeader.SumOfMiscFees, tblOrderDetails.OrderPrice, tblOrderDetails.OrderQty, tblOrderHeader.StateTax, tblOrderHeader.ShipRate, Sum(([OrderPrice]*[OrderQty])+[ShipRate]+[StateTax]) AS TotalProductIncome
FROM tblOrderHeader INNER JOIN tblOrderDetails ON tblOrderHeader.OrderID = tblOrderDetails.OrderID
WHERE ((([tblOrderHeader]![OrderID]) Like [tblOrderDetails]![OrderID]))
GROUP BY tblOrderDetails.SKU, tblOrderHeader.OrderID, tblOrderHeader.CustomerID, tblOrderHeader.SumOfMiscFees, tblOrderDetails.OrderPrice, tblOrderDetails.OrderQty, tblOrderHeader.StateTax, tblOrderHeader.ShipRate;

Fifth query qryCalculations4:

SELECT qryCalculations3.SKU, qryCalculations3.SumOfMiscFees, qryCalculations2.NumberOfTimesListed, qryCalculations2.SumOfListingFee
FROM qryCalculations2 INNER JOIN qryCalculations3 ON qryCalculations2.SKU = qryCalculations3.SKU;

Sixth query qryCalculations5:

SELECT qryCalculations3.OrderID, qryCalculations3.SKU, qryCalculations4.NumberOfTimesListed, qryCalculations3.SumOfMiscFees, qryCalculations4.SumOfListingFee, qryCalculations3.TotalProductIncome, [qryCalculations3]![SumOfMiscFees]+[qryCalculations4]![SumOfListingFee] AS TotalProductExpenses
FROM qryCalculations3 INNER JOIN qryCalculations4 ON qryCalculations3.SKU = qryCalculations4.SKU;

Seventh query qryFinalProductIncomeExpenseCalculation2:

SELECT qryCalculations5.OrderID, qryCalculations5.SKU, qryCalculations5.NumberOfTimesListed, qryCalculations5.TotalProductIncome, qryCalculations5.TotalProductExpenses, [qryCalculations5]![TotalProductIncome]+[qryCalculations5]![TotalProductExpenses] AS ProductProfitLoss, tblInventory.ProductTitle, tblInventory.Picture, tblListings.ListingDte, tblOrderHeader.OrderDte
FROM ((qryCalculations5 INNER JOIN tblInventory ON qryCalculations5.SKU = tblInventory.SKU) INNER JOIN tblOrderHeader ON qryCalculations5.OrderID = tblOrderHeader.OrderID) INNER JOIN tblListings ON (qryCalculations5.SKU = tblListings.SKU) AND (tblInventory.SKU = tblListings.SKU)
WHERE (((DatePart("m",[OrderDte])) Like Month([ListingDte])) AND ((DatePart("yyyy",[OrderDte])) Like Year([ListingDte])) AND (([Enter A Month]) Like Month([ListingDte])) AND (([Enter A Year]) Like Year([ListingDte])))
GROUP BY qryCalculations5.OrderID, qryCalculations5.SKU, qryCalculations5.NumberOfTimesListed, qryCalculations5.TotalProductIncome, qryCalculations5.TotalProductExpenses, [qryCalculations5]![TotalProductIncome]+[qryCalculations5]![TotalProductExpenses], tblInventory.ProductTitle, tblListings.ListingDte, tblOrderHeader.OrderDte;

 

[Nicolette]

Ok I looked into the outer join option andwith a bit of editing it worked! my query in now showing all records but I have run into another issue the editing I had to do was removing the DatePart function from the query. Because I had to remove this I now have records showing that I don't want to show...

SELECT qryCalculations5.OrderID, qryCalculations5.SKU, qryCalculations5.NumberOfTimesListed, qryCalculations5.TotalProductIncome, qryCalculations5.TotalProductExpenses, [qryCalculations5]![TotalProductIncome]+[qryCalculations5]![TotalProductExpenses] AS ProductProfitLoss, tblInventory.ProductTitle, tblInventory.Picture, tblListings.ListingDte, tblOrderHeader.OrderDte
FROM ((qryCalculations5 INNER JOIN tblInventory ON qryCalculations5.SKU = tblInventory.SKU) INNER JOIN tblOrderHeader ON qryCalculations5.OrderID = tblOrderHeader.OrderID) INNER JOIN tblListings ON (qryCalculations5.SKU = tblListings.SKU) AND (tblInventory.SKU = tblListings.SKU)
WHERE (((DatePart("m",[OrderDte])) Like Month([ListingDte])) AND ((DatePart("yyyy",[OrderDte])) Like Year([ListingDte])) AND (([Enter A Month]) Like Month([ListingDte])) AND (([Enter A Year]) Like Year([ListingDte])))
GROUP BY qryCalculations5.OrderID, qryCalculations5.SKU, qryCalculations5.NumberOfTimesListed, qryCalculations5.TotalProductIncome, qryCalculations5.TotalProductExpenses, [qryCalculations5]![TotalProductIncome]+[qryCalculations5]![TotalProductExpenses], tblInventory.ProductTitle, tblListings.ListingDte, tblOrderHeader.OrderDte;

I need the query to show sales records where the OrderDte Month & Year are the same as the ListingDte Month & Year.

Any suggestions?

 

[Nicolette]

I have messed with using some other date functions and none have worked does anyone have any suggestions?

 

[SOS]

Changed your Where clause to this:

SELECT qryCalculations5.OrderID, qryCalculations5.SKU, qryCalculations5.NumberOfTimesListed, qryCalculations5.TotalProductIncome, qryCalculations5.TotalProductExpenses, [qryCalculations5]![TotalProductIncome]+[qryCalculations5]![TotalProductExpenses] AS ProductProfitLoss, tblInventory.ProductTitle, tblInventory.Picture, tblListings.ListingDte, tblOrderHeader.OrderDte
FROM ((qryCalculations5 INNER JOIN tblInventory ON qryCalculations5.SKU = tblInventory.SKU) INNER JOIN tblOrderHeader ON qryCalculations5.OrderID = tblOrderHeader.OrderID) INNER JOIN tblListings ON (qryCalculations5.SKU = tblListings.SKU) AND (tblInventory.SKU = tblListings.SKU)
[COLOR=red][B]WHERE DateSerial(Year([OrderDte]),Month([OrderDte]),1)[OrderDte] = DateSerial(Year([ListingDte]),Month([ListingDte]),1)
[/B][/COLOR]GROUP BY qryCalculations5.OrderID, qryCalculations5.SKU, qryCalculations5.NumberOfTimesListed, qryCalculations5.TotalProductIncome, qryCalculations5.TotalProductExpenses, [qryCalculations5]![TotalProductIncome]+[qryCalculations5]![TotalProductExpenses], tblInventory.ProductTitle, tblListings.ListingDte, tblOrderHeader.OrderDte;

 

[Nicolette]

Ok so I changed my Where clause to your suggestion and I kept getting a missing operator error. So I added [ListingDte]= to the beginning of the statement after WHERE and i no longer get the missing operator message but it also returns no records. also I need to be able to enter the month and year by parameters. I'm gonna keep playing but if you have any further suggestions i'm all ears. Thanks!

 

[Nicolette]

oh i had an idea let me see if this works!

 

[Nicolette]

ok this might be progress ;-) I changed the where clause as follows

WHERE DateSerial(Year([Enter Start Date mm/dd/yyyy/]), Month([Enter End Date mm/dd/yyyy])-1,1) And DateSerial(Year([Enter Start Date mm/dd/yyyy/]), Month([Enter End Date mm/dd/yyyy]),0)

The problem i'm having with this is it isn't showing records only between my 2 dates.

 

[PNGBill]

A side idea is to select all records and use the Form Filter (vba) to do the filtering from Unbound Text Box controls on your form.
The code will run on the AfterUpdate event of the control.
This makes your query easier and you can type any date in the form and have the records and calculated controls on the form to reflect this criteria.

 

[Nicolette]

this isn't for a form it is for a report

 

[PNGBill]

Yes but how do you call the report ?
Normally by a button on a form and if that form also has a date text box control or any other such control, then the button that calls the report can see the value and implement the filter accordingly.

This is just as you can print an Invoice for what ever Sale you have as focus on the form where the button is.

The Query the Report uses should have All Invoices but the button uses Link Criteria and only shows the One Sale in the Report.

 

[PNGBill]

Here is code for a command button on a form.

The Button name is CmdRptLoansIssuedReport
The Button Caption is not relevant but just says "print report"

On the form is an Unbound text Box Control named ReportDate

Private Sub CmdRptLoansIssuedReport_Click()
On Error GoTo Err_CmdRptLoansIssuedReport_Click
    Dim Response As Integer
      
    'Filter report to display only Date currently showing on FrmLoansIssuedReport
    ' (by ReportDate)
    DoCmd.OpenReport "RptLoansIssuedReport", acViewPreview, , "TRNACTDTE = #" & Format(Me.ReportDate, "mm/dd/yyyy") & "#"
    
Exit_CmdRptLoansIssuedReport_Click:
    Exit Sub

Err_CmdRptLoansIssuedReport_Click:
    MsgBox Err.Description
    Resume Exit_CmdRptLoansIssuedReport_Click
    
End Sub

Because we don't use US date then we have the Format date issue.

The Report, RptLoanIssuedReport will open with just one days data.

I am sure it could be written to include dates between two dates. You would have two Unbound Text Box Controls on your Form.

And this code is in the On Open event of the Report and just does the OrderBy task as Filter was done from the Form.

Private Sub Form_Open(Cancel As Integer)
    
    Me.OrderBy = "TRNACTDTE DESC"
    Me.OrderByOn = True
    
End Sub

 

[Nicolette]

i just had a pop up form with command buttons to open each report that is all that is on that specific from is command buttons one for each form

 

[PNGBill]

i just had a pop up form with command buttons to open each report that is all that is on that specific from is command buttons one for each form

Somewhere you make a decision on the dates?

Maybe do a screen shot and post the jpg. It may allow me to explain / understand better.

 

[Nicolette]

I have attached a picture of the results that I get with the following SQL statement

SELECT tblListings.ListingDte, tblOrderHeader.OrderDte, qryFinalProductIncomeExpenseCalculation.OrderID, tblInventory.Picture, tblListings.SKU, qryFinalProductIncomeExpenseCalculation.ProductTitle, qryFinalProductIncomeExpenseCalculation.NumberOfTimesListed, qryFinalProductIncomeExpenseCalculation.TotalProductIncome, qryFinalProductIncomeExpenseCalculation.TotalProductExpenses, qryFinalProductIncomeExpenseCalculation.ProductProfitLoss
FROM ((qryFinalProductIncomeExpenseCalculation RIGHT JOIN tblListings ON qryFinalProductIncomeExpenseCalculation.SKU = tblListings.SKU) LEFT JOIN tblOrderHeader ON qryFinalProductIncomeExpenseCalculation.OrderID = tblOrderHeader.OrderID) LEFT JOIN tblInventory ON qryFinalProductIncomeExpenseCalculation.SKU = tblInventory.SKU
WHERE ((([Enter A Month]) Like Month([ListingDte])) AND (([Enter A Year]) Like Year([ListingDte])));

The results that i get are all listings for the month of december in 2006 which is what i asked the query to fetch, but as you can see in the picture the 2 orders are not sold until May 2008 so these should not be showing as orders just as listing. Also those are the only two records with all fields filled in. where as I need all of them filled in

I want to use this information in a report that will have all the listings and sales during a month and year I input as a parameter. If there are no sales I want just the listings to show. so I can figure out the months income and expenses.

I'm thinking that there is a Major flaw in my design of the query maybe? I'm not sure


i have also tried the following where clause but this produces no records

WHERE (((tblListings.ListingDte)=DateSerial(Year([OrderDte]),Month([OrderDte]),1)) AND ((tblOrderHeader.OrderDte)=DateSerial(Year([ListingDte]),Month([ListingDte]),1)))

I am going to continue playing with this but if you have a suggestion please let me know

 

[Nicolette]

Somewhere you make a decision on the dates?

the month and year a simply query parameters

 

[PNGBill]

Sorry Nicolette, I mean't a screen shot of the form where you click the button.

 

[Nicolette]

no problem here it is