Creating unique valued queries from non-unique tables

B

Brien Clark

I'm trying to create a query that has all unique values. I am working with
two tables: IDFeatures and Readings.

The IDFeatures table contains all the IDfeatures (a unique number given to
each object) as well as some static information pertaining to each IDfeature.
In this table, there are no duplicate IDfeatures.

The Readings table has readings and dates for each IDfeature. There are
multiple readings/dates for many of the IDfeatures, so there are duplicate
IDfeatures (with different readings/dates).

The tables are set up with a relationship so that the IDfeature is correlated.

Right now, I can only create a query that displays all the readings (with a
duplicate IDfeature for each different reading). I am trying to create a
query that displays only one IDfeature with the most current readings from
the Readings table. Using the DISTINCT command does not work, since the
Readings table has non-duplicate columns (i.e. different readings dates).

Someone suggested using a MAX command, but I don't know if that actually
exists (I couldn't find it in the help file or Balter's Mastering Access).

Does anyone have any ideas? Thank you
 
S

Sharkbyte

When you build your query, use the Totals function (The funny looking E),
then change GroupBy for your dates to Max. That sounds like what you want.

Sharkbyte
 
B

Brien Clark

Thanks Sharkbyte, I changed the groupby to max but it didn't help. It still
displays duplicate IDfeatures with different readdates. Any more suggestions
by chance? Thank you
 
S

Sharkbyte

What are the fields in your query? Or even cut and paste the SQL of the
query. It sounds like there is another field, in the query, causing the
extra rows.

If it is simply IDFeatures (group by) and ReadDates (Max), you should not be
getting duplicate rows.

Sharkbyte
 
B

Brien Clark

There are some other static fields (from IDfeatures) and some dynamic data
(from readings). Here is the SQL:

SELECT DISTINCT tblIDFeatures.IDFEATURE, tblIDFeatures.RDNUMBER,
tblIDFeatures.TIEDOWN, tblReadings.READDATE, tblReadings.FPIPE1ON,
tblReadings.FPIPE1OFF, tblReadings.FPIPE2ON, tblReadings.FPIPE2OFF,
tblReadings.FFPIPE1ON, tblReadings.FFPIPE1OFF, tblReadings.FFPIPE2ON,
tblReadings.FFPIPE2OFF, tblReadings.FSPANON, tblReadings.FSPANOFF,
tblReadings.FANODE, tblReadings.SURFREF, tblReadings.COMMENTS,
tblReadings.WHO, tblReadings.Status, tblReadings.DISREPAIR
FROM tblIDFeatures INNER JOIN tblReadings ON tblIDFeatures.IDFEATURE =
tblReadings.IDFEATURE
WHERE (((tblReadings.READDATE) Between #1/1/2004# And Now()));

The date criteria at the end is a necessary filter, but I still just want to
display 1 date that corresponds to 1 IDfeature in that critera range. THank
you
 
S

Sharkbyte

Ok. You are going to need to create 2 queries.

When you use the totals function, Access tries to group by every field in
the query, so where you are getting duplicate IDFEATUREs, there is most
likely some different data in one of the other fields you are grabbing.

Try this:

query 1 > SELECT tblIDFeatures.IDFEATURE, MAX(tblReadings.READDATE) as
MaxReadDate FROM tblIDFeatures INNER JOIN tblReadings ON
tblIDFeatures.IDFEATURE = tblReadings.IDFEATURE GROUP BY
tblIDFeatures.IDFEATURE WHERE (((tblReadings.READDATE) Between #1/1/2004# And
Now()));

query 2 > link Query 1, tblIDFeatures, and tblReadings together and pull
the rest of your data.

This should get you your distinct, max dates.

Sharkbyte
 

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

Top