Form output data manipulation

[bearsgone]

Hello
I have a form that has a search button. Search button calls on a query.

SELECT Info.InfoEntryID, Info.InfoDate, Info.InfoBarrelNum, Info.InfoPh, Info.InfoSalinity, Info.InfoAcid, Info.InfoDip, Info.InfoText, Info.InfoMRU, Info.InfoBarrelID
FROM Info
WHERE (((Info.InfoDate)>=[Forms]![frmInfoBatchEntry]![DateFrom] And (Info.InfoDate)<=[Forms]![frmInfoBatchEntry]![DateTo])
AND ((Info.InfoPh)>=[Forms]![frmInfoBatchEntry]![phLow]
And (Info.InfoPh)<=[Forms]![frmInfoBatchEntry]![phHigh])
AND ((Info.InfoSalinity)>=[Forms]![frmInfoBatchEntry]![SaltLow]
And (Info.InfoSalinity)<=[Forms]![frmInfoBatchEntry]![SaltHigh])
AND ((Info.InfoAcid)>=[Forms]![frmInfoBatchEntry]![AcidLow]
And (Info.InfoAcid)<=[Forms]![frmInfoBatchEntry]![AcidHigh])
AND ((Info.InfoDip)>=[Forms]![frmInfoBatchEntry]![DipLow]
And (Info.InfoDip)<=[Forms]![frmInfoBatchEntry]![DipHigh]));

the issue is that for some date ranges there are multiple identical barrel numbers such as:

Range 1/1/2010 - 2/1/2010

Date Barrel Acid
1/1/2010 5 6
1/2/2010 5 7
1/1/2010 6 8
5/1/2010 7 1

Should be 3 records, first record is gun because it is not the latest input for that barrel.

Date Barrel Acid
1/2/2010 5 7
1/1/2010 6 8
5/1/2010 7 1


. For any given date range (((Info.InfoDate)>=[Forms]![frmInfoBatchEntry]![DateFrom] And (Info.InfoDate)<=[Forms]![frmInfoBatchEntry]![DateTo])
if there are multiple barrels select the barrel with the latest date. So it is date range with maximum date if there are multiple identical barrels.
Any ideas or examples would be greatly appreciated.
Thank you

 

[SmallTime]

Hi Bearsgone

I tried reading this over a couple of times. Maybe it's just me but I can't seem to make head nor tail of your question.

Maybe simpifying your question would help.

 

[SmallTime]

Ok I get it,


You could use a unique records on your query.

 

[bearsgone]

SmallTime

I tried different combinations. Can you give an example of what unique records might look like?

Thank you

 

[SmallTime]

use SELECT DISTINCTROW

you might want to do this just for the two fields Date and Barrel and then base another query on the first one with acid.

use the query designe view as it'll be much easier to see whats going on. the unique recordes setting is in the property sheet.

 

[SmallTime]

Hi Bearsgone

How did you get along? Ddid you manage to work it out or do you still need further help?

SmallTime

 

[bearsgone]

I tried adding max to a date and distinctrow. Did not help and date no shows as #Name? because of Max setting for the date.

Here is what I have:
SELECT DISTINCTROW Info.InfoEntryID, Info.InfoBarrelNum, Info.InfoPh, Info.InfoSalinity, Info.InfoAcid, Info.InfoDip, Info.InfoText, Info.InfoMRU, Info.InfoBarrelID, Max(Info.InfoDate) AS MaxOfInfoDate
FROM Info
GROUP BY Info.InfoEntryID, Info.InfoBarrelNum, Info.InfoPh, Info.InfoSalinity, Info.InfoAcid, Info.InfoDip, Info.InfoText, Info.InfoMRU, Info.InfoBarrelID
HAVING (((Info.InfoPh)>=[Forms]![frmInfoBatchEntry]![phLow] And (Info.InfoPh)<=[Forms]![frmInfoBatchEntry]![phHigh]) AND ((Info.InfoSalinity)>=[Forms]![frmInfoBatchEntry]![SaltLow] And (Info.InfoSalinity)<=[Forms]![frmInfoBatchEntry]![SaltHigh]) AND ((Info.InfoAcid)>=[Forms]![frmInfoBatchEntry]![AcidLow] And (Info.InfoAcid)<=[Forms]![frmInfoBatchEntry]![AcidHigh]) AND ((Info.InfoDip)>=[Forms]![frmInfoBatchEntry]![DipLow] And (Info.InfoDip)<=[Forms]![frmInfoBatchEntry]![DipHigh]) AND ((Max(Info.InfoDate))>=[Forms]![frmInfoBatchEntry]![DateFrom] And (Max(Info.InfoDate))<=[Forms]![frmInfoBatchEntry]![DateTo]));