Having only one field in query be distinct 1

[bozic12]

Hi everyone,
I have a question that I'm sure is fairly easy to answer. I have a simple select query with 5 fields from a table called "Milling Cutter" and a date field from the table, "Tool Characteristics". The tables are joined by Assembly Number, which is in the query. In the Tool Characteristics table, there can be records with the same assembly number but different dates, so the query returns all of these records. I only need one of them for the report that runs off of this query. Since the records have a different date, they aren't exactly the same, and so just adding Select Distinct .... won't eliminate the duplicate assembly numbered records. Is there a way I can have the query run with only the assembly number field being distinct (or every field except for date), and not the whole select statement. I assume it's a pretty easy SQL statement or maybe even some sort of query parameter, but I'm not too familiar with SQL. Thank you.

-Jeff

 

[CosmoKramer]

To return the most recent date try using the Max function:

Select Max([YourDateField]).....

and set the query to Group By the remaining fields and you should be all set....

 

[bozic12]

cosmo,
Here is my query after inserting your suggestions:

SELECT max( [Tool Characteristics].[The Date]), ([Milling Cutter].[Assembly Number]), [Milling Cutter].Customer, [Milling Cutter].Material, [Milling Cutter].[Material Grade], [Milling Cutter].[Lead Angle]
FROM [Milling Cutter] INNER JOIN [Tool Characteristics] ON [Milling Cutter].[Assembly Number] = [Tool Characteristics].[Assembly Number]
WHERE (((InStr(1,NamesSelected(),"," & [Milling Cutter]![Lead Angle] & ",&quot)>0))
GROUP BY [Milling Cutter].[Assembly Number], [Milling Cutter].Customer, [Milling Cutter].Material, [Milling Cutter].[Material Grade], [Milling Cutter].[Lead Angle];

When I run it, I get a parameter dialogue box asking for the date. If i put a date in, the report runs without the duplicate assembly number records, but the date field for all records is the value i enter in the parameter box.
any suggestions???

-Jeff

 

[CosmoKramer]

Sorry, may bad. I forgot that you have to alias the date field:

SELECT max( [Tool Characteristics].[The Date])

As MaxOfTheDate

, ([Milling Cutter].[Assembly Number]), [Milling Cutter].Customer, [Milling Cutter].Material, [Milling Cutter].[Material Grade], [Milling Cutter].[Lead Angle]
FROM [Milling Cutter] INNER JOIN [Tool Characteristics] ON [Milling Cutter].[Assembly Number] = [Tool Characteristics].[Assembly Number]
WHERE (((InStr(1,NamesSelected(),"," & [Milling Cutter]![Lead Angle] & ","))>0))
GROUP BY [Milling Cutter].[Assembly Number], [Milling Cutter].Customer, [Milling Cutter].Material, [Milling Cutter].[Material Grade], [Milling Cutter].[Lead Angle];

 

[bozic12]

I still get the enter parameter for "The Date" dialogue box with the addition of an alias.

 

[CosmoKramer]

Is

"The Date"

the correct name of the field in the Tool Characteristics table??

 

[bozic12]

yep -- [Tool Characteristics]![The Date]

 

[CosmoKramer]

I don't know, this syntax works for me....Can you send me a zipped copy of the db??

sw3540@yahoo.com

 

[bozic12]

I sent it. Thank you for your time trying to help me out, I really appreciate it

 

[CosmoKramer]

Jeff,

It looks like there were a couple of problems.

First, the reason you were being prompted for [The Date] was because the report had [The Date] field, but the query didn't have it, it has the Min alias field. The detail field was changed, and the sort was changed to the MinOfTheDate field instead.

Then, I think you have to go with a two query approach. The first (AngleQuery1) will return the distinct list of [Lead Angle] with the earlist date. This query and the other two tables are used in AngleQuery2 to get the other fields necessary for the report. I changed the RecordSource of the Orders By Angle report to AngleQuery2.

Let me know if this is what you're looking for and good luck with the rest of your project.....

 

[bozic12]

Steven,
Your changes do exactly what I desired. Thank you so much for all your time you put forth to help me out, I really appreciate it. I'd give you more than one star if I could.

-Jeff