Unique Records

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

When pulling data from a table that has multiple parts to a key, how does
Access determine which criteria statement to read first?

DMax("EFFDT","Dbo_HR_POSITION_DATA","([Dbo_HR_POSITION_DATA].POSITION_NBR)='
" & [Dbo_HR_POSITION_DATA].[POSITION_NBR] & "'")

DMax("EFFSEQ","tblNonUniondata1","([tblNonUniondata1].EMPLID)='" &
[tblNonUniondata1].[EMPLID] & "'")

I have been creating sub queries to ensure that the order is correct. I am
hoping there is an easier way....

Thanks for your help!
 
Chris

Is there a particular business need for why you are using DMax() function
instead of a Totals query, finding the Max() value?
 
This is a good point. I have inherited some pre-written queries, and if
they used Dmax, I have too... but If I rewrite as a Totals query, and choose
Max in two different fields, then which one is it going to execute first?

Thanks,
 
I should have tested this before I wrote back, but that totals query with
the Max value selected gives me all the rows not the max value....the field
looks to be formatted as a date. The DMax function brings back just the
rows with the max date. Any ideas?

Chris
 
Chris

I'm full of it ('er, them), as folks will happily tell you. What I've seen
so far is "how" you are approaching a problem. What I'm not clear on is
what you'd like to accomplish.

If you were explaining what you want to end up with to your mother, what
would you tell her...?
 
Hi Jeff,

The queries I am working with are running off of PeopleSoft data that is
stored in multiple tables. This data is effective dated and without the
DMax statement, I get multiple rows for each employee. For the most part,
this statement has worked fine. It seems to execute first in order when I
run a query. There are times though that I would like another criteria
statement to execute first. If I have two different groups of employees for
example, say Management(MGT) and Adjunct(ADJ), I may have criterea that says
MGT first and then within that group give me the highest effective dated
row. In this case, I would write a query to first pull off the management,
and then a second to look for the most effective dated row within that group
because the employee may be in both groups and if I just pull the effective
dated row first, I may get and ADJ. When I look for MGT later, it will drop
the record.

If you would like to see a sample of the actual data, I would be happy to
include it.

Thanks again for your help,
Chris
 
Chris

Are you saying that you want to select only those rows with [EmployeeType] =
"MGT", and determine the Max([EffectiveDate]) of those?

If so, open a new query. Bring in the table.

Select [EmployeeType], add "MGT" to the criterion "cell" and uncheck the
check box.

Select [EffectiveDate]. Click on the Totals query toolbar button and select
Max for the [EffectiveDate] (it will start out showing "GroupBy").

This will generate the Max([EffectiveDate]) of all rows with "MGT" for
[EmployeeType].

Your syntax will vary...
 
Jeff,

I tried this again and here are the results:

qryTestMax

EMPLID
MaxOfEFFDT
EFFSEQ
PAYGROUP

12001956
1/1/2004
0
MGN

12001956
7/1/2003
1
MGN

12006803
1/1/2004
0
MGN

12006803
2/5/2003
1
MGN

12028055
7/1/2001
0
MGN

12028055
7/1/2001
1
MGN

qryTestMax

EMPLID
MaxOfEFFDT
MaxOfEFFSEQ
PAYGROUP

12001956
1/1/2004
1
MGN

12006803
1/1/2004
1
MGN

12028055
7/1/2001
1
MGN

qryTestMax

EMPLID
MaxOfEFFDT
FirstOfEFFSEQ
PAYGROUP

12001956
1/1/2004
0
MGN

12006803
1/1/2004
0
MGN

12028055
7/1/2001
0
MGN



I did get the MGN rows, but am still not getting a unique record....I



f I use Max on EFFSEQ, which seems to be the problem, the first two records
appear to have the wrong EFFSEQ from the second row, but that is the Max for
EFFSEQ.... The third record is correct...



If I use First for EFFSEQ, I get the first two correct, but the third has
the wrong EFFSEQ.....



Do I have some other options?



Thanks again,

Chris
 
Back
Top