By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 1,987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Access 2003 - Possible Subquery Problem

P: 7
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
May 7 '07 #1
Share this Question
Share on Google+
13 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. UPDATE QueryName
  2. SET [Date]=Null
  3. WHERE [Date] < DMax("[Date]","QueryName","[FK]=" & [FK])
  4.  
May 8 '07 #2

P: 7
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 ...
Expand|Select|Wrap|Line Numbers
  1. UPDATE QueryName
  2. SET [Date]=Null
  3. WHERE [Date] < DMax("[Date]","QueryName","[FK]=" & [FK])
  4.  
May 8 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Is this the query you are running?
Expand|Select|Wrap|Line Numbers
  1. UPDATE QueryName
  2. SET [NegDate]=Null,  [NegFinishDate]=Null
  3. WHERE [NegDate] < DMax("[NegDate]","QueryName","[ FdrID]=" & [ FdrID])
  4.  
May 8 '07 #4

P: 7
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?
Expand|Select|Wrap|Line Numbers
  1. UPDATE QueryName
  2. SET [NegDate]=Null,  [NegFinishDate]=Null
  3. WHERE [NegDate] < DMax("[NegDate]","QueryName","[ FdrID]=" & [ FdrID])
  4.  
May 8 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. UPDATE qryNegotiatedDates 
  2. SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
  3. WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]=" & [FdrID])));
  4.  
May 8 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Just thinking, is FdrID a text field?
May 8 '07 #7

P: 7
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 ...
Expand|Select|Wrap|Line Numbers
  1. UPDATE qryNegotiatedDates 
  2. SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
  3. WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]=" & "[FdrID]")));
  4.  
May 8 '07 #8

P: 7
Yes, FdrID is a text field.

Just thinking, is FdrID a text field?
May 8 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, try this ...
Expand|Select|Wrap|Line Numbers
  1. UPDATE qryNegotiatedDates 
  2. SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
  3. WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]='" & [FdrID] & "'")));
  4.  
May 8 '07 #10

P: 7
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 ...
Expand|Select|Wrap|Line Numbers
  1. UPDATE qryNegotiatedDates 
  2. SET qryNegotiatedDates.NegDate = Null, qryNegotiatedDates.NegMiles=Null, qryNegotiatedDates.NegFinishDate=Null
  3. WHERE (((qryNegotiatedDates.StartDate)<DMax("[StartDate]","qryNegotiatedDates","[FdrID]=' " & [FdrID] & " ' ")));
  4.  
May 8 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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
May 8 '07 #12

P: 7
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
May 9 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 9 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.