Trying to make Super Query, Perhaps you can help make it Simpler (1 Viewer)

[Bill Bisco]

Dear all,

I have a slightly complicated set of data that I have to be careful to correctly extract. Any help on making this simpler would be appreciated.

I have a field called Claim # and another field called Month and another field called Quantity. A Claim will appear only 1 time in any given month, but sometimes the same Claim appears over multiple months. Only the first time any given claim appears it is valid and will be included on our query.

I want to find the Sum of Quantity from all claims each month while excluding any repeat Claims in subsequent months. My quick and dirty method is to make a query for each month and then include a large number of left joins to ensure that only one month is included every time with the proper records.

Any ideas or suggestions?

-- Bill

 

[plog]

This will not be possible unless you have something more specific than a Month field. What happens when a claim is filed in November 2012, December 2012 and January 2013? You would want November returned, however January (1) comes before November (11) so it will attribute that claim to the incorrect month.

Do you have a more detailed date field than 'Month' (which by the way is a bad field name because it is a reserved word in Access)?

 

[Bill Bisco]

I have a year Field as well. For the moment January 2013 is not an issue, I'm compiling data for 2012.

Thanks,

Bill

 

[plog]

For the moment January 2013 is not an issue...

Are you in management where I work?

As for your problem, you are going to want to build an Aggregate query that determines the first appearance of each claim in your data. Since you don't have a field with date information and two fields with Month and Year values, I suggest you create a date value in a sub-query for every record using the CDate function (http://www.techonthenet.com/access/functions/datatype/cdate.php). Then you would find the minimum value of that field for every claim number. That query determines which specific claims to use from your main table.

If you can post the pertinent fields from your main data source, some sample data and what you want returned in your query based on that sample data I can provide more specific help.

 

[Bill Bisco]

Thank you very much plog. Your suggestion to use First Worked! Now I have the unique set of data I was craving.

You helped greatly,

Bill

 

[Pat Hartman]

First is not the same as minimum. The Min() function will always return the earliest date but the First() function may not because it is dependent on record order.

 

[plog]

I agree with Pat and would like to point out that I never recommended the First() function.

 

[Bill Bisco]

Query changed to Min

Thanks!

--Bill