Inner Joins (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

carrie -> Inner Joins (6/22/2006 9:32:08)

Can somebody help me with this query? I'm trying to pull all of the records where the difference between today's date, and the date in the record is greater than 25 days.

SQL_Search = "Select ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name, ListingLeads.Prop_Addr, ListingLeads.Category, " _
& "ListingLeadsMailType.DateSent from ListingLeads " _
& "INNER JOIN ListingLeadsMailType ON ListingLeads.ContactID = ListingLeadsMailType.ContactID " _
& " WHERE datediff("d", [ListingLeadsMailType.DateSent], [date]) > 25 ORDER BY Category ASC, Last_Name ASC "

I keep getting an expected end of statement error that is around the 'datediff' function. Do I need more parentheses, or can I just not use that WHERE clause? I've got it working inside an Access query, but I'm trying to use it in a web page & it's not working.

Thanks for any help,
Carrie



rdouglass -> RE: Inner Joins (6/22/2006 9:45:14)

quote:

...WHERE datediff("d", [ListingLeadsMailType.DateSent], ...


Try it like this:

...WHERE datediff('d', [ListingLeadsMailType...

That help any?



markhawker -> RE: Inner Joins (6/22/2006 9:47:50)

Should [ListingLeadsMailType.DateSent] be [ListingLeadsMailType].[DateSent] and you might have to prefix the last ORDER BY statements with their respective tables?



carrie -> RE: Inner Joins (6/22/2006 10:05:16)

I had to use both solutions, and correct another oversight:

datediff('d', [ListingLeadsMailType].[DateSent], '" &date & "')

That got me results. Thanks for the help guys!

Carrie



carrie -> RE: Inner Joins (6/22/2006 10:22:09)

Now how do you think I can get the record with the most recent date?

I tried this:
"Select ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name, ListingLeads.Prop_Addr, ListingLeads.Category, " _
& "Max(ListingLeadsMailType.DateSent) from ListingLeads " _
& "INNER JOIN ListingLeadsMailType ON ListingLeads.ContactID = ListingLeadsMailType.ContactID " _
& " WHERE datediff('d', [ListingLeadsMailType].[DateSent], '" &date & "') > 50 ORDER BY Category ASC, Last_Name ASC "

and I'm getting an error:

'You tried to execute a query that does not include the specified expression 'ContactID' as part of an aggregate function'

Each record can have from 1 to 3 dates, but I only care about the most recent date for this query.



markhawker -> RE: Inner Joins (6/22/2006 10:29:02)

Do you need a GROUP BY ListingLeads.ContactID after your WHERE?



carrie -> RE: Inner Joins (6/22/2006 10:57:27)

Thank you for being so willing to help.

Let me explain what I'm trying to do.

ListingLeads is a table that has Name and Address Information for prospects. ListingLeadsMailType is a table that has dates that mailers were sent to these prospects.

Since we're adding new prospects all the time, some records in ListingLeadsMailType may have more than one date.

I'm trying to create a report that shows who's due for a mailer, by selecting the records that were sent mailers more than 25 days ago. Some mailers were sent yesterday, but because a previous mailer was sent to the same person last month, the record is showing up in the results, even though they are not due for a mailer.

I've been trying to select the most recent date of the mailer, but only if it's more than 25 days old. I can't pull the Max date, because I'm going to get records with mailers that I sent yesterday, yet I don't know if it's possible to pull the Max date if it's older than 25 days.

This is what I currently have, but it's not working with the easy to figure out 'Unspecified Error' message:

"Select ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name, ListingLeads.Prop_Addr, ListingLeads.Category, " _
& " (Select Max(ListingLeadsMailType.DateSent) from ListingLeadsMailType WHERE ListingLeads.ContactID = ListingLeadsMailType.ContactID " _
& " AND datediff('d', [ListingLeadsMailType].[DateSent], '" &date & "') > 25)"

Thanks again,
Carrie




markhawker -> RE: Inner Joins (6/22/2006 11:07:18)

You could use a HAVING MAX(date) > 25 clause? If you've already sent a mailer out will the count of entries in ListingLeadsMailType be greater than 1? If so, also use HAVING COUNT(ListingLeads.ContactID) > 1. Does that shed any light?



carrie -> RE: Inner Joins (6/22/2006 11:18:02)

Having Max is a great idea.
ListingLeadsMailType holds a date record for each corresponding ListingLeads record, but in some cases, when the prospect was first entered, say back in March, ListingLeadsMailType will have 3 records with separate dates for mailers.

I will try the having max date clause & let you know.



carrie -> RE: Inner Joins (6/22/2006 11:37:16)

I get a "'Last_Name' not part of the aggregate function . . . error when trying to use the query below: It doesn't like the Max function where I have it.

How can I select the record with matching contactID whose most recent date is more than 25 days from today?

"Select ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name, " _
& "ListingLeads.Prop_Addr, ListingLeads.Category, " _
& "ListingLeadsMailType.DateSent from ListingLeads " _
& "INNER JOIN ListingLeadsMailType ON ListingLeads.ContactID = ListingLeadsMailType.ContactID " _
& "GROUP BY ListingLeads.ContactID HAVING Max(ListingLeadsMailType.DateSent) < '5/31/06'"



markhawker -> RE: Inner Joins (6/22/2006 11:54:03)

How about FIRST(ListingLeads.Last_Name), FIRST(ListingLeads.First_Name) etc.

quote:

How can I select the record with matching contactID whose most recent date is more than 25 days from today?


There should be a dateadd() function much like your datediff() function where you can add days.



carrie -> RE: Inner Joins (6/22/2006 12:05:38)

Thanks so much Mark, but the records are from 2 different tables.

The dates are in one table (and each record may have more than one date), and the names are in the other. I assumed I needed to join the two tables, but I only want to pull one date record for each name record, the most recent date older than 25 days.

This is as close as I have gotten, but I think it's returning all of the records, not just the one I'm asking for:

"Select ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name, " _
& "ListingLeads.Prop_Addr, ListingLeads.Category, " _
& "ListingLeadsMailType.DateSent from ListingLeads " _
& "INNER JOIN ListingLeadsMailType ON ListingLeads.ContactID = ListingLeadsMailType.ContactID " _
& "WHERE datediff('d',(Select Max(DateSent) from ListingLeadsMailType), '" &date&"') > 25"

To me, the query above says, 'Select the contactid, last name, first name, property address, category and datesent, where the difference in the amount of days between the most recent date and today's date is greater than 25. But it's giving me every record - sometimes more than once, instead of just the records I think I'm asking for.

Carrie




markhawker -> RE: Inner Joins (6/22/2006 12:44:08)

LOL, OK, you're looking for:

SELECT ll.id, ll.lastname, ll.firstname, ll.address, ll.category, mt.datesent
FROM listingleads AS ll
WHERE ll.id IN (SELECT DISTINCT id FROM mailtype AS mt WHERE MAX(date) > now()+25)

How does that do?

NB That's in kinda pseudo-SQL!



carrie -> RE: Inner Joins (6/22/2006 14:49:49)

It looks promising, but I'm getting a 'value of one or more parameters is missing' error when I try to execute this:

"SELECT LL.ContactID, LL.Last_Name, LL.First_Name, LL.Prop_Addr, LL.Category, mt.DateSent " _
& "FROM ListingLeads AS LL WHERE LL.ContactID IN (SELECT DISTINCT ContactID FROM ListingLeadsMailType " _
& "AS mt WHERE (Select MAX(DateSent) from ListingLeadsMailType) < '" & date - 25 & "')"


can you see what the problem is?



markhawker -> RE: Inner Joins (6/22/2006 14:53:26)

quote:

WHERE (Select MAX(DateSent) from ListingLeadsMailType) < '" & date - 25 & "')"

Should be WHERE (MAX(DateSent) < '" & date - 25 & "') (I think!)



rdouglass -> RE: Inner Joins (6/22/2006 15:22:41)

quote:

< '" & date - 25 & "')


Would that not be:

< #" & dateadd('d',-25,date()) & "#)

...for Access anyways?



carrie -> RE: Inner Joins (6/22/2006 15:27:29)

No, it cries about aggregate functions if I use the MAX keyword by itself. I took out the lead mt.DateSent field & got the query to run with no errors, but it's still returning the wrong records.

For example, It will return Frank Owens (from listingleads) who's received a mailer on 6/05/06, 4/24/06, and 3/16/06 (3 entries in listingleadsmailtype). His name should not be in the results because his most recent mailer was sent less than 25 days ago.


"SELECT LL.ContactID, LL.Last_Name, LL.First_Name, LL.Prop_Addr, LL.Category " _
& "FROM ListingLeads AS LL WHERE LL.ContactID IN (SELECT DISTINCT mt.ContactID FROM ListingLeadsMailType " _
& "AS mt WHERE (Select MAX(DateSent) from ListingLeadsMailType) < '" & date - 25 & "')"


I'm just about to give up. I've worked for hours on this, and I'm no closer than when I started. If you think of anything I'm missing, let me know.

Thanks,
Carrie



markhawker -> RE: Inner Joins (6/22/2006 15:36:59)

Carrie, did you say you have it in Access? If so, would you be able to e-mail it to me? This is definitely do-able!! Promise!



markhawker -> RE: Inner Joins (6/22/2006 15:38:08)

Shouldn't that be a > not a < ? Or use NOT IN.



markhawker -> RE: Inner Joins (6/22/2006 17:08:10)

Carrie, it might just be me, but your date fields are set as text so will the date functions not work? I'm trying everything I can think of!



carrie -> RE: Inner Joins (6/23/2006 8:42:04)

It could be. I'll see what I can do. Thanks for looking at it.



rdouglass -> RE: Inner Joins (6/23/2006 8:56:25)

If it were me, I'd back up and be sure I can accomplish the JOIN without the WHERE clause . Have you confirmed that yet?



carrie -> RE: Inner Joins (6/23/2006 9:10:26)

Yeah, I've gotten all the above queries to run, join or no join, but I'm not getting the results I want.
I may have to find another way . . .



yb2 -> RE: Inner Joins (6/23/2006 12:55:57)

Have you thought about using an archive table so that only the most recent mailers are in the table, and the older ones sit in the archive table?

quote:

"Select ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name, " _
& "ListingLeads.Prop_Addr, ListingLeads.Category, " _
& "ListingLeadsMailType.DateSent from ListingLeads " _
& "INNER JOIN ListingLeadsMailType ON ListingLeads.ContactID = ListingLeadsMailType.ContactID " _
& "GROUP BY ListingLeads.ContactID HAVING Max(ListingLeadsMailType.DateSent) < '5/31/06'"


every column retrieved by the query needs to be included in the GROUP BY clause

"Select ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name, " _
& "ListingLeads.Prop_Addr, ListingLeads.Category, " _
& "ListingLeadsMailType.DateSent from ListingLeads " _
& "INNER JOIN ListingLeadsMailType ON ListingLeads.ContactID = ListingLeadsMailType.ContactID " _
& "GROUP BY ListingLeads.ContactID, ListingLeads.Last_Name, ListingLeads.First_Name,ListingLeads.Prop_Addr, ListingLeads.Category,ListingLeadsMailType.DateSent from ListingLeads " _
& "HAVING Max(ListingLeadsMailType.DateSent) < '5/31/06'"



carrie -> RE: Inner Joins (6/23/2006 13:24:54)

No, I haven't thought of archive table.
If it means moving all old records to another table then it sounds like an awful lot of maintenance. I'm adding new records every week, so people would get their monthly mailer on different weeks.

Also, I don't know about you, but everytime I try to use the MAX keyword I get an error about aggregate functions. The only way I can use max is by using it within a select statement.

Remember, this code is from a webpage - not from within Access - if it makes any difference.

Carrie

I think I'm just going to have to come up with a different system.



yb2 -> RE: Inner Joins (6/23/2006 14:21:13)

I know it's frustrating, but you still have options, and one of them is certainly to change the system. You could also create a query that does that maintenance for you with a click of a button. You could also use a more powerful database like SQL Server Express, as Access is very useful but essentially it's a reporting tool and doesn't expose the same amount of power as Express does. You'd then have the option of triggers and scheduling that are beyond the scope of Access.

You will get an error about aggregate functions if you don't include all the columns in the Group By, that's just the way it is, and then you can look at why the MAX may or may not be causing a problem.

If you're going to stick with the subquery then you could try replacing the max function with a Between

SELECT DISTINCT LL.ContactID, LL.Last_Name, LL.First_Name, LL.Prop_Addr, LL.Category
FROM ListingLeads LL
WHERE LL.DateSent BETWEEN '" & date & "' AND '" & date - 25 & "'
ORDER BY LL.ContactID, LL.DateSent DESC

How does that work?



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125