Well people, here I am with another plea for assistance. I have two tables
in a production database which I've stripped down to the bare essentials
here for purposes of this request. The first table (tblService) contains a
record off all services given and the second table (tblAddress) has a list
peoples address which records their addresses including changes over time.
What I need is a query that will pull the data from tblService and add a
field which shows the person's zip code at the time the service was
delivered. Anyone give me a hand.
CREATE TABLE tblService (
[CtId] [int] NOT NULL,
[Service] [int] NOT NULL,
[DateOfService] [datetime] NOT NULL
)
CREATE TABLE tblAddress (
[CtId] [int] NOT NULL,
[DateStart] [datetime] NOT NULL,
[DateStop] [datetime] NULL,
[Zip] [int] NULL
)
CREATE TABLE tblResults (
[CtId] [int],
[Service] [int],
[DateOfService] [datetime],
[Zip] [int]
)
INSERT tblService (1, 1, '3/1/2003')
INSERT tblService (1, 2, '3/10/2003')
INSERT tblService (2, 1, '4/4/2003')
INSERT tblService (2, 2, '5/3/2003')
INSERT tblService (3, 1, '3/2/2003')
INSERT tblService (4, 1, '4/4/2003')
INSERT tblAddress (1, '2/1/1995', '2/28/2003', 90028)
INSERT tblAddress (1, '3/1/2003', NULL, 90010)
INSERT tblAddress (2, '3/3/1998', '4/3/2003', 90078)
INSERT tblAddress (2, '4/4/2003', '5/1/2003', 90054)
INSERT tblAddress (2, '5/2/2003', NULL, 90043)
INSERT tblAddress (3, '3/5/2003', NULL, 90042)
INSERT tblAddress (4, '3/3/2002', '4/1/2003', 90028)
INSERT tblAddress (4, '4/2/2003', '5/30/2003', 90038)
Primary Key for all tables is CtId.
Expected Results:
tblResults:
CtId Service DateOfService Zip
1 1 3/1/2003 90010
1 2 3/10/2003 90010
2 1 4/4/2003 90054
2 2 5/3/2003 90043
3 1 3/2/2003 NULL
4 1 4/4/2003 90038
Any assistance would be greatly appreciated.
Query Help Request