January 16, 2005 at 1:46 pm
I am trying to generate a list of subscriptions from 2 Access tables. The SQL that I have works except I can’t get an outer join.
I have the following 2 tables:
tSubsAdds
Subscrno Number Subscription number (PK)
Subaddeffdate Date Effective date of subscription address (PK)
Delfirstnam Text Delivery address first name
Dellastnam Text Delivery address last name
Delhouseno Text Delivery address house or building number
Delstreetnam Text Delivery address street name
Delcity Text Delivery address city
Delstate Text Delivery address state or province
Delzipcode Text Delivery address zip or postal code
tSubsRoutes
Subscrno Number Subscription number (PK)
Subrouteeffdate Date Effective date of subscription route (PK)
Subrouteno Text Subscription route number
The following generates the list:
SELECT t1.Subscrno, t1.Subaddeffdate, t1.Delhouseno, t1.Delaptno, t1.Delstreetnam, t1.Delcity, t1.Delstate, t1.Delzipcode, t1.Deltitle, t1.Delfirstnam, t1.Dellastnam, t1.Delstreetnam, t2.Subrouteeffdate, t2.Subrouteno
FROM tSubsAdds AS t1 LEFT JOIN tSubsroutes AS t2 ON t1.Subscrno=t2.Subscrno
WHERE (((t2.Subrouteeffdate)=(SELECT max(Subrouteeffdate) FROM tSubsroutes WHERE Subscrno = t2.Subscrno AND Subrouteeffdate <= Date())) AND ((t1.Subaddeffdate)=(SELECT max(Subaddeffdate) FROM tSubsadds WHERE Subscrno = t1.Subscrno AND Subaddeffdate <= Date())) AND ((t1.Delstreetnam) Like "O*"))
The SQL appears to work except if there is a record for a subscription in tSubsAdds but there is no record in tSubsRoutes, there is no row generated for that subscription number in the result. Is there any way to get it to produce a row for every record in tSubsAdds (that satisfies the WHERE clause) even if there is none for that subscription in tSubsRoutes without using separate saved queries? I’ve tried changing LEFT to INNER and to RIGHT but it didn’t work.
The purpose of the list is to display current information about subscriptions. The current information for a subscription in both tSubsAdds and tSubsRoutes (see above) is the record with the highest effective date up to today. Subscription number and effective date are the key fields in the tables so there is only 1 record per date per subscription. The join matches records from tSubsAdds with corresponding records from tSubsRoutes by subscription number and returns the current address from tSubsAdds as well as the current route number from tSubsRoutes. Thus, there is 1 and only 1 row for each subscription number in the result. It is important to realize that the dates in the 2 tables are not necessarily synchronized (and are otherwise of no interest). There may be many records in each table for a subscription number, or there may be none at all, but all the records for a subscription have a different date. What we are interested in is the highest one but not over todays’s date. The records before that are part of history and those with higher dates haven’t kicked in yet.
The result is also filtered down by the field Delstreetnam, the street name, which is in tSubsAdds, to make the list selectable and faster. (
There is also a main or header table called tSubs (not needed in this case):
tSubs
Subscrno Number Subscription number (PK)
various fields
Thanks for looking.
Robert
January 17, 2005 at 12:58 am
It's pretty complex, but your problem might be in the WHERE clause, specifically the
WHERE (((t2.Subrouteeffdate)=(SELECT ...)))
bit. If you have a record in t1 with no matching record in t2, then t2.Subrouteeffdate will be null and the record will not be returned, regardless of the left join.
So perhaps you need to add
or t2.Subrouteeffdate is null
to this part of the WHERE clause.
Good luck
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 17, 2005 at 5:08 am
Thanks for responding.
This almost works. When I insert this change the query does return a row for subscriptions which have no record in tSubsRoutes but it does not return a row where there is a record in tSubsRoutes that does not satisfy the condition (effective date <= today’s date). I may not have made it clear before that there could be no record at all for a subscription in tSubsRoutes or there could be a record that doesn’t meet the condition. Either way is the same thing. Is there a way to tweak this change to correct that without getting the wrong route returned?
Or is what I really need to do is tell sql that my query keys off of tSubsAdds and that tSubsRoutes is just a lookup? With it being necessary to return the correct record (i.e. where effective date <= today’s date in both tables).
Anyone have any ideas. I can post the tables if that’s possible here.
January 17, 2005 at 5:21 am
I think I understand what you are getting at, but some sample data highlighting what you are currently getting and what you want to get would help significantly. To simplify this, please just include the tables/columns that are relevant to the SQL you have posted, to make it easier to read.
Cheers
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 17, 2005 at 7:08 am
How do I attach an Access db?
January 17, 2005 at 3:01 pm
Okay, I just got the word. No attachments!
Let’s try this. I have the following 2 tables:
tSubsAdds
Subscrno Number Subscription number (PK)
Subaddeffdate Date Effective date of subscription address (PK)
Delhouseno Text Delivery address house or building number
Delstreetnam Text Delivery address street name
tSubsRoutes
Subscrno Number Subscription number (PK)
Subrouteeffdate Date Effective date of subscription route (PK)
Subrouteno Text Subscription route number
Example for tSubsAdds:
Subscrno Subaddeffdate Delhouseno Delstreetnam
1 01/01/2000 100 Elm St.
1 07/31/2002 350 Oak St.
2 01/01/2001 100 Oak St.
3 01/01/2005 55 Sycamore St.
4 01/01/1999 19 Park Ave.
5 02/15/2005 101 Ohio St.
6 03/31/1998 4 Ohio St.
6 07/20/2004 137 Walnut St.
6 02/01/2005 400 Elm St.
for tSubsRoutes:
Subscrno Subrouteeffdate Routeno
1 01/01/2000 1
1 01/01/2001 8
2 01/01/2001 99
4 02/15/2005 59
5 02/15/2005 42
6 03/31/1998 34
6 02/01/2005 8
The correct result with a selection of ‘A’ to ‘ZZZ’ on Delstreetnam should be:
Subscrno Subaddeffdate Delhouseno Delstreetnam Subreouteeffdate Routeno
1 07/31/2002 350 Oak St. 01/01/2001 8
2 01/01/2001 100 Oak St. 01/01/2001 99
3 01/01/2005 55 Sycamore St.
4 01/01/1999 19 Park Ave.
6 07/20/2004 137 Walnut t. 03/31/1998 34
In my original sql, subscriptions 3 and 4 do not appear in the result, 3 because there is no record for it in tSubsRoutes and 4 because the only record in tSubsRoutes is post-dated. That is the problem. Note the blank Routeno and Subrouteeffdate above. If I make the change suggested by Phil, 3 comes in but 4 doesn’t.
Subscription 5 does not appear in the result because the records in both tables are post-dated. Post-dated records do not participate in the result. This is fine. What I need is to get 3 and 4 in the result. They are not post-dated in tSubsAdds.
Hope this makes it clear.
January 17, 2005 at 10:37 pm
OK, this is a beast, as you've got multiple subqueries to cope with the required GROUP BYs in tables linked by a LEFT JOIN. Query works in SQL Server, you might have to adapt it for Access:
select
t1.subscrno, t1.Subaddeffdate, t2.Delhouseno, t2.Delstreetnam, t4.Subrouteeffdate, t4.routeno
from
(select x1.Subscrno, max(x1.Subaddeffdate) Subaddeffdate
from dbo.tsubsadds x1 where x1.Subaddeffdate < getdate()
group by x1.Subscrno) t1
inner join dbo.tsubsadds t2 on
t1.subscrno = t2.subscrno and t1.Subaddeffdate = t2.Subaddeffdate
left outer join
(select
x3.subscrno, x3.Subrouteeffdate, t3.routeno
from
(select
x2.subscrno, max(x2.Subrouteeffdate) Subrouteeffdate
from
dbo.tSubsRoutes x2
where
x2.Subrouteeffdate < getdate()
group by
x2.subscrno) x3 inner join
dbo.tSubsRoutes t3 on x3.subscrno = t3.subscrno and x3.Subrouteeffdate = t3.Subrouteeffdate) t4
on t1.subscrno = t4.subscrno
order by
t1.subscrno
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 18, 2005 at 9:27 am
Access doesn't like it. What is the meaning of
max(x1.Subaddeffdate) Subaddeffdate
with no comma?
And where is table dbo defined?
Robert
January 18, 2005 at 3:03 pm
max(x1.Subaddeffdate) Subaddeffdate
- Subaddeffdate is an alias for the column, and this alias is used elsewhere in the query. It is referring to the same column, hence no comma.
dbo is the owner of the table. In SQL Server, tables within a db can have different owners, so you sometimes need to qualify table names with their owner names. Not a problem in Access, so you can delete 'dbo.' wherever it appears.
However, I still think that you might have a problem making the entire query work as one and therefore suggest that you try splitting it down as follows (do you know how to use a query as a datasource for another query in Access?):
Q1
select x1.Subscrno, max(x1.Subaddeffdate) Subaddeffdate
from dbo.tsubsadds x1 where x1.Subaddeffdate < getdate()
group by x1.Subscrno
Q2
select
x2.subscrno, max(x2.Subrouteeffdate) Subrouteeffdate
from
dbo.tSubsRoutes x2
where
x2.Subrouteeffdate < getdate()
group by
x2.subscrno
Q3
select
x3.subscrno, x3.Subrouteeffdate, t3.routeno
from
(select from Q2) x3 inner join
dbo.tSubsRoutes t3 on x3.subscrno = t3.subscrno and x3.Subrouteeffdate = t3.Subrouteeffdate
Q4
select
t1.subscrno, t1.Subaddeffdate, t2.Delhouseno, t2.Delstreetnam, t4.Subrouteeffdate, t4.routeno
from
(select from Q1) t1
inner join dbo.tsubsadds t2 on
t1.subscrno = t2.subscrno and t1.Subaddeffdate = t2.Subaddeffdate
left outer join
(select from Q3) t4
order by
t1.subscrno
Hope that helps!
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 18, 2005 at 9:05 pm
In Access you have to use the keyword 'As' when aliasing a column.
max(x1.Subaddeffdate) As Subaddeffdate
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 18, 2005 at 9:09 pm
Going with SQLserver, suppose you have 500,000 records in each of the 2 tables. I need to allow the user to generate a report. To do that in a short while I allow the input of a value for the street name in tSubsAdds. (I’m also going to do one for the actual delivery name.) A Where clause needs to be added as in my original SQL. With an index defned on the Delstreetnam field in tSubsAdds, jumping through and reading only the specified records should be quick. But what about the join? Will it jump through tSubsRoutes and pick up only the needed records or will it search through the entire table (which could take hours)?
Robert
January 18, 2005 at 9:13 pm
Kathi,
You mean:
select
t1.subscrno, t1.Subaddeffdate, t2.Delhouseno, t2.Delstreetnam, t4.Subrouteeffdate, t4.routeno
from
(select x1.Subscrno, max(x1.Subaddeffdate) as Subaddeffdate
from tsubsadds as x1 where x1.Subaddeffdate < Date()
group by x1.Subscrno) as t1
inner join tsubsadds as t2 on
t1.subscrno = t2.subscrno and t1.Subaddeffdate = t2.Subaddeffdate
left join
(select
x3.subscrno, x3.Subrouteeffdate, t3.routeno
from
(select
x2.subscrno, max(x2.Subrouteeffdate) as Subrouteeffdate
from
tSubsRoutes as x2
where
x2.Subrouteeffdate < Date()
group by
x2.subscrno) as x3 inner join
tSubsRoutes as t3 on x3.subscrno = t3.subscrno and x3.Subrouteeffdate = t3.Subrouteeffdate) as t4
on t1.subscrno = t4.subscrno
Access still won't take it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply