How can I adjust this SQL query?

ajtruckle · October 5th, 2007, 01:46 PM

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

dparsons · October 5th, 2007, 02:01 PM

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

ajtruckle · October 5th, 2007, 02:10 PM

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?

dparsons · October 5th, 2007, 02:34 PM

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

ajtruckle · October 5th, 2007, 02:48 PM

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.

dparsons · October 5th, 2007, 02:52 PM

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

ajtruckle · October 5th, 2007, 03:31 PM

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

dparsons · October 5th, 2007, 03:47 PM

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

ajtruckle · October 5th, 2007, 03:52 PM

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.

gbianchi · October 5th, 2007, 09:59 PM

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