Query to Return Earliest Contract Date (1 Viewer)

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #1

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

MVP / VIP

Local time

Today, 08:57

Joined

Jan 14, 2017

Messages

18,209

• Oct 11, 2018

• #2

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.

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #3

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

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #4

Maybe I need to understand what an AGGREGATE query is.

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #5

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

 

Gasman

Enthusiastic Amateur

Local time

Today, 08:57

Joined

Sep 21, 2011

Messages

14,232

• Oct 11, 2018

• #6

Surely you need criteria as well?

Code:Copy to clipboard

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

music_al said:

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

Click to expand...

 

isladogs

MVP / VIP

Local time

Today, 08:57

Joined

Jan 14, 2017

Messages

18,209

• Oct 11, 2018

• #7

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

 

Attachments

• 
  Capture.PNG

  60.9 KB · Views: 38

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #8

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

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #9

Colin, that is exactly what I have.

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #10

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

MVP / VIP

Local time

Today, 08:57

Joined

Jan 14, 2017

Messages

18,209

• Oct 11, 2018

• #11

music_al said:

Colin, that is exactly what I have.

Click to expand...

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

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #12

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

MVP / VIP

Local time

Today, 08:57

Joined

Jan 14, 2017

Messages

18,209

• Oct 11, 2018

• #13

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?

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #14

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

Enthusiastic Amateur

Local time

Today, 08:57

Joined

Sep 21, 2011

Messages

14,232

• Oct 11, 2018

• #15

Pardon me for a typing error.

music_al said:

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 ??

Click to expand...

 

M

music_al

Registered User.

Local time

Today, 08:57

Joined

Nov 23, 2007

Messages

200

• Oct 11, 2018

• #16

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