Query to Return Earliest Contract Date

[music_al]

Hi

I have a table of staff with (for simplicity)...

• Spot_ID (this is the Staff ID)
• Full_Name
• Initial_Start_Date
• and other stuff...

The 'Initial Start Date' is the start date of the staff member's very first contract using this Dlookup...

=DLookUp("[MinOfStart_Date]","[qry_Initial_Start_Date2]")


I have a related table that stores their contracts, with...

• Spot_ID
• Start Date
• Role
• End Date
• and other stuff

In the staff table I am trying to show the start date of the very first contract that is in the Contracts table for each member of staff.

The problem is, the earliest date of one of the staff records is being shown against the other record. What am I doing wrong ?

 

[isladogs]

Create an aggregate query for the Contracts table using the totals button and add SpotID and InitialStartDate fields.
In the Totals row of the query design change InitialStartDate to Min.
Run your query.

 

[music_al]

Colin, isn't that what I've done ? See the screen shots.

 

[music_al]

Maybe I need to understand what an AGGREGATE query is.

 

[music_al]

Even though INITIAL START DATE isn't actually in the Contracts table ?

 

[Gasman]

Surely you need criteria as well?

=DLookUp("[MinOfStart_Date]","[qry_Initial_Start_Date2]", "Spot_ID = " & Me.SpotID )

Hi

I have a table of staff with (for simplicity)...

• Spot_ID (this is the Staff ID)
• Full_Name
• Initial_Start_Date
• and other stuff...

The 'Initial Start Date' is the start date of the staff member's very first contract using this Dlookup...

=DLookUp("[MinOfStart_Date]","[qry_Initial_Start_Date2]")


I have a related table that stores their contracts, with...

• Spot_ID
• Start Date
• Role
• End Date
• and other stuff

In the staff table I am trying to show the start date of the very first contract that is in the Contracts table for each member of staff.

The problem is, the earliest date of one of the staff records is being shown against the other record. What am I doing wrong ?

View attachment 72293
View attachment 72294

 

[isladogs]

Sorry - use Start_Date field from Contracts table - see screenshot for an aggregate (totals) query

 

[music_al]

Ive copied the last argument in but its now saying its Invalid Syntax.

 

[music_al]

Colin, that is exactly what I have.

 

[music_al]

Gasman, should you last argument include the UNDERSCORE between Spot and ID ?
Spot_ID

Because I've added this and I now get a #NAME? in the field.

 

[isladogs]

Colin, that is exactly what I have.

Its not what your screenshot in post #1 shows. That wasn't an aggregate query. You have to click the Totals button in the ribbon

 

[music_al]

I had created an aggregate query first. That is shown in my screenshot.



This is then used in my second query which I show in my first post.

 

[isladogs]

Ah but I'm not psychic!
So what was wrong with the original aggregate query ... and why do you need both dates from table & query?

 

[music_al]

OK, I've got it to work by adding the criteria argument that Gasman added (but had to fix the error he'd made.

the Dlookup should have been...

=DLookUp("[MinOfStart_Date]","[qry_Initial_Start_Date2]","Spot_ID=" & [Spot_ID])

However...

In the New Record row, I'm now seeing #Error. How do I suppress this ??

 

[Gasman]

Pardon me for a typing error.

OK, I've got it to work by adding the criteria argument that Gasman added (but had to fix the error he'd made.

the Dlookup should have been...

=DLookUp("[MinOfStart_Date]","[qry_Initial_Start_Date2]","Spot_ID=" & [Spot_ID])

However...

In the New Record row, I'm now seeing #Error. How do I suppress this ??

 

[music_al]

Sorry, wasn't meant as a criticism just wanted to help any future readers of this post.