Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 5th, 2007, 01:46 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default How can I adjust this SQL query?

Hello

I have a Microsoft Access Database and am trying to use MS Access to build a SQL statement so that I can use that inside my own computer program. I am quite comfortable with run of the mill SQL statements for adding, updating and deleting. But I now have need of what is a more complex SQL statement. I am almost there but not completely.

This is what I have right now:

Code:
SELECT Congregations.Congregations, Congregations.Coordinator, Congregations.[Last Invited], Max([Home Talks].[Last Given]) AS [MaxOfLast Given]
FROM Congregations LEFT JOIN [Home Talks] ON Congregations.Congregations = [Home Talks].Congregation
GROUP BY Congregations.Congregations, Congregations.Coordinator, Congregations.[Last Invited]
ORDER BY Congregations.Congregations;
This does not do exactly what I want. It returns a table with 2 date columns. But I only want one date column. I want the date to be either:

Congregations.[Last Invited]
Max([Home Talks].[Last Given])

which ever is the most recent date should be used.

I hope this is possible and that someone can teach me how to adapt this query to achieve my desired query results.

Thank you in advance.

Andrew

  #2 (permalink)  
Old October 5th, 2007, 02:01 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I am not sure if Access supports CASE but you can try it.

Your code would be something like:

CASE
   WHEN Congregations.[Last Invited] > Max([Hom Talks].[Last Given]) Then Congregations.[Last Invited]
   Else Max([Hom Talks].[Last Given])
END as [Most Recent]

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========
  #3 (permalink)  
Old October 5th, 2007, 02:10 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

Thanks for the suggestion. I have tried this:

SELECT Congregations.Congregations, Congregations.Coordinator, CASE
   WHEN Congregations.[Last Invited] > Max([Home Talks].[Last Given]) Then Congregations.[Last Invited]
   Else Max([Home Talks].[Last Given])
END as [Most Recent]

FROM Congregations LEFT JOIN [Home Talks] ON Congregations.Congregations = [Home Talks].Congregation
GROUP BY Congregations.Congregations, Congregations.Coordinator, Congregations.[Last Invited]
ORDER BY Congregations.Congregations;

But it won't let me save the query. Is the syntax wrong?

  #4 (permalink)  
Old October 5th, 2007, 02:34 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Hmm. It looks ok to me. I wrote this query real quick (though in SQL Server) and it works ok:

Code:
SELECT
    CASE
        when rev_date > Max(depart_time) then rev_date
        else Max(depart_time)
    END as [Date]
From [table]
group by rev_date
order by [Date] asc
What happens if you rewrite your query with just the case?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========
  #5 (permalink)  
Old October 5th, 2007, 02:48 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT
CASE
   WHEN Congregations.[Last Invited] > Max([Home Talks].[Last Given]) Then Congregations.[Last Invited]
   Else Max([Home Talks].[Last Given])
END as [Most Recent]

FROM Congregations LEFT JOIN [Home Talks] ON Congregations.Congregations = [Home Talks].Congregation
GROUP BY Congregations.Congregations, Congregations.[Last Invited]
ORDER BY Congregations.Congregations;

This doe snot work. It says sytax error (missing operator.

  #6 (permalink)  
Old October 5th, 2007, 02:52 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Alrighty, Access cant use CASE what it does have, however, is Switch:

http://www.techonthenet.com/access/f...ced/switch.php

Alternatively you could look at using iif but that would get a little messy I think.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========
  #7 (permalink)  
Old October 5th, 2007, 03:31 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Update.

If I create two queries:

Query 1:

SELECT Congregations.Congregations, Congregations.Coordinator, Switch([Home Talks].[Last Given]>[Congregations].[Last Invited],Max([Home Talks].[Last Given]),[Congregations].[Last Invited],[Congregations].[Last Invited]) AS NewDate
FROM Congregations LEFT JOIN [Home Talks] ON Congregations.Congregations = [Home Talks].Congregation
GROUP BY Congregations.Congregations, Congregations.Coordinator, Congregations.[Last Invited], [Home Talks].[Last Given];

Query 2

SELECT Test.Congregations, Test.Coordinator, Test.NewDate
FROM Test
GROUP BY Test.Congregations, Test.Coordinator, Test.NewDate
ORDER BY Test.NewDate;

I end up with what seems to be the right data. But I have two questions:

1. When I use the ORDER BY on the NewDate field it does not seem to obey it. It does for Congregations and Coordinator. But not NewDate. Why?

2. How do I combine these two queries? In real life I will be executing one SQL statement to obtain one record set.

Thanks.

Andrew

  #8 (permalink)  
Old October 5th, 2007, 03:47 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I do not know the answer to your first question, your queries seem to be ok.

To combine them you could use UNION ALL:

SELECT Congregations.Congregations, Congregations.Coordinator, Switch([Home Talks].[Last Given]>[Congregations].[Last Invited],Max([Home Talks].[Last Given]),[Congregations].[Last Invited],[Congregations].[Last Invited]) AS NewDate
FROM Congregations LEFT JOIN [Home Talks] ON Congregations.Congregations = [Home Talks].Congregation
GROUP BY Congregations.Congregations, Congregations.Coordinator, Congregations.[Last Invited], [Home Talks].[Last Given];

Union All

SELECT Test.Congregations, Test.Coordinator, Test.NewDate
FROM Test
GROUP BY Test.Congregations, Test.Coordinator, Test.NewDate
ORDER BY Test.NewDate;

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========
  #9 (permalink)  
Old October 5th, 2007, 03:52 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, that seems to pose some problems. Firstly, it does not allow the sorting by Test.NewDate. It says I must use a field from the first select clause. But if I change it to just "NewDate" it accepts it. Even though the sorting is not obeyed.


Then it went back to showing multiple rows which is not quite right.
  #10 (permalink)  
Old October 5th, 2007, 09:59 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there... well.. the problem is that you want to sort the 2 querys using only one sorted order... and you can't do that...
you will have to subquery this (something like select * from (yourunionquery) order by (yourlastcolumnname))
it will show you multiple rows because your second query brings a lot of records from the test table.. maybe you want to do something else and we don't get your point?? want to show a little example?

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========




Similar Threads
Thread Thread Starter Forum Replies Last Post
Adjust row height of gridview wirerider ASP.NET 2.0 Basics 5 February 1st, 2008 07:01 AM
adjust zone time kherrerab BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 6 July 2nd, 2007 08:53 AM
function adjust-dateTime-to-timezone Tomi XSLT 3 July 26th, 2006 11:51 AM
Adjust Pie Chart Size terencechua Reporting Services 0 January 19th, 2006 10:49 PM
How to adjust several SQL scripts in crystal repor juma Crystal Reports 0 July 10th, 2004 01:34 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.