Unexpected Query Results - Not enough records

  • Thread starter Thread starter Jasonm
  • Start date Start date
J

Jasonm

Good morning. I am running the following query:

SELECT MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy") AS SampleYear, Format([sampledate],"m") AS
SampleMonth, MonthlyReport.BWFlow AS AVGBWFlow
FROM MonthlyReport RIGHT JOIN MillPond ON MonthlyReport.MRDate =
MillPond.SampleDate
GROUP BY MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy"), Format([sampledate],"m"), MonthlyReport.BWFlow
HAVING (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want to
run this report for?"]));

The table MonthlyReport has an entry for every day of the year while the
table MillPond has only one entry for each week (more or less).

I have set the query so that I should be getting ALL of the records from
MonthlyReport, and only the recrods from MillPond where the date fields are
equal. The query only retuns 51 records (the number of records in the
MillPond table for 2008). Should this be the case? Am I just misinterpreting
the way this query should work?

Are there any suggestions from the group on how to acomplish what I just
explained? Any assistance would be appreciated.

Jasonm
 
I have set the query so that I should be getting ALL of the records from
MonthlyReport,
Click to expand...


Nope, it seems you did the reverse, with:

FROM MonthlyReport RIGHT JOIN MillPond

All records from MillPond, since MillPond is at the RIGHT of the word JOIN,
will be kept. Try:


FROM MonthlyReport LEFT JOIN MillPond


to get all records from MonthlyReport, or

FROM MillPondMonthlyReport RIGHT JOIN MonthlyReport

to get the table MonthlyReport at the 'specified' side of the word JOIN.



Vanderghast, Access MVP


Jasonm said:
Good morning. I am running the following query:

SELECT MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy") AS SampleYear, Format([sampledate],"m") AS
SampleMonth, MonthlyReport.BWFlow AS AVGBWFlow
FROM MonthlyReport RIGHT JOIN MillPond ON MonthlyReport.MRDate =
MillPond.SampleDate
GROUP BY MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy"), Format([sampledate],"m"),
MonthlyReport.BWFlow
HAVING (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want to
run this report for?"]));

The table MonthlyReport has an entry for every day of the year while the
table MillPond has only one entry for each week (more or less).

I have set the query so that I should be getting ALL of the records from
MonthlyReport, and only the recrods from MillPond where the date fields
are equal. The query only retuns 51 records (the number of records in the
MillPond table for 2008). Should this be the case? Am I just
misinterpreting the way this query should work?

Are there any suggestions from the group on how to acomplish what I just
explained? Any assistance would be appreciated.

Jasonm
Click to expand...
 
Michel, I have tried both configurations listed below, and get the same
number of records. Do you have any other suggestions? I think I will shut
down, restart and try again... who knows maybe my conputer is having a bad
hair day!

Thanks, Jm


Michel Walsh said:
I have set the query so that I should be getting ALL of the records from
MonthlyReport,
Click to expand...


Nope, it seems you did the reverse, with:

FROM MonthlyReport RIGHT JOIN MillPond

All records from MillPond, since MillPond is at the RIGHT of the word
JOIN, will be kept. Try:


FROM MonthlyReport LEFT JOIN MillPond


to get all records from MonthlyReport, or

FROM MillPondMonthlyReport RIGHT JOIN MonthlyReport

to get the table MonthlyReport at the 'specified' side of the word JOIN.



Vanderghast, Access MVP


Jasonm said:
Good morning. I am running the following query:

SELECT MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy") AS SampleYear, Format([sampledate],"m") AS
SampleMonth, MonthlyReport.BWFlow AS AVGBWFlow
FROM MonthlyReport RIGHT JOIN MillPond ON MonthlyReport.MRDate =
MillPond.SampleDate
GROUP BY MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy"), Format([sampledate],"m"),
MonthlyReport.BWFlow
HAVING (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want
to run this report for?"]));

The table MonthlyReport has an entry for every day of the year while the
table MillPond has only one entry for each week (more or less).

I have set the query so that I should be getting ALL of the records from
MonthlyReport, and only the recrods from MillPond where the date fields
are equal. The query only retuns 51 records (the number of records in the
MillPond table for 2008). Should this be the case? Am I just
misinterpreting the way this query should work?

Are there any suggestions from the group on how to acomplish what I just
explained? Any assistance would be appreciated.

Jasonm
Click to expand...
Click to expand...
 
Note that if

sampledate

comes from the unpreserved table ( I assume it should be MillPond ) then
the HAVING destroy the job done by the join. Try, instead:
GROUP BY ...
HAVING (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want
to run this report for?"]))
Click to expand...
Click to expand...
Click to expand...


using:


....
WHERE sampledate IS NULL
OR (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want
to run this report for?"]))
GROUP BY ...


ie: the HAVING become a WHERE, it is placed before the GROUP BY, and you
add provision for sampledate being NULL, AFTER THE JOIN had been done.



Vanderghast, Access MVP




Jasonm said:
Michel, I have tried both configurations listed below, and get the same
number of records. Do you have any other suggestions? I think I will shut
down, restart and try again... who knows maybe my conputer is having a bad
hair day!

Thanks, Jm


Michel Walsh said:
I have set the query so that I should be getting ALL of the records from
MonthlyReport,
Click to expand...


Nope, it seems you did the reverse, with:

FROM MonthlyReport RIGHT JOIN MillPond

All records from MillPond, since MillPond is at the RIGHT of the word
JOIN, will be kept. Try:


FROM MonthlyReport LEFT JOIN MillPond


to get all records from MonthlyReport, or

FROM MillPondMonthlyReport RIGHT JOIN MonthlyReport

to get the table MonthlyReport at the 'specified' side of the word JOIN.



Vanderghast, Access MVP


Jasonm said:
Good morning. I am running the following query:

SELECT MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy") AS SampleYear, Format([sampledate],"m") AS
SampleMonth, MonthlyReport.BWFlow AS AVGBWFlow
FROM MonthlyReport RIGHT JOIN MillPond ON MonthlyReport.MRDate =
MillPond.SampleDate
GROUP BY MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy"), Format([sampledate],"m"),
MonthlyReport.BWFlow
HAVING (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want
to run this report for?"]));

The table MonthlyReport has an entry for every day of the year while the
table MillPond has only one entry for each week (more or less).

I have set the query so that I should be getting ALL of the records from
MonthlyReport, and only the recrods from MillPond where the date fields
are equal. The query only retuns 51 records (the number of records in
the MillPond table for 2008). Should this be the case? Am I just
misinterpreting the way this query should work?

Are there any suggestions from the group on how to acomplish what I just
explained? Any assistance would be appreciated.

Jasonm
Click to expand...
Click to expand...
Click to expand...
 
Thanks, that did the trick! Also, in playing around with it a bit I deleted
the "SampleYear" field and recreated it and that ALSO corrected the problem!
Go figure. I guess something was hung up somewhere.

Thanks again for your help. I really appreciate it.

Jason
Michel Walsh said:
Note that if

sampledate

comes from the unpreserved table ( I assume it should be MillPond ) then
the HAVING destroy the job done by the join. Try, instead:
GROUP BY ...
HAVING (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want
to run this report for?"]))
Click to expand...
Click to expand...


using:


...
WHERE sampledate IS NULL
OR (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want
to run this report for?"]))
GROUP BY ...


ie: the HAVING become a WHERE, it is placed before the GROUP BY, and you
add provision for sampledate being NULL, AFTER THE JOIN had been done.



Vanderghast, Access MVP




Jasonm said:
Michel, I have tried both configurations listed below, and get the same
number of records. Do you have any other suggestions? I think I will shut
down, restart and try again... who knows maybe my conputer is having a
bad hair day!

Thanks, Jm


Michel Walsh said:
I have set the query so that I should be getting ALL of the records
from MonthlyReport,


Nope, it seems you did the reverse, with:

FROM MonthlyReport RIGHT JOIN MillPond

All records from MillPond, since MillPond is at the RIGHT of the word
JOIN, will be kept. Try:


FROM MonthlyReport LEFT JOIN MillPond


to get all records from MonthlyReport, or

FROM MillPondMonthlyReport RIGHT JOIN MonthlyReport

to get the table MonthlyReport at the 'specified' side of the word JOIN.



Vanderghast, Access MVP


Good morning. I am running the following query:

SELECT MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy") AS SampleYear, Format([sampledate],"m") AS
SampleMonth, MonthlyReport.BWFlow AS AVGBWFlow
FROM MonthlyReport RIGHT JOIN MillPond ON MonthlyReport.MRDate =
MillPond.SampleDate
GROUP BY MonthlyReport.MRDate, MillPond.SampleDate, MillPond.pH,
MillPond.TotalCl2, MillPond.TSS, MillPond.NTU, MillPond.Comments,
Format([sampledate],"yyyy"), Format([sampledate],"m"),
MonthlyReport.BWFlow
HAVING (((Format([sampledate],"yyyy"))=["What CALENDAR year do you want
to run this report for?"]));

The table MonthlyReport has an entry for every day of the year while
the table MillPond has only one entry for each week (more or less).

I have set the query so that I should be getting ALL of the records
from MonthlyReport, and only the recrods from MillPond where the date
fields are equal. The query only retuns 51 records (the number of
records in the MillPond table for 2008). Should this be the case? Am I
just misinterpreting the way this query should work?

Are there any suggestions from the group on how to acomplish what I
just explained? Any assistance would be appreciated.

Jasonm
Click to expand...
Click to expand...
Click to expand...
 
Back
Top