Nested SQL

G

Greg

I am trying to do something that I believe requires nested SQL. I am having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because the
last month is 09/01/2006 and there is no month 10 record. The output from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because the
first month is 10/01/2006 and there is no month 9 record. The output from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have any
suggestions on how to do this??

Thank You,
Greg
 
H

Hunter57

I am trying to do something that I believe requires nested SQL. I am having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because the
last month is 09/01/2006 and there is no month 10 record. The output from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because the
first month is 10/01/2006 and there is no month 9 record. The output from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have any
suggestions on how to do this??

Thank You,
Greg
Click to expand...

Hi Greg,

If a number is deleted, as far as I know, you cannot retrieve the
phone number unless you store deleted numbers or changes to data in
another table. You can always archive your data and there are a
number of ways to do it. The simplest method is just to make a copy
of your database file periodically. Of course, that can use up a lot
of hard drive space if your DB is large.

However, why are you deleting phone numbers? Obviously you need them
so why not keep them? You can archive them and there are plenty of
resources on the web to help you. Allen Browne has sample code and
instructions here: http://allenbrowne.com/ser-37.html. If that is too
advanced for your present Access skills Roger J. Carlson has a sample
DB you can download here: http://www.rogersaccesslibrary.com/download3.asp?SampleName=BackUpWithCompact.mdb
There are many more examples on the Web.

The second query will require a Parameter Query. The SQL will look
something like this:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
FROM Invoice
WHERE (Invoice.InvoiceDate = (Between StartDate AND EndDate));

To add the criteria to your query in the Query Design Grid under
InvoiceDate in the Criteria row type this: BETWEEN StartDate AND
EndDate

Then on the Menu click Query and then select Parameters. A box will
open up with two columns. In the first Column enter BETWEEN StartDate
AND EndDate there also. In the second Column select Date/Time.

When you run your query a dialogue box should as for your StartDate
and then another one should ask for your EndDate. Be sure to type
them in the correct format you have specified in your table or
forms!

You can also find help for your Query in Access Help if you search for
Parameter Query.

Hope it works out well for you.

Hunter57
http://churchmanagementsoftware.googlepages.com/home
 
H

Hunter57

I am trying to do something that I believe requires nested SQL. I am having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because the
last month is 09/01/2006 and there is no month 10 record. The output from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because the
first month is 10/01/2006 and there is no month 9 record. The output from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have any
suggestions on how to do this??

Thank You,
Greg
Click to expand...

IMPORTANT CORRECTION:
I forgot the brackets and should not have put the = in the query

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
FROM Invoice
WHERE ((InvoiceDate) BETWEEN [StartDate] AND [EndDate]);

In the Query Design Grid: In the Criteria Row under InvoiceDate enter
BETWEEN [StartDate] AND [EndDate]
Be sure to include the Brackets.

When you click Query and select Parameters you need to use 2 rows in
the Parameter grid.
In the first Column, first Row enter [StartDate] in the 2nd column
select Date/Time.
In the first Column second Row enter [EndDate] and in the 2nd column
select Date/Time.


Hope that helps

Hunter57
http://churchmanagementsoftware.googlepages.com/home
 
G

Greg

Hunter57,

Thank You very much!

I am not doing a good job explaining myself.

I am thinking that in my first query I could type into the query 09/01/2006.
I can then determine if a number is deleted if the number appears with a
date of 09/01/2006 but there is no appearance of the number with a date of
10/01/2006. This indicates the number has been returned to the telephone
company. We internally would no longer need to track usage on this number.

Likewise, in the second query I would type into the query 09/01/2006. I can
then determine if a number has been added if the number appears with a date
of 10/01/2006 (increment month by 1) but not with a date of 09/01/2006. I
could also just type 10/01/2006 into the query and forget about the
increment.

In the first query I would always be looking ahead 1 record to determine if
telephone number is deleted.

In the second Query, I would increment month by 1 and look back one record
or I would just enter into(type into the Query ) 10/01/2006 and the look
back one record .

Considering the order of the table I believe I could flowchart this
procedurally but can not define thru SQL.
This will be an analysis tool only used by me so I can directly modify the
query to look at different months.

