Help with Access 2000 compare query

  • Thread starter Thread starter Stumped!!
  • Start date Start date
S

Stumped!!

I have a table that shows Name, Address, Customer #, and date of
Service. I'm trying to create a query that shows customers with dates
of service in 1997 with no service in 1998 - 2002 but service in 2003.
Can this be done in one query or do I have to create multiple queries
and join them? I'm diving into Access for the first time and am having
trouble figuring this out. Any help would be appriciated. I have two
books that are over 1000 pages each and can't find the answer.

Nick
 
Not a good practice to use "Name" as the name of a field or a control; Name,
as are many other special words, mean special things to ACCESS (reserved
words or names of VBA functions). Also not good to use special characters
such as # in those names, as # is a date/time delimiter.

Try this SQL statement:

SELECT [Name], Address, [Customer #]
FROM Tablename
WHERE Year([Date of Service]) = 1997 OR
Year([Date of Service]) = 2003
GROUP BY [Name], Address, [Customer #];
 
I only used "name" as an example. The actual data field is "custname",
"custnumber" and so on, no special characters. I'll try what you gave
me. Thanks.
 
SELECT [Customer#]
FROM TableName
WHERE DateOfService Between #1/1/97# AND #12/31/97#
AND [Customer#] In
(SELECT T.[Customer#]
FROM TableName as T
WHERE T.DateOfService Between #1/1/03# AND #12/31/03#)
AND [Customer#] NOT IN
(SELECT T2.[Customer#]
FROM TableName as T2
WHERE T2.DateOfService Between #1/1/98# AND #12/31/02#)

That could be slow, since Access doesn't handle NOT IN very well.

You could try replacing the not in with an exists clause

And NOT EXISTS (SELECT T2.[Customer#]
FROM TableName as T2
WHERE T2.DateOfService Between #1/1/98# AND #12/31/02#
AND T2.[Customer#] = TableName.[Customer#])

MULTIPLE QUERIES WOULD PROBABLY BE FASTER

Query one gets all customers that have service in 98-02 period
Query two uses query one in a Left Join to get non matches

SELECT [Customer#]
FROM TableName LEFT JOIN QueryOne
ON Tablename.[Customer#] = QueryOne.[Customer#]
WHERE DateOfService Between #1/1/97# AND #12/31/97#
AND [Customer#] In
(SELECT T.[Customer#]
FROM TableName as T
WHERE T.DateOfService Between #1/1/03# AND #12/31/03#)
AND QueryOne.[Customer#] Is Null

Other options are available depending on your version of access.
 
The SQL example that I gave you isn't exactly what you asked for....my
apology. I just now see that you also wanted a criterion of "no service
during 1998 through 2002" -- my SQL doesn't include that.

I believe that John Spencer's reply is more akin to what you seek.

--
Ken Snell
<MS ACCESS MVP>

Stumped!! said:
I only used "name" as an example. The actual data field is "custname",
"custnumber" and so on, no special characters. I'll try what you gave
me. Thanks.

Not a good practice to use "Name" as the name of a field or a control; Name,
as are many other special words, mean special things to ACCESS (reserved
words or names of VBA functions). Also not good to use special characters
such as # in those names, as # is a date/time delimiter.

Try this SQL statement:

SELECT [Name], Address, [Customer #]
FROM Tablename
WHERE Year([Date of Service]) = 1997 OR
Year([Date of Service]) = 2003
GROUP BY [Name], Address, [Customer #];
Click to expand...
Click to expand...
 
Unfortunately, This query is much harder than it should because your Table
is unnormalised. You really should have 2 Tables:

* tblCustomer with Customer details like name, address, etc ... Include a
CustID Field as the PrimaryKey for use in the One-to-Many relationship with
the second Table.

* tblService which have Records of Service with the CustID as the
ForeignKey.

You should find out about Relational Database Design Theory and
Normalisation and apply the techniques to your database.

In the mean time, assuming the [Customer#] is unique for each customer, you
can try the following *untested* SQL String using the (Non)Membership Test /
Not In clause:

SELECT DISTINCT T1.[Customer#], T1.[Name], T1.Address

FROM YourTable AS T1
INNER JOIN YourTable AS T2
ON T1.[Customer#] = T2.[Customer#]

WHERE (T1.[DateOfService] BETWEEN #01/01/1997# AND #12/31/1997#)
AND (T2.[DateOfService] BETWEEN #01/01/2003# AND #12/31/2003#)
AND
( T1.[Customer#] NOT IN
(
SELECT DISTINCT T3.[Customer#]
FROM YourTable AS T3
WHERE T3.[DateOfService]
BETWEEN #01/01/1998# AND #12/31/2002#
)
)



Alternatively, you can try the *untested* "Frustrated Outer Join":

SELECT DISTINCT T1.[Customer#], T1.[Name], T1.Address

FROM
(
YourTable AS T1
LEFT JOIN YourTable AS T3
ON T1.[Customer#] = T3.[Customer#]
)
INNER JOIN YourTable AS T2
ON T1.[Customer#] = T2.[Customer#]

WHERE (T1.[DateOfService] BETWEEN #01/01/1997# AND #12/31/1997#)
AND (T2.[DateOfService] BETWEEN #01/01/2003# AND #12/31/2003#)
AND (T3.[DateOfService] BETWEEN #01/01/1998# AND #12/31/2002#)
AND (T3.[Customer#] Is Null)
 
I did not identify the tables correctly or the fields. I have two
tables. One called "CustHist" the other called "CustFile". There is a
customer number in both tables.

In the "CustHist" table there is a field for customername, address1,
address2, city, state, zip, and tourstart.

I tried to run what was below with the field names changed to reflect
what we actually have but ran into trouble.

What values do I plug into the SQL string below? and can I just copy
it into Access from this reply?

Nick
 
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)



nick said:
I did not identify the tables correctly or the fields. I have two
tables. One called "CustHist" the other called "CustFile". There is a
customer number in both tables.

In the "CustHist" table there is a field for customername, address1,
address2, city, state, zip, and tourstart.
Click to expand...
Each Table should represnt an entity. Asuming the CustFile represent the
Customer and the CustHist represents tours taken by Customer then you should
only have the linking Field [Customer#] (to the CustFile) in the CustHist
Table, not other details. Name, Address, ... are attributes of the Customer
entity and therefore belong to the CustFile Table, NOT CustHist Table.


I tried to run what was below with the field names changed to reflect
what we actually have but ran into trouble.

What values do I plug into the SQL string below? and can I just copy
it into Access from this reply?
Click to expand...
replace with appropriate Table names and Field names.

You can copy my SQL String to the SQL View of the Query and modify the
names. When you open a new Query, Access present a list of Tables / Queries
for you to select. Simply close this down without selecting any Table. The
first icon on the Query Toolbar should have the text "SQL". Click this
button and you be present with the SQLView of the Query where you can copy
and paste the SQL String to.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top