April 8, 2008 at 1:10 pm
Hello, I have two tables that I need to select the maximum date from both.
Table1 has fields: Table2 has fields:
File_Name, File_Desc, File_Name, Act_Code,
and Downloaded_Date and Act_Date
What I have so far is:
Select
Table1.*, Table2.*
From Table1, Table2
Where Table1.File_Name = Table2.File_Name
and File_Desc = Trim(cbFileDesc.text) **Match text from combobox.
and Downloaded_Date = Act_Date
and Downloaded_Date = (Select Max(Downloaded_Date) from Table1)
I need to match the dates from both tables so I get the latest matching record from both, but I thought if I selected the maximum date from Table1, it would pull the latest from Table2 also since the date would match. Of course, not so and I do not know how to select the max from both tables.
Any help would be greatly appreciated!! Thanks!
April 8, 2008 at 1:20 pm
This should give you what you want:
Select
Table1.*,
Table2.*
From
Table1 T1 INNER JOIN
Table2 T2 On
T1.File_Name = T2.File_Name And
T1.Downloaded_Date = T2.Act_Date INNER JOIN
(Select
File_Name,
Max(DownLoaded_Date) as Max_Date
From
Table1
Group By
File_Name) as T3 On
T1.File_Name = T3.File_Name And
T1.Downloaded_Date = T3.Max_Date
Where
T1.File_Desc = Trim(cbFileDesc.text) --Match text from combobox.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2008 at 1:31 pm
Thanks for responding Jack!
I get a "missing keyword" error.
April 8, 2008 at 1:45 pm
Select
Table1.*, Table2.*
From Table1, Table2
Where Table1.File_Name = Table2.File_Name
and File_Desc = Trim(cbFileDesc.text) **Match text from combobox.
and Downloaded_Date = Act_Date
and Downloaded_Date = (Select Max(Downloaded_Date) from Table1)
The issue I don't see addressed is what happens if you the max date value in either table doesn't exist in the other or is different (thsi can be caused by data values such a GETDATE() being used as well).
For example
The max Downloaded_Date could be 1/1/2008 1:01:36.018 PM
The max Act_Date could be 1/1/2008 1:01:36.038 PM
so the values are different and will not resolve the way you intend.
So you need to ask these questions first.
1) Is there a chance the max value can be different in each?
2) How do I know which records go together?
3) Are there ways to get if there is extraneous data?
4) What is my final goal?
Exampl of what values can exist in each table would help us to understand what we are trying to accomplish.
April 8, 2008 at 1:56 pm
marge0513 (4/8/2008)
Thanks for responding Jack!I get a "missing keyword" error.
In the subquery put TABLE.File_Name instead of File_Name as File_Name is a function.
Antares' questions are also valid.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2008 at 2:01 pm
Thanks for responding!
Well, I guess there could be a chance that the time would be different, but I took off the "seconds" so it would only be the hour and minute, but I don't really know what the chances would be. I'm still kind of new to this. I'm using:
FormatDateTime('mm/dd/yyyy hh:mm', Now()) to insert the date.
The two tables are only matched up by the file_name and the time.
I want the user to retrieve the latest row from each table so that they can change the status of that file or view where that file stands, for example, if that file has been received, downloaded, loaded, etc.
We want to keep a history of all rows instead of updating the same one.
That's why I need to make sure the user gets the latest information for the particular file.
April 8, 2008 at 2:30 pm
Thanks Jack, I'm still getting the same message.
April 8, 2008 at 2:40 pm
Sorry. Replace the Table1.* and Table2.* with T1.* and T2.*. I copied and pasted your original code, modified it and forgot to change those to the Aliases I created.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2008 at 2:50 pm
Ok, it officially hates me!! LOL!!
I'm still getting the same error.
April 8, 2008 at 3:01 pm
marge0513 (4/8/2008)
Thanks for responding Jack!I get a "missing keyword" error.
SQL doesn't have a "TRIM" function, only RTRIM and LTRIM.
So replace the last line with:
T1.File_Desc = Ltrim(RTrim(cbFileDesc.text) )
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 3:09 pm
Man Matt, am I glad you are around to clean up behind me!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2008 at 3:41 pm
Jack Corbett (4/8/2008)
Man Matt, am I glad you are around to clean up behind me!
It's the least I could do... You already did the heavy lifting...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 6:07 am
Thanks for responding!
I tried that too by just taking that part out and putting in string value
T1.File_Desc = 'LEVY'
April 9, 2008 at 6:59 am
marge0513 (4/9/2008)
Thanks for responding!I tried that too by just taking that part out and putting in string value
T1.File_Desc = 'LEVY'
Does this mean you're all set, or are you still having trouble?
If you're having trouble - maybe post your latest test "victim" and tell us what's not right about it, so that we can see how to help you. I for one am no longer sure what the overall code looks like.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 7:04 am
Thanks Matt, I'm still having problems. I leave work at 5:00, so I just got in this morning and started "playing" with this code again. I've tried a few things, but I'm still getting the "missing keyword" error. It's not fun being a newbie! LOL!! Anyway, here's the latest:
Select T1.*, T2.*
From
Table1 T1 INNER JOIN
Table2 T2 On
T1.File_Name = T2.File_Name And
T1.Downloaded_Date = T2.Act_Date INNER JOIN
(Select
T1.File_Name,
Max(T1.DownLoaded_Date) as Max_Date
From
T1
Group By
T1.File_Name) as T3 On
T1.File_Name = T3.File_Name And
T1.Downloaded_Date = T3.Max_Date
Where
T1.File_Desc = 'LEVY'
Thanks!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply