Access 2003 - Possible Subquery Problem

[KOelschlaeger]

I am trying to create a query within a query in which there are more than one foreign keys, and within each set of foreign keys, there is more than one date. I want to retrieve the latest date for each foreign key and then I want to delete the remaining dates. I don't want to delete the record of the duplicated foreign key, just the older dates. How do I go about comparing the dates, and when I do, having it where only the latest date is remaining? The end result should be that for each set of foreign keys, there should only be one date.

i.e.,

FK Date

1 1/1/2003
1 2/21/2004
1 3/14/2005
2 10/19/2003
2 12/1/2003
2 5/6/2007

Result should look like:

FK Date

1
1
1 3/14/2005
2
2
2 5/6/2007


Thank you.

KOelschlaeger

[MMcCarthy]

Try this ...

UPDATE QueryName
SET [Date]=Null
WHERE [Date] < DMax("[Date]","QueryName","[FK]=" & [FK])
 

[KOelschlaeger]

The suggested code seems to be deleting other columns than the specified Date column. It also seems to delete the date I would like to keep in the specified Date column. Here is an example of what I'm working with (The TTID is the primary key): [What I would like for it to look like is below this example].

FdrID NegDate NegFinishDate NegMiles TTID StartDate
3 1/1/2006 3/31/2006 35.12 6960 4/1/2006
3 1/1/2006 3/31/2006 35.12 2430 1/12/2002
3 1/1/2006 3/31/2006 35.12 121 1/9/1999
4 1/1/2006 3/31/2006 17.6 6861 3/18/2006
4 1/1/2006 3/31/2006 17.6 2431 2/23/2002
4 1/1/2006 3/31/2006 17.6 122 12/12/1998
5 1/1/2006 3/31/2006 18.25 6860 3/4/2006
5 1/1/2006 3/31/2006 18.25 2432 3/16/2002
5 1/1/2006 3/31/2006 18.25 123 11/14/1998

FdrID NegDate NegFinishDate NegMiles TTID StartDate
3 1/1/2006 3/31/2006 35.12 6960 4/1/2006
3 2430 1/12/2002
3 121 1/9/1999
4 1/1/2006 3/31/2006 17.6 6861 3/18/2006
4 2431 2/23/2002
4 122 12/12/1998
5 1/1/2006 3/31/2006 18.25 6860 3/4/2006
5 2432 3/16/2002
5 123 11/14/1998

Thank you.

Try this ...

UPDATE QueryName
SET [Date]=Null
WHERE [Date] < DMax("[Date]","QueryName","[FK]=" & [FK])
 

[MMcCarthy]

Is this the query you are running?

UPDATE QueryName
SET [NegDate]=Null,  [NegFinishDate]=Null
WHERE [NegDate] < DMax("[NegDate]","QueryName","[ FdrID]=" & [ FdrID])
 

[KOelschlaeger]

Actually, it's something like this:

UPDATE qryNegotiatedDates
SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
WHERE (((qryNegotiatedDates.StartDate)<DMax("StartDate", "qryNegotiatedDates","FdrID=" & "FdrID")));

I'm basing the date in which to determine which Negotiated information to keep on the latest Start Date information. Is this incorrect, or how should I go about determining a solution?

Is this the query you are running?

UPDATE QueryName
SET [NegDate]=Null,  [NegFinishDate]=Null
WHERE [NegDate] < DMax("[NegDate]","QueryName","[ FdrID]=" & [ FdrID])
 

[MMcCarthy]

Try this ...

UPDATE qryNegotiatedDates 
SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]=" & [FdrID])));
 

[MMcCarthy]

Just thinking, is FdrID a text field?

[KOelschlaeger]

Okay. I basically copied and pasted what you have and ALL the information went blank. I've even tried it when you place "NegDate" in place of "StartDate" to see if it makes a difference. It doesn't. I'm trying to avoid doing this by hand, and it seems like the problem shouldn't be too difficult to solve. However, I guess it's more complicated than I had originally figured.

Thank you.

Try this ...

UPDATE qryNegotiatedDates 
SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]=" & "[FdrID]")));
 

[KOelschlaeger]

Yes, FdrID is a text field.

Just thinking, is FdrID a text field?

[MMcCarthy]

OK, try this ...

UPDATE qryNegotiatedDates 
SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]='" & [FdrID] & "'")));
 

[KOelschlaeger]

I'm having trouble understanding the last part, do you mean to have quotes around the second [FdrID]? It's giving me a "Data type mismatch in critera expression" when I copy what you have and when I place quotes around [FdrID]. What does the single quotation do so that I may fully understand the logic.

Thank you.

OK, try this ...

UPDATE qryNegotiatedDates 
SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]=' " & [FdrID] & " ' ")));
 

[MMcCarthy]

I'm having trouble understanding the last part, do you mean to have quotes around the second [FdrID]? It's giving me a "Data type mismatch in critera expression" when I copy what you have and when I place quotes around [FdrID]. What does the single quotation do so that I may fully understand the logic.

Thank you.

The single quotes denote a text field rather than a number field. Are you sure the field is a text field?

BTW, I've been re-reading the thread and I'm just wondering where these values in the query are being drawn from. If these dates are only entered singly in their original table but are being repeated in the query you can't do what you are trying to do.

Mary

[KOelschlaeger]

Yes, you're deduction is correct. It has been entered singly in one table and being duplicated in the query. Do I have any other options?

Otherwise, now that I know I'm up against a wall, I'll attempt other possibilities.

Thank you.

The single quotes denote a text field rather than a number field. Are you sure the field is a text field?

BTW, I've been re-reading the thread and I'm just wondering where these values in the query are being drawn from. If these dates are only entered singly in their original table but are being repeated in the query you can't do what you are trying to do.

Mary

[MMcCarthy]

Yes, you're deduction is correct. It has been entered singly in one table and being duplicated in the query. Do I have any other options?

Otherwise, now that I know I'm up against a wall, I'll attempt other possibilities.

Thank you.

If you are using this query for a report just group on the date and it will only show once.