Unexpected Query Results - Not enough records

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
 
M

Michel Walsh

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...
 
J

Jasonm

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...
 
M

Michel Walsh

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...
 
J

Jasonm

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting date gives bad results in query 2
Query not return all records 1
Simple query not working 3
Not Enough Space On Temporary Disk When Open/run Ms Access Append Query 0
Query Does Not Pick Up Data 5
Query results and display nulls? 4
Adding records to filtered query via linked table 3
Query not returning all records from a linked Excel table 1

Top