Query returns 9 records with criteria that exists in one record

  • Thread starter Thread starter ZOYA
  • Start date Start date
Z

ZOYA

hello - my query has a field, Parcel Number. Criteria in this field is a
field from a form the person enters data into. that part works.
my query also has a field Type so when a person chooses record 01, which is
a weed type, they get a weed report.

problem - When I specify parcel number 01, the query returns, and it shows 9
of this record on the screen, which in turn is giving me an 18 page report, 9
each of parcel 01. (report is 2 pages). HELLLLLPPPPPPPP.
 
Kind of need some more information. I'm guessing that your query is finding
9 matches where Parcel Number = 01, which could be correct if the table(s)
being queried have 9 entries for that parcel. Not knowing your database I'll
try to give a generic example:

Say you have your list of Parcels in one table - all it has is Parcel Number
and some details about each Parcel. In a second table, you have all the
records posted to that Parcel. This would be your Fact table where it has
many records per Parcel (e.g. Apr 2008 - Parcel 01 - wheat; Aug 2008 - Parcel
01 - clear; Sep 2008 - Parcel 01 - weeds [assuming you're referring to
parcels of land and what is on it]).

Anyway, it sounds like your query is a simple join:

SELECT ParcelTable.ParcelNumber, FactTable.Date, FactTable.Activity
FROM FactTable INNER JOIN ParcelTable ON FactTable.ParcelNumber =
ParcelTable.FactNumber;

If this is the case, you need to give it some filter to restrict what it
brings back (e.g. MAX of the Date field to get the latest entry). If you
have two (or 9 in your case) entries for Parcel Number 01 and Type = Weed,
then you end up 9 records being returned. For sure you want to group the
records by something (e.g. Type) to reduce the record count returned.
 
if you are seeing duplicate information it is most likly due to what
bobt has said you may want to add distinct to the query but you do
need to supply more information like what is the data that the query
is returning

Regards
Kelvan
 
Back
Top