I might use the queries 1 per month to identify what has been taken out of
service or what has been placed in service.

Thank You,
Greg

Hunter57 said:
I am trying to do something that I believe requires nested SQL. I am having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because the
last month is 09/01/2006 and there is no month 10 record. The output from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because the
first month is 10/01/2006 and there is no month 9 record. The output from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have any
suggestions on how to do this??

Thank You,
Greg
Click to expand...

Hi Greg,

If a number is deleted, as far as I know, you cannot retrieve the
phone number unless you store deleted numbers or changes to data in
another table. You can always archive your data and there are a
number of ways to do it. The simplest method is just to make a copy
of your database file periodically. Of course, that can use up a lot
of hard drive space if your DB is large.

However, why are you deleting phone numbers? Obviously you need them
so why not keep them? You can archive them and there are plenty of
resources on the web to help you. Allen Browne has sample code and
instructions here: http://allenbrowne.com/ser-37.html. If that is too
advanced for your present Access skills Roger J. Carlson has a sample
DB you can download here: http://www.rogersaccesslibrary.com/download3.asp?SampleName=BackUpWithCompac
t.mdb
There are many more examples on the Web.

The second query will require a Parameter Query. The SQL will look
something like this:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
FROM Invoice
WHERE (Invoice.InvoiceDate = (Between StartDate AND EndDate));

To add the criteria to your query in the Query Design Grid under
InvoiceDate in the Criteria row type this: BETWEEN StartDate AND
EndDate

Then on the Menu click Query and then select Parameters. A box will
open up with two columns. In the first Column enter BETWEEN StartDate
AND EndDate there also. In the second Column select Date/Time.

When you run your query a dialogue box should as for your StartDate
and then another one should ask for your EndDate. Be sure to type
them in the correct format you have specified in your table or
forms!

You can also find help for your Query in Access Help if you search for
Parameter Query.

Hope it works out well for you.

Hunter57
http://churchmanagementsoftware.googlepages.com/home
Click to expand...
 
J

John Spencer

IF your Invoice dates are always on the first of the month then you can use
a query like the following. Warning this query cannot be built using the
QBE (query grid), it must be built in the SQL view

Query to get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#

Query to get new numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",-1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#


