Only Shows Small Portion of Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The Select query displays 1287 records.

When I created this query without the Fiscal Month table, I got 1287
records. When I added the INNER JOIN with the Fiscal Month table, I only get
28 records, although I used this query on another table and it worked well.
This query also only pulls data from 2006 and 2007 and none from 2008 through
2010.

How do I get this query to show all 1287 records? #%$*#

SELECT qryMaterialDollarsSBM2.PartNo, qryMaterialDollarsSBM2.PartDesc,
qryMaterialDollarsSBM2.BLPricePD, qryMaterialDollarsSBM2.BLQtyReqPD,
qryMaterialDollarsSBM2.PullDate, qryMaterialDollarsSBM2.ArrivalDate,
qryMaterialDollarsSBM2.PRNo, qryMaterialDollarsSBM2.PRQty,
qryMaterialDollarsSBM2.PRPrice, qryMaterialDollarsSBM2.PRDueDate,
qryMaterialDollarsSBM2.PONo, qryMaterialDollarsSBM2.POQty,
qryMaterialDollarsSBM2.POPrice, qryMaterialDollarsSBM2.VPD,
qryMaterialDollarsSBM2.ReceiptDate, qryMaterialDollarsSBM2.RVPD,
tblFiscalMonths.FMNo, tblFiscalMonths.FiscalMonth, tblFiscalMonths.FiscalYear
FROM qryMaterialDollarsSBM2 INNER JOIN tblFiscalMonths ON
(qryMaterialDollarsSBM2.PullDate<=tblFiscalMonths.EndDate) AND
(qryMaterialDollarsSBM2.PullDate>=tblFiscalMonths.StartDate)
WHERE [PullDate]>=Date();
 
The Select query displays 1287 records.

When I created this query without the Fiscal Month table, I got 1287
records. When I added the INNER JOIN with the Fiscal Month table, I only get
28 records, although I used this query on another table and it worked well.
This query also only pulls data from 2006 and 2007 and none from 2008 through
2010.

How do I get this query to show all 1287 records? #%$*#

SELECT qryMaterialDollarsSBM2.PartNo, qryMaterialDollarsSBM2.PartDesc,
qryMaterialDollarsSBM2.BLPricePD, qryMaterialDollarsSBM2.BLQtyReqPD,
qryMaterialDollarsSBM2.PullDate, qryMaterialDollarsSBM2.ArrivalDate,
qryMaterialDollarsSBM2.PRNo, qryMaterialDollarsSBM2.PRQty,
qryMaterialDollarsSBM2.PRPrice, qryMaterialDollarsSBM2.PRDueDate,
qryMaterialDollarsSBM2.PONo, qryMaterialDollarsSBM2.POQty,
qryMaterialDollarsSBM2.POPrice, qryMaterialDollarsSBM2.VPD,
qryMaterialDollarsSBM2.ReceiptDate, qryMaterialDollarsSBM2.RVPD,
tblFiscalMonths.FMNo, tblFiscalMonths.FiscalMonth, tblFiscalMonths.FiscalYear
FROM qryMaterialDollarsSBM2 INNER JOIN tblFiscalMonths ON
(qryMaterialDollarsSBM2.PullDate<=tblFiscalMonths.EndDate) AND
(qryMaterialDollarsSBM2.PullDate>=tblFiscalMonths.StartDate)
WHERE [PullDate]>=Date();
Click to expand...

Presumably the data in tblFiscalMonths does not contain records such
that the records you're missing are retrieved. Check the values of
EndData and StartDatein that table; or, if you're willing to see
nothing at all in the fields FMNo, FiscalMonth, and FiscalYear, change
the INNER JOIN to LEFT JOIN.

John W. Vinson[MVP]
 
Back
Top