Another way would be to use a Not In query, but that is slow with a large
group of records
To get discontinued phone numbers
SELECT *
FROM [YourTable]
WHERE TelephoneNumber Not IN
(SELECT TelephoneNumber
FROM [YourTable]
WHERE InvoiceDate = #8/1/2006#)
AND InvoiceDate = #7/1/2006#

You should be able to work out how to get new phone numbers

If all these are too slow, there is one more technique that you can use to
speed the queries up. Basically you would need to use a subquery in the
join clause to get maximum performance.

To get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN
(SELECT TelephoneNumber
FROM YourTable
WHERE InvoiceDate = #8/1/2006#) as YTb
ON YTa.TelephoneNumber = YTb.TelephoneNumber
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #7/1/2006#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Greg

John Spencer,

Thank You!

I used your suggestion utilizing the "NOT IN" logic and it works well.

I tried your 3rd suggestion to speed up the query and my system "hangs". By
this I mean when I try to execute, I stay on the page where I entered my sql
and nothing changes. I just look at my sql. I can not close the sql page. I
am using the same tables in this query as I used in your suggestion that
works.

I think I understand what the query is doing with the "subquery in the Join
clause". I do not see anything wrong with your suggestion.

I am just learning Access, sql and vba so there may be something obvious
that I do not see.

I do know the sql has been analyzed for syntax and table references because
I had to clean up some typos.
Now it just "hangs". The way I stop is to go to "Task Manager" and stop the
Query manually. It is running but will not respond to anything but the task
manager.

Thank you for your suggestions and patience.

Greg


John Spencer said:
IF your Invoice dates are always on the first of the month then you can use
a query like the following. Warning this query cannot be built using the
QBE (query grid), it must be built in the SQL view

Query to get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#

Query to get new numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",-1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#


Another way would be to use a Not In query, but that is slow with a large
group of records
To get discontinued phone numbers
SELECT *
FROM [YourTable]
WHERE TelephoneNumber Not IN
(SELECT TelephoneNumber
FROM [YourTable]
WHERE InvoiceDate = #8/1/2006#)
AND InvoiceDate = #7/1/2006#

You should be able to work out how to get new phone numbers

If all these are too slow, there is one more technique that you can use to
speed the queries up. Basically you would need to use a subquery in the
join clause to get maximum performance.

To get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN
(SELECT TelephoneNumber
FROM YourTable
WHERE InvoiceDate = #8/1/2006#) as YTb
ON YTa.TelephoneNumber = YTb.TelephoneNumber
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #7/1/2006#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Greg said:
I am trying to do something that I believe requires nested SQL. I am having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because the
last month is 09/01/2006 and there is no month 10 record. The output from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because the
first month is 10/01/2006 and there is no month 9 record. The output from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have
any
suggestions on how to do this??

Thank You,
Greg
Click to expand...
Click to expand...
 
J

John Spencer

Could you copy and paste the SQL that hangs? Perhaps I or someone else can
spot the problem.

Also, is the
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Greg said:
John Spencer,

Thank You!

I used your suggestion utilizing the "NOT IN" logic and it works well.

I tried your 3rd suggestion to speed up the query and my system "hangs".
By
this I mean when I try to execute, I stay on the page where I entered my
sql
and nothing changes. I just look at my sql. I can not close the sql page.
I
am using the same tables in this query as I used in your suggestion that
works.

I think I understand what the query is doing with the "subquery in the
Join
clause". I do not see anything wrong with your suggestion.

I am just learning Access, sql and vba so there may be something obvious
that I do not see.

I do know the sql has been analyzed for syntax and table references
because
I had to clean up some typos.
Now it just "hangs". The way I stop is to go to "Task Manager" and stop
the
Query manually. It is running but will not respond to anything but the
task
manager.

Thank you for your suggestions and patience.

Greg


John Spencer said:
IF your Invoice dates are always on the first of the month then you can use
a query like the following. Warning this query cannot be built using the
QBE (query grid), it must be built in the SQL view

Query to get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#

Query to get new numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",-1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#


Another way would be to use a Not In query, but that is slow with a large
group of records
To get discontinued phone numbers
SELECT *
FROM [YourTable]
WHERE TelephoneNumber Not IN
(SELECT TelephoneNumber
FROM [YourTable]
WHERE InvoiceDate = #8/1/2006#)
AND InvoiceDate = #7/1/2006#

You should be able to work out how to get new phone numbers

If all these are too slow, there is one more technique that you can use
to
speed the queries up. Basically you would need to use a subquery in the
join clause to get maximum performance.

To get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN
(SELECT TelephoneNumber
FROM YourTable
WHERE InvoiceDate = #8/1/2006#) as YTb
ON YTa.TelephoneNumber = YTb.TelephoneNumber
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #7/1/2006#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Greg said:
I am trying to do something that I believe requires nested SQL. I am having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because the
last month is 09/01/2006 and there is no month 10 record. The output from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because
the
first month is 10/01/2006 and there is no month 9 record. The output from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have
any
suggestions on how to do this??

Thank You,
Greg
Click to expand...
Click to expand...
Click to expand...
 
G

Greg

John,
As you requested here is the sql I typed:

SELECT YTa.*
FROM [TBL-Wireless Unit Mo Bill (B)] AS YTa LEFT JOIN [SELECT [ Unit No]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE [Bill Date] = #08/01/2006#]. AS YTb ON YTa.[Unit No]=YTb.[Unit No]
WHERE YTb.[Unit No] IS NULL AND YTa.[Bill Date]=#07/01/2006#;

I am embarrassed I think I see an incorrect character "[" just before the
SELECT in the LEFT JOIN.
I am going to try again. Believe it or not I looked at this sql for at least
30 min.
If you see anything else please let me know.

Thanks,
Greg





John Spencer said:
Could you copy and paste the SQL that hangs? Perhaps I or someone else can
spot the problem.

Also, is the
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Greg said:
John Spencer,

Thank You!

I used your suggestion utilizing the "NOT IN" logic and it works well.

I tried your 3rd suggestion to speed up the query and my system "hangs".
By
this I mean when I try to execute, I stay on the page where I entered my
sql
and nothing changes. I just look at my sql. I can not close the sql page.
I
am using the same tables in this query as I used in your suggestion that
works.

I think I understand what the query is doing with the "subquery in the
Join
clause". I do not see anything wrong with your suggestion.

I am just learning Access, sql and vba so there may be something obvious
that I do not see.

I do know the sql has been analyzed for syntax and table references
because
I had to clean up some typos.
Now it just "hangs". The way I stop is to go to "Task Manager" and stop
the
Query manually. It is running but will not respond to anything but the
task
manager.

Thank you for your suggestions and patience.

Greg


John Spencer said:
IF your Invoice dates are always on the first of the month then you can use
a query like the following. Warning this query cannot be built using the
QBE (query grid), it must be built in the SQL view

Query to get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#

Query to get new numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",-1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#


Another way would be to use a Not In query, but that is slow with a large
group of records
To get discontinued phone numbers
SELECT *
FROM [YourTable]
WHERE TelephoneNumber Not IN
(SELECT TelephoneNumber
FROM [YourTable]
WHERE InvoiceDate = #8/1/2006#)
AND InvoiceDate = #7/1/2006#

You should be able to work out how to get new phone numbers

If all these are too slow, there is one more technique that you can use
to
speed the queries up. Basically you would need to use a subquery in the
join clause to get maximum performance.

To get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN
(SELECT TelephoneNumber
FROM YourTable
WHERE InvoiceDate = #8/1/2006#) as YTb
ON YTa.TelephoneNumber = YTb.TelephoneNumber
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #7/1/2006#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am trying to do something that I believe requires nested SQL. I am having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because the
last month is 09/01/2006 and there is no month 10 record. The output from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because
the
first month is 10/01/2006 and there is no month 9 record. The output from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have
any
suggestions on how to do this??

Thank You,
Greg
Click to expand...
Click to expand...
Click to expand...
 
G

Greg

John,
I typed open paren " ( " originally. I then saved query. When I call it up
again to view The open paren. is changed to open Bracket. The same is
happening with close paren, it is getting changed to closed bracket.
I may be losing it or having a senior moment!

Thanks,
Greg
Greg said:
John,
As you requested here is the sql I typed:

SELECT YTa.*
FROM [TBL-Wireless Unit Mo Bill (B)] AS YTa LEFT JOIN [SELECT [ Unit No]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE [Bill Date] = #08/01/2006#]. AS YTb ON YTa.[Unit No]=YTb.[Unit No]
WHERE YTb.[Unit No] IS NULL AND YTa.[Bill Date]=#07/01/2006#;

I am embarrassed I think I see an incorrect character "[" just before the
SELECT in the LEFT JOIN.
I am going to try again. Believe it or not I looked at this sql for at least
30 min.
If you see anything else please let me know.

Thanks,
Greg





John Spencer said:
Could you copy and paste the SQL that hangs? Perhaps I or someone else can
spot the problem.

Also, is the
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Greg said:
John Spencer,

Thank You!

I used your suggestion utilizing the "NOT IN" logic and it works well.

I tried your 3rd suggestion to speed up the query and my system "hangs".
By
this I mean when I try to execute, I stay on the page where I entered my
sql
and nothing changes. I just look at my sql. I can not close the sql page.
I
am using the same tables in this query as I used in your suggestion that
works.

I think I understand what the query is doing with the "subquery in the
Join
clause". I do not see anything wrong with your suggestion.

I am just learning Access, sql and vba so there may be something obvious
that I do not see.

I do know the sql has been analyzed for syntax and table references
because
I had to clean up some typos.
Now it just "hangs". The way I stop is to go to "Task Manager" and stop
the
Query manually. It is running but will not respond to anything but the
task
manager.

Thank you for your suggestions and patience.

Greg


IF your Invoice dates are always on the first of the month then you can
use
a query like the following. Warning this query cannot be built using the
QBE (query grid), it must be built in the SQL view

Query to get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#

Query to get new numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",-1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#


Another way would be to use a Not In query, but that is slow with a large
group of records
To get discontinued phone numbers
SELECT *
FROM [YourTable]
WHERE TelephoneNumber Not IN
(SELECT TelephoneNumber
FROM [YourTable]
WHERE InvoiceDate = #8/1/2006#)
AND InvoiceDate = #7/1/2006#

You should be able to work out how to get new phone numbers

If all these are too slow, there is one more technique that you can use
to
speed the queries up. Basically you would need to use a subquery in the
join clause to get maximum performance.

To get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN
(SELECT TelephoneNumber
FROM YourTable
WHERE InvoiceDate = #8/1/2006#) as YTb
ON YTa.TelephoneNumber = YTb.TelephoneNumber
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #7/1/2006#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am trying to do something that I believe requires nested SQL. I am
having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because
the
last month is 09/01/2006 and there is no month 10 record. The output
from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because
the
first month is 10/01/2006 and there is no month 9 record. The output
from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have
any
suggestions on how to do this??

Thank You,
Greg
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

John,
I typed open paren " ( " originally. I then saved query. When I call it up
again to view The open paren. is changed to open Bracket. The same is
happening with close paren, it is getting changed to closed bracket.
I may be losing it or having a senior moment!
Click to expand...

What version of Access? I've seen A97 do this with subqueries. The closing
bracket for the subquery needs a period after it, and if I recall, you aren't
allowed to use bracketed table or fieldnames within the bracketed subquery.
You may need to create the inner query, save it as a query name, and join THAT
query.

I would really recommend NOT using special characters such as parentheses,
hyphens, and - even - blanks in table or field names; Access can certainly get
confused.

John W. Vinson [MVP]
 
G

Greg

John Spencer,

The sql does not work with Paren can you see anything else I can try??

Thanks,
Greg
Greg said:
John,
As you requested here is the sql I typed:

SELECT YTa.*
FROM [TBL-Wireless Unit Mo Bill (B)] AS YTa LEFT JOIN [SELECT [ Unit No]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE [Bill Date] = #08/01/2006#]. AS YTb ON YTa.[Unit No]=YTb.[Unit No]
WHERE YTb.[Unit No] IS NULL AND YTa.[Bill Date]=#07/01/2006#;

I am embarrassed I think I see an incorrect character "[" just before the
SELECT in the LEFT JOIN.
I am going to try again. Believe it or not I looked at this sql for at least
30 min.
If you see anything else please let me know.

Thanks,
Greg





John Spencer said:
Could you copy and paste the SQL that hangs? Perhaps I or someone else can
spot the problem.

Also, is the
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Greg said:
John Spencer,

Thank You!

I used your suggestion utilizing the "NOT IN" logic and it works well.

I tried your 3rd suggestion to speed up the query and my system "hangs".
By
this I mean when I try to execute, I stay on the page where I entered my
sql
and nothing changes. I just look at my sql. I can not close the sql page.
I
am using the same tables in this query as I used in your suggestion that
works.

I think I understand what the query is doing with the "subquery in the
Join
clause". I do not see anything wrong with your suggestion.

I am just learning Access, sql and vba so there may be something obvious
that I do not see.

I do know the sql has been analyzed for syntax and table references
because
I had to clean up some typos.
Now it just "hangs". The way I stop is to go to "Task Manager" and stop
the
Query manually. It is running but will not respond to anything but the
task
manager.

Thank you for your suggestions and patience.

Greg


IF your Invoice dates are always on the first of the month then you can
use
a query like the following. Warning this query cannot be built using the
QBE (query grid), it must be built in the SQL view

Query to get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#

Query to get new numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",-1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#


Another way would be to use a Not In query, but that is slow with a large
group of records
To get discontinued phone numbers
SELECT *
FROM [YourTable]
WHERE TelephoneNumber Not IN
(SELECT TelephoneNumber
FROM [YourTable]
WHERE InvoiceDate = #8/1/2006#)
AND InvoiceDate = #7/1/2006#

You should be able to work out how to get new phone numbers

If all these are too slow, there is one more technique that you can use
to
speed the queries up. Basically you would need to use a subquery in the
join clause to get maximum performance.

To get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN
(SELECT TelephoneNumber
FROM YourTable
WHERE InvoiceDate = #8/1/2006#) as YTb
ON YTa.TelephoneNumber = YTb.TelephoneNumber
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #7/1/2006#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am trying to do something that I believe requires nested SQL. I am
having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted because
the
last month is 09/01/2006 and there is no month 10 record. The output
from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because
the
first month is 10/01/2006 and there is no month 9 record. The output
from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have
any
suggestions on how to do this??

Thank You,
Greg
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

The problem is with your table name and field names. Access will not allow
you to have a subquery in the from clause that uses [] and your field and
table names require brackets. Good naming conventions that don't allow
spaces and punctuation marks would solve the problem.

One way to solve the name problem is to build a query that "fixes" the field
names and then use that saved query.

Something like the following: Save it as qWirelessUnitBill
SELECT [Unit No] as UnitNo, [Bill Date] as BillDate
FROM [TBL-Wireless Unit Mo Bill (B)]

Of course, you will have to add and fix any other field names you need.

Now use that query in
SELECT YTa.*
FROM qWirelessUnitBill AS YTa LEFT JOIN
[SELECT UnitNo
FROM qWirelessUnitBill
WHERE BillDate = #08/01/2006#]. AS YTb
ON YTa.UnitNo=YTb.UnitNo
WHERE YTb.UnitNo IS NULL AND YTa.BillDate=#07/01/2006#;

Another way to solve this is to use two queries.

Query one:
Save this query as QOtherMonth
SELECT [Unit No]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE [Bill Date] = #08/01/2006#

Query two:
SELECT YTa.*
FROM [TBL-Wireless Unit Mo Bill (B)] AS YTa LEFT JOIN QOtherMonth AS YTb
ON YTa.[Unit No]=YTb.[Unit No]
WHERE YTb.[Unit No] IS NULL AND YTa.[Bill Date]=#07/01/2006#;

Too make this more efficient, I would use an unbound form to input
parameters for the two months and then reference the controls on the form.
Do you know how to do this?
Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm

Or check out this from MS
http://office.microsoft.com/en-us/access/HA011730581033.aspx


Query one would then look like:
Parameters Forms!NameOfForm!OtherMonth DateTime;
SELECT [Unit No]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE [Bill Date] = Forms!NameOfForm!OtherMonth

Query two
SELECT YTa.*
FROM [TBL-Wireless Unit Mo Bill (B)] AS YTa LEFT JOIN QOtherMonth AS YTb
ON YTa.[Unit No]=YTb.[Unit No]
WHERE YTb.[Unit No] IS NULL AND YTa.[Bill Date]=
Forms!NameOfForm!CurrentMonth

On the form, you would enter the two dates you were interested in and then
get the output. SO if you wanted new phone numbers for august you would
enter 08/01/2006 for CurrentMonth and 07/01/2006 for OtherMonth. If you
wanted Dropped phone numbers for August you would enter
07/01/2006 for current month and 08/01/2006 for other month.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Greg said:
John,
As you requested here is the sql I typed:

SELECT YTa.*
FROM [TBL-Wireless Unit Mo Bill (B)] AS YTa LEFT JOIN [SELECT [ Unit No]
FROM [TBL-Wireless Unit Mo Bill (B)]
WHERE [Bill Date] = #08/01/2006#]. AS YTb ON YTa.[Unit No]=YTb.[Unit No]
WHERE YTb.[Unit No] IS NULL AND YTa.[Bill Date]=#07/01/2006#;

I am embarrassed I think I see an incorrect character "[" just before the
SELECT in the LEFT JOIN.
I am going to try again. Believe it or not I looked at this sql for at
least
30 min.
If you see anything else please let me know.

Thanks,
Greg





John Spencer said:
Could you copy and paste the SQL that hangs? Perhaps I or someone else can
spot the problem.

Also, is the
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Greg said:
John Spencer,

Thank You!

I used your suggestion utilizing the "NOT IN" logic and it works well.

I tried your 3rd suggestion to speed up the query and my system
"hangs".
By
this I mean when I try to execute, I stay on the page where I entered
my
sql
and nothing changes. I just look at my sql. I can not close the sql page.
I
am using the same tables in this query as I used in your suggestion
that
works.

I think I understand what the query is doing with the "subquery in the
Join
clause". I do not see anything wrong with your suggestion.

I am just learning Access, sql and vba so there may be something
obvious
that I do not see.

I do know the sql has been analyzed for syntax and table references
because
I had to clean up some typos.
Now it just "hangs". The way I stop is to go to "Task Manager" and stop
the
Query manually. It is running but will not respond to anything but the
task
manager.

Thank you for your suggestions and patience.

Greg


IF your Invoice dates are always on the first of the month then you
can
use
a query like the following. Warning this query cannot be built using the
QBE (query grid), it must be built in the SQL view

Query to get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#

Query to get new numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN [YourTable] as YTb
ON YTa.TelephoneNumber =YTb.TelephoneNumber
AND YTa.InvoiceDate = DateAdd("M",-1,Ytb.InvoiceDate)
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #8/1/2006#


Another way would be to use a Not In query, but that is slow with a large
group of records
To get discontinued phone numbers
SELECT *
FROM [YourTable]
WHERE TelephoneNumber Not IN
(SELECT TelephoneNumber
FROM [YourTable]
WHERE InvoiceDate = #8/1/2006#)
AND InvoiceDate = #7/1/2006#

You should be able to work out how to get new phone numbers

If all these are too slow, there is one more technique that you can
use
to
speed the queries up. Basically you would need to use a subquery in the
join clause to get maximum performance.

To get discontinued numbers
SELECT YTa.*
FROM [YourTable] as YTa
LEFT JOIN
(SELECT TelephoneNumber
FROM YourTable
WHERE InvoiceDate = #8/1/2006#) as YTb
ON YTa.TelephoneNumber = YTb.TelephoneNumber
WHERE YTb.TelephoneNumber is Null
AND YTa.InvoiceDate = #7/1/2006#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am trying to do something that I believe requires nested SQL. I am
having
a devil of a time with understanding how nesting works!!

Here is what I am trying to do:

Query has 1 table as input. The table has a multifield key.
Table is called Invoice
Structure of the table is:

TelephoneNumber, InvoiceDate, RecurringCharge, MinUsed
3342227777,08/01/2006,100.00,25
3342227777,09/01/2006,111.00,26
3342227777,10/01/2006,80.00,15
3342227777,11/01/2006,135.00,40
8342229999,08/01/2006,100.00,25
8342229999,09/01/2006,111.00,26
7342228888,08/01/2006,100.00,25
7342228888,09/01/2006,111.00,26
7342228888,10/01/2006,80.00,15
7342228888,11/01/2006,135.00,40
3342220000,10/01/2006,88.00,15
3342220000,11/01/2006,134.00,40

The key fields are TelephoneNumber and InvoiceDate.

I would like to ba able to have 2 Queries:
The first Query would Identify all Telephone numbers deleted between
09/01/2006 and 10/01/2006. I know number 834222999 is deleted
because
the
last month is 09/01/2006 and there is no month 10 record. The output
from
the query would be all fields for 8342229999.

The second query would identify all telephone numbers added between
09/01/2006 and 10/01/2006. I know number 3342220000 is added because
the
first month is 10/01/2006 and there is no month 9 record. The output
from
this query would be all fields for 3342220000.

Is this doable? I am far from an expert at SQL or VBA. Does anyone have
any
suggestions on how to do this??

Thank You,
Greg
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

most recent rows 5
Query for groups of records 2
Awkward Query Idea / Help!? Percentiles. 2
Extracting HTML from web based query? 1
Slow VBA-Generated SQL Involving Totals Query 9
Excel/SQL Query Quandry 3
WTD: Nvidia Videocard for 4x AGP Slot 0

